Query with count on relation model

@ersinkandemir could you help me on this :D?

@kocoten1992

$result = A::whereHas('b', function ($query) {
    $query->where('number', '>', 5);
})->get();

class A extends Model 
{
    function b()
    {
        return $this->hasMany(B::class):
    }
}

Return all A where it has a B relationship which has the number column greater than 5.

@phildawson , I need a count() from all B, instead of just one B is bigger than 5

Return all the As where all of the B->number for it combined adds up to greater than 5?

yes @phildawson :D,

return As where all belongs Model B -> number combined adds up to greater than 5

@kocoten1992 I'm just thinking what the query would look like, then it might be easier to see how it could be done. It's hard typing on the phone but guessing?

select * from a join (
    select sum(number) as total, a_id from b group by a_id
) as b 
where b.total > 5 and b.a_id = a.id

I really wish there is a simpler solution like

return $query->whereHas('B', function ($query2) {
    $query2->whereSum('number', '>', 5);
});

It may take me couple day to learn about join, sql syntax. (got to admit that, I have 0 exp vs SQL, all query I ever learn is by laravel clean API, hahahah)

P/S: btw, I saw there is sum() on DB query builder, can it help for a simpler solution?

@kocoten1992 You can use whereHas with just a bit more effort:

$query->whereHas('B', function ($q) {
    $q->groupBy('foreign_key')->select(DB::raw('sum(number)'));
}, '>', 5)->get();

Mind that you can't use selectRaw in the closure (because it works like addSelect).

ps. count and sum won't work as expected in the relational queries.

uhm, I am trying the method, but actually I was trying to simplify a bit the question, this is the real of my query, and somehow when I try it, it don't work, this stuff is too advance, can't fix it. Help @JarekTkaczyk .

I got A, B, C.

A hasMany B, B hasMany C
C belongsTo B, B belongsTo A

then this is the method I was trying

return $query->whereHas('B', function ($query2) {
            $query2->whereHas('C', function ($query3) {
                $query3->groupBy('b_id')->select(DB::raw('sum(number)'));
            }, '>', 5);
        });

P/S: I think I guess the problem, I want to

sum all B of (sum all C->number of B) > 5

I shouldn't simplify the question at all, sorry :D

just noticed the reply above is updated so this might not be applicable

Yeah so that wouldn't be summing if you are checking if any were bigger than five.

Without checking I would say adding the nested relationship should be what you want. Get all A which has Bs which have at least one C with number column greater than 5.

$result = A::whereHas('b.c', function ($query) {
    $query->where('number', '>', 5);
})->get();

If you are actually wanting at least 5 Cs then do this

A::has('b.c', '>=', 5)->get();

No @phil, I really want the sum, now it a bit more complex, just update the comment upper

@kocoten1992 Read this first.

Then tell us what you acutally want to achieve, but forget the queries and laravel. Otherwise it's impossible to help you.

I guess, you'd like to select count of Bs having sum(number) > 5, definitely not sum all B of (sum all C->number of B) > 5

You better share your own models and relationships. You mentioned the C model but we couldn't solve what it is for and how to be queried.

Really sorry for this mess, thank for the link, I will try to avoid this in the future.

I can't detail it but just take another example but really Identical to that.

A as school, B as class, C as student.

The relation is:

C belongsTo B, and B belongsTo A

Each C will give charity a small amount of money. (It is the "number")

I want to get the school that have charity more than 5k. (the 5)

A::whereHas('B.C', function ($q) {
    $q->select(DB::raw('sum(number)'));
}, '>', 5)->get();

@ersinkandemir , but that query mean: get the school have class donate more than 5k, not school donate more than 5k?

$schools = School::select('schools.*')
    ->join('classes', 'schools.id', '=', 'classes.school_id')
    ->join('students', 'classes.id', '=', 'students.class_id')
    ->groupBy('schools.id')
    ->havingRaw('sum(students.number) > 5')
    ->get();

@pmall , that is super :D, solve my problem, I just test a little bit and this method work too.

I define a hasManyThrough relation between A and C, and then

return $query->whereHas('theRelationBetweenAnC', function ($query2) {
            $query2->select(DB::raw('sum(number)'));
        }, '>', 5);

@kocoten1992 whereHas filter by the number of related lines selected. With your example it will select all the schools having more than 5 students.

No, I'm testing right now, my method is correctly return the result too.

Really, it filters on the numbers of lines.

@pmall,

I think, while using whereHas, it creates a query like:

select * from table where (select sum(number) from table2 where table_id=id) > 5

If you don't set the operator and count, it puts " >= 1 " which works as a boolean to check whether subquery returns anything. In the above use, because the subquery returns single column with a value, it checks whether it is greater than 5.

I don't know, seriously you need to test this.

I've been testing for a few more setting, it work every time, I'm on laravel newest:

4 schools,
schools 1: 15 classes
schools 2: 0 classes
schools 3: 2 classes
schools 4: 0 classes

and in each class, I define some random student number, each student with random donation number

I did define hasManyThrough (incase you miss that).

Ok I may be wrong I through it always created a count query.

I think it may be because with the select statement you override the default select statement, which is a count. It would be consistent with @JarekTkaczyk saying you have to use select and not selectRaw.

@kocoten1992 Would you please dd(DB::getQueryLog()) and paste the SQL query here for your last one and the way i suggest you? I wonder what Eloquent builds.

"select * from `schools` where `schools`.`deleted_at` is null and (select sum(number) from `students` inner join `classs` on `classs`.`id` = `students`.`class_id` where `schools`.`id` = `classs`.`school_id`) >= 5"

I have to change to real class name, so the classs pretty lame.

select * from `schools` where `schools`.`deleted_at` is null and (select count(*) from `classs` where `classs`.`school_id` = `schools`.`id` and (select sum(number) from `class_items` where `students`.`class_id` = `classs`.`id`) > 5) >= 1

The second is you suggest me.

@kocoten1992 put public $table = 'classes'; in your Class model ^^