Support the ongoing development of Laravel.io →
Database Eloquent
Last updated 1 year ago.
0

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();
Last updated 1 year ago.
0
#1111 - Invalid use of group function 

Thanks, I will try to play with it.

Last updated 1 year ago.
0

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. :)

Last updated 1 year ago.
0

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 
Last updated 1 year ago.
0

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 :)

Last updated 1 year ago.
0

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();
Last updated 1 year ago.
0

Sign in to participate in this thread!

Eventy

Your banner here too?

lachyn lachyn Joined 23 Oct 2014

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2024 Laravel.io - All rights reserved.