Level 75
Try replacing where with having.
Feb 1, 2017
6
Level 1
Issues with Eloquent using with(), groupBy() and sum()
I have got my relationships correctly set in the models. I like that I do not have to join any tables manually and that I can eager load when it calls for. However, I am having a hard time converting an SQL query into Eloquent.
The RAW SQL query:
SELECT a.id, SUM(a.revenue) as total, b.email, c.name, d.name as poc
FROM partner_revenues a
INNER JOIN lp_users b ON a.id = b.id
INNER JOIN partners c ON a.partner_id = c.id
INNER JOIN pocs d ON a.poc_id = d.id
GROUP BY a.user_id
The Eloquent code:
$totalRevenue = PartnerRevenue::with([
'partner' => function($query) {$query->select('id', 'partner_id');},
'poc' => function($query) {$query->select('id', 'poc_id');},
'user' => function($query) {$query->select('id', 'id');}])
->groupBy('user_id')
->where('accepted', 1)
->sum('revenue');
That does not work. What am I doing wrong?
Level 1
With having, I get this:
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1 ?' at line 1 (SQL: select sum(revenue) as aggregate from partner_revenues group by user_id having accepted 1 )
Level 1
With the where clause(just as I posted in the original post), it only runs one query. This one:
select sum(revenue) as aggregate from partner_revenues where accepted = '1' group by user_id
Level 1
This query will work just fine:
$totalRevenue = DB::table('partner_revenues')
->select('lp_users.email', DB::raw('SUM(revenue) as total'))
->join('lp_users', 'partner_revenues.user_id', '=', 'lp_users.id')
->join('partners', 'partner_revenues.partner_id', '=', 'partners.id')
->join('pocs', 'partner_revenues.poc_id', '=', 'pocs.id')
->groupBy('partner_revenues.user_id')
->paginate(15);
I guess my question is, why cannot eager load this query?
1 like
Level 75
Eager load is more for simple relation, orders / order details, that sort of thing, a group by, all have to be checked for sql to get grouping.
2 likes
Level 1
What table user_id in groupBy("user_id") belongs to? I have the same problem as you and i made a fix on it. You may take a look on this:
$user = User::with(['messages' => function($query) {
$query->select('messages.user_id');
$query->groupBy('user_id');
}])->get();
1 like
Please or to participate in this conversation.