Try something like this.
Check out the query in your phpmyadmin
$result = Model::with('relations')->whereIn('id', function($query){
$query->select('models.id')->from('models')
->join('relations', 'models.id', '=', 'relations.model_id')
->whereRaw('models.total > SUM(relations.total)')
->groupBy('models.id');
})->get();
#1111 - Invalid use of group function
Thanks, I will try to play with it.
the error comes because of model.total. SQL doesn't know every unique models.id has a unique models.total.
I thought this would happen. I think I found a solution. Group by both, models.total and models.id.
->groupBy('models.id', 'models.total')
I hope this works. :)
Still no luck :(
select * from `models` where `id` in (select `models`.`id` from `models` inner join `relations` on `models`.`id` = `relations`.`model_id` where model .total > SUM(relations.total) group by `models`.`id`, `models`.`total`) )
#1111 - Invalid use of group function
You have to use HAVING instead of WHERE in the inner query.
select * from `models` where `id` in (select `models`.`id` from `models` inner join `relations` on `models`.`id` = `relations`.`model_id` group by `models`.`id`, `models`.`total` having models.total > SUM(relations.total)) )
You can do this in Laravel with ->having('...').
This should work :)
Bingo! You are the Man! I owe you a Beer :) There are still things to learn. Thank you very much.
Laravel way for audience:
$result = Model::with('relations')->whereIn('id', function($query){
$query->select('models.id')->from('models')
->join('relations', 'models.id', '=', 'relations.model_id')
->groupBy('model.id','model.total')
->havingRaw('model.total > SUM(relations.total)');
})->get();
Sign in to participate in this thread!
The Laravel portal for problem solving, knowledge sharing and community building.
The community