Querying count from Eloquent

Question

I've got a query in eloquent correctly calculating the count as it should here:

$query = A::withCount(
        ['bs' =>
            function ($query) use ($from, $to) {
                $query->where(function ($query) use ($from) {
                    $query->whereDate('start', '<', $from)
                        ->whereDate('end', '>', $from);
                })->orWhere(function ($query) use ($to) {
                    $query->whereDate('start', '<', $to)
                        ->whereDate('end', '>', $to);
                })->orWhere(function ($query) use ($from, $to) {
                    $query->whereDate('start', '>', $from)
                        ->whereDate('end', '<', $to);
                });
        }])
        ->whereBetween('cmp1', [$min_cmp1, $max_cmp1])
        ->whereBetween('cmp2', [$min_cmp2, $max_cmp2])
        ->where('cmp3', '<=', $request->cmp3)
        ->where('cmp4', '<=', $request->cmp4)
        ->with('more_relations');

    return AResource::collection($query->paginate(5));

This goes to an API Controller. I use this for frontend pagination with Vue. I want to use the count to filter out all A's with a bs_count of 0, however chaining a where clause does not work as it is an aggregate, and it returns an error for not finding a column named bs_count. The solution, I found out, would be to use a having clause. I found this code, but when I try to convert it, it doesn't work, returning the same error, not finding a column named bs_count.

DB::table('bs')
->select('*', DB::raw('COUNT(*) as bs_count'))
->groupBy('a_id')
->having('bs_count', '=' , 0);

This is without adding the extra query on the count, as I wanted to try this to see if it works first, which it doesn't.

Is the having clause the correct way to go? I think I need to use Eloquent rather than the DB::table() syntax as the Resource builder uses the model structure to build the resource response.

In summary, I'm trying to use the count I calculated in my first query, and query against it: where bs_count = 0. Is there a way to do this in Laravel and still be able to pass on the results to the API Resource?

score 0 · Answer 1 · answered Mar 19 '18 at 16:06

0

Using a HAVING clause is the correct way to go.

The problem is that Laravel pagination doesn't natively support HAVING clauses.
You have to create the pagniator manually:
How to use paginate() with a having() clause when column does not exist in table

Your test query with DB doesn't work because you can't select all columns when grouping:

->select('a_id', DB::raw('COUNT(*) as bs_count'))

answered Mar 19 '18 at 16:06

Jonas Staudenmeir

24,815
6
63
109

How does this work with the custom count I'm using? Will I have to write the count query in raw SQL as well? – ashraj98 Mar 19 '18 at 17:38
I think you can use the query as it is and just add `->having('bs_count', '=' , 0)` to it. – Jonas Staudenmeir Mar 19 '18 at 18:18
I meant the part with the dates, I'm not trying to get the full count of `B`'s, just the `B`'s that satisfy the date query. – ashraj98 Mar 19 '18 at 19:11
I'm sorry, I still don't understand your question. Can you give me more details? – Jonas Staudenmeir Mar 19 '18 at 19:39
In your select call, you use `COUNT(*)`, which I do not believe will query only the rows that satisfy the date logic I have in my first snippet. Will I have to write that date logic in the count in raw SQL for this solution to work? – ashraj98 Mar 19 '18 at 19:47
That was just meant to show how your test query could be fixed. – Jonas Staudenmeir Mar 19 '18 at 21:32

score 0 · Accepted Answer · answered Mar 21 '18 at 18:20

Laravel does have a built-in solution for this, as I discovered. From the docs, I found the functions whereHas() and whereDoesntHave(). These functions allow you pass a closure to filter the rows based on the count it does behind the scenes. How I adapted my previous solution was:

$query = A::whereDoesntHave(
    'bs', 
        function ($query) use ($from, $to) {
            $query->where(function ($query) use ($from) {
                $query->whereDate('start', '<', $from)
                    ->whereDate('end', '>', $from);
            })->orWhere(function ($query) use ($to) {
                $query->whereDate('start', '<', $to)
                    ->whereDate('end', '>', $to);
            })->orWhere(function ($query) use ($from, $to) {
                $query->whereDate('start', '>', $from)
                    ->whereDate('end', '<', $to);
            });
    )
    ->whereBetween('cmp1', [$min_cmp1, $max_cmp1])
    ->whereBetween('cmp2', [$min_cmp2, $max_cmp2])
    ->where('cmp3', '<=', $request->cmp3)
    ->where('cmp4', '<=', $request->cmp4)
    ->with('more_relations');

return AResource::collection($query->paginate(5));

This automatically does a count of B's based on the query passed and only returns rows that have a count of zero, which is exactly what I needed.

Querying count from Eloquent

2 Answers2