GroupBy on Eloquent relationship

Question

I am trying to combine 4 models to get a summary report. These are my tables.

user
-id
-name
-country_id

country
-id
-name

invoice
-id
-user_id
-type_id

type
-id
-name

I want to count a detail of invoice for each type based on the country. That is something like this.

country | type_name_1 | type_name_2 | type_name_2
America |   10        |   2         |   4
Canada  |   62        |   0         |   35
China   |   23        |   9         |   5

I tried the following query but it's not exactly giving the answer I want above.

\App\Invoice::all()->groupBy(function($s){
    return $s->user->country->name;
})->groupBy(function($s){
    return $s->type->count();
})

Error: Exception with message 'Property [type] does not exist on this collection instance.'

Can someone give me a pointer here?

you can use Polymorphic Relations to simplify . it is easily managable. See https://laravel.com/docs/5.7/eloquent-relationships#polymorphic-relations — Nirali, Nov 19 '18 at 05:33
You will need to use joins instead of the relation itself , the result will be a query result instead of the eloquent collection. So for this result , the query builder is the one to go. What you want is quite hard actually because you want the results from one table to become columns, it would be easier if you had the working RAW SQL https://stackoverflow.com/questions/1241178/mysql-rows-to-columns i think this will help you — Munteanu Petrisor, Nov 19 '18 at 06:53
There is no easy nor pretty solution, you might need to look into selectRaw(), joins from query builder, unless you know them already — Munteanu Petrisor, Nov 19 '18 at 07:03

score 0 · Answer 1 · answered Nov 19 '18 at 05:21

Your query will get only results of Invoice model. To get the related table use with.

\App\Invoice::with('type','user')->get();

This will result collection of invoices with related types and user. Then you can use the groupBy method to filter the result collection. To know more about groupBy method, see the link.

score 0 · Answer 2 · answered Nov 19 '18 at 07:09

0

You have to query country model like

    Country::withCount(['invoice' => function($q){
          $q->groupBy('type_id');
     ->get();

answered Nov 19 '18 at 07:09

Usman Jdn

807
8
21

GroupBy on Eloquent relationship

2 Answers2