Case statement with withCount in Laravel

Question

How do I achieve this kind of query in Laravel?

Model::query()
        ->select('id')
        ->withCount(['relation_1', 'relation_2', 'relation_3'])
        ->selectRaw(
            '(CASE
WHEN relation_1_count < 0 THEN "relation 1 failed"
WHEN relation_1_count > 0 and relation_2_count < 0 THEN "relation 2 failed"
WHEN relation_1_count > 0 and relation_2_count > 0 and relation_3_count < 0 THEN "relation 3 failed"
ELSE "All relations passed"
END) AS model_status')
    ->get()

This gives the relation_1_count column that does not exist.

What is the proper approach for this kind of problem?

`relation_1_count` is a alias of subQuery Eloquent generated for withCount relations. you can't use column aliases in select list. column aliases can only be used in having clause for filtering. — Zohaib, Dec 26 '22 at 08:09
I didn't realize that. So, what is the proper way? Event Tim's answer is not working properly, (see below). — Suresh Hemal, Dec 26 '22 at 08:23

score 0 · Answer 1 · answered Dec 26 '22 at 08:49

Your count queries are aliased subqueries so you can't use the alias directly. In MySQL however you can use the alias in another subquery e.g.:

Model::query()
    ->select('id')
    ->withCount(['relation_1', 'relation_2', 'relation_3'])
    ->selectRaw("CASE WHEN (SELECT relation_1_count) < 0 THEN 'relation 1 failed'
                      WHEN (SELECT relation_1_count) > 0 AND (SELECT relation_2_count) < 0 THEN 'relation 2 failed'
                      WHEN (SELECT relation_1_count) > 0 AND (SELECT relation_2_count) > 0 AND (SELECT relation_3_count) < 0 THEN 'relation 3 failed'
                 ELSE 'All relations passed' END AS model_status")
->get()

though in my opinion this sort of logic to craft a presentational message is best handled by the PHP view logic.

Sidenote: I've not actually tried this particular one myself so let me know if it works.

Case statement with withCount in Laravel

1 Answers1