GroupBy on Eloquent relationship
up vote
0
down vote
favorite
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.
AppInvoice::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?
laravel eloquent
asked Nov 19 at 5:12
Fawzan
2,77432353
add a comment |
up vote
0
down vote
favorite
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.
AppInvoice::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?
laravel eloquent
asked Nov 19 at 5:12
Fawzan
2,77432353
you can use Polymorphic Relations to simplify . it is easily managable. See laravel.com/docs/5.7/…
– Nirali
Nov 19 at 5: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 stackoverflow.com/questions/1241178/mysql-rows-to-columns i think this will help you
– Munteanu Petrisor
Nov 19 at 6: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 at 7:03
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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.
AppInvoice::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?
laravel eloquent
asked Nov 19 at 5:12
Fawzan
2,77432353
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.
AppInvoice::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?
laravel eloquent
laravel eloquent
asked Nov 19 at 5:12
Fawzan
2,77432353
asked Nov 19 at 5:12
Fawzan
2,77432353
asked Nov 19 at 5:12
Fawzan
2,77432353
asked Nov 19 at 5:12
Fawzan
2,77432353
asked Nov 19 at 5:12
Fawzan
2,77432353
2,77432353
you can use Polymorphic Relations to simplify . it is easily managable. See laravel.com/docs/5.7/…
– Nirali
Nov 19 at 5: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 stackoverflow.com/questions/1241178/mysql-rows-to-columns i think this will help you
– Munteanu Petrisor
Nov 19 at 6: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 at 7:03
add a comment |
you can use Polymorphic Relations to simplify . it is easily managable. See laravel.com/docs/5.7/…
– Nirali
Nov 19 at 5: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 stackoverflow.com/questions/1241178/mysql-rows-to-columns i think this will help you
– Munteanu Petrisor
Nov 19 at 6: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 at 7:03
you can use Polymorphic Relations to simplify . it is easily managable. See laravel.com/docs/5.7/…
– Nirali
Nov 19 at 5:33
you can use Polymorphic Relations to simplify . it is easily managable. See laravel.com/docs/5.7/…
– Nirali
Nov 19 at 5: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 stackoverflow.com/questions/1241178/mysql-rows-to-columns i think this will help you
– Munteanu Petrisor
Nov 19 at 6:53
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 stackoverflow.com/questions/1241178/mysql-rows-to-columns i think this will help you
– Munteanu Petrisor
Nov 19 at 6: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 at 7:03
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 at 7:03
add a comment |
2 Answers
2
active
oldest
votes
up vote
0
down vote
Your query will get only results of Invoice model. To get the related table use with.
AppInvoice::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.
answered Nov 19 at 5:21
InvincibleElf
865
add a comment |
up vote
0
down vote
You have to query country model like
Country::withCount(['invoice' => function($q){
$q->groupBy('type_id');
->get();
answered Nov 19 at 7:09
Usman Jdn
437113
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
Your query will get only results of Invoice model. To get the related table use with.
AppInvoice::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.
answered Nov 19 at 5:21
InvincibleElf
865
add a comment |
up vote
0
down vote
Your query will get only results of Invoice model. To get the related table use with.
AppInvoice::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.
answered Nov 19 at 5:21
InvincibleElf
865
add a comment |
up vote
0
down vote
up vote
0
down vote
Your query will get only results of Invoice model. To get the related table use with.
AppInvoice::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.
answered Nov 19 at 5:21
InvincibleElf
865
Your query will get only results of Invoice model. To get the related table use with.
AppInvoice::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.
answered Nov 19 at 5:21
InvincibleElf
865
answered Nov 19 at 5:21
InvincibleElf
865
answered Nov 19 at 5:21
InvincibleElf
865
answered Nov 19 at 5:21
InvincibleElf
865
865
add a comment |
add a comment |
up vote
0
down vote
You have to query country model like
Country::withCount(['invoice' => function($q){
$q->groupBy('type_id');
->get();
answered Nov 19 at 7:09
Usman Jdn
437113
add a comment |
up vote
0
down vote
You have to query country model like
Country::withCount(['invoice' => function($q){
$q->groupBy('type_id');
->get();
answered Nov 19 at 7:09
Usman Jdn
437113
add a comment |
up vote
0
down vote
up vote
0
down vote
You have to query country model like
Country::withCount(['invoice' => function($q){
$q->groupBy('type_id');
->get();
answered Nov 19 at 7:09
Usman Jdn
437113
You have to query country model like
Country::withCount(['invoice' => function($q){
$q->groupBy('type_id');
->get();
answered Nov 19 at 7:09
Usman Jdn
437113
answered Nov 19 at 7:09
Usman Jdn
437113
answered Nov 19 at 7:09
Usman Jdn
437113
answered Nov 19 at 7:09
Usman Jdn
437113
437113
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53368663%2fgroupby-on-eloquent-relationship%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
you can use Polymorphic Relations to simplify . it is easily managable. See laravel.com/docs/5.7/…
– Nirali
Nov 19 at 5: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 stackoverflow.com/questions/1241178/mysql-rows-to-columns i think this will help you
– Munteanu Petrisor
Nov 19 at 6: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 at 7:03