Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

tnort's avatar
Level 4

Query with multiple withCounts

Hi,

I am trying to figure out how to get a relationship grouped based on different criteria. I figured out that I could potentially have multiple counts that address different criteria but that doesn't seem to be the best approach.

MyQuery
->when($includeTests, function ($query){
                return $query->withCount(['englishTestResults as passed_tests' => function($q){
                    return $q->where('passed_test', 1);
                }]);
            })
->when($includeTests, function ($query){
                return $query->withCount(['englishTestResults as failed_tests' => function($q){
                    return $q->where('passed_test', -1);
                }]);
            })
->when($includeTests, function ($query){
                return $query->withCount(['englishTestResults as missed_tests' => function($q){
                    return $q->where('passed_test', 0);
                }]);
            })

Is there a better way of running this query?

Thank you in advance!

0 likes
4 replies
tykus's avatar

You could use a JOIN instead

tnort's avatar
Level 4

@tykus, thank you for your reply. How would it help in this case? Isn't the join already done when I say "->with".

tykus's avatar

@tudosm really; have you examined the query that has been executed?

tnort's avatar
Level 4

@tykus, I looked at it and it seems to be a subquery. But still cannot see how would a joining query help :( .

Please or to participate in this conversation.