Adding pagination breaks QueryBuilder return

Using Laravel with Spatie Query Builder, I have this function that works great

        return Inertia::render('Classes/Index', [
            'classes' => QueryBuilder::for(OrientationClass::class)
                ->defaultSort('date')
                ->allowedFilters(AllowedFilter::custom('date', new DateFilter, 'date')->default('upcoming'))
                ->get()
        ]);

However, if I try to add pagination, like so:

        return Inertia::render('Classes/Index', [
            'classes' => QueryBuilder::for(OrientationClass::class)
                ->defaultSort('date')
                ->allowedFilters(AllowedFilter::custom('date', new DateFilter, 'date')->default('upcoming'))
                ->paginate(1)
                ->withQueryString()
        ]);

it breaks and returns no values. I have a similar (almost) identical query that works fine:

        return Inertia::render('Users/Index', [
            'users' => QueryBuilder::for(User::class)
                ->defaultSort('member_number')
                ->allowedSorts(['last_name', 'member_number'])
                ->allowedFilters(AllowedFilter::custom('name_number', new NameMemberNumberFilter),
                    AllowedFilter::exact('status'),
                    AllowedFilter::exact('member_type'))
                ->paginate(25)
                ->withQueryString()
        ]);

For reference, here is my DateFilter:

class DateFilter implements Filter
{

    /**
     * @inheritDoc
     */
    public function __invoke(Builder $query, $value, string $property)
    {
        if ($value === 'upcoming') {
            $query->where($property, '>=', Carbon::today()->toDateString());
        } elseif ($value === 'past') {
            $query->where($property, '<', Carbon::today()->toDateString());
        }
    }
}

Via Clockwork, I've checked the query being run behind the scenes, and it works fine; it shows the values I would expect.

What am I missing?

LaryAI

1 year ago

Level 58

The issue you're experiencing with pagination in your QueryBuilder setup might be related to how the DateFilter is being applied or how the pagination interacts with the query results. Here are a few steps and considerations to help troubleshoot and resolve the issue:

Check the Filter Logic: Ensure that the DateFilter is correctly filtering the data. Since the query works without pagination, the filter logic seems correct, but it's worth double-checking that the filter is applied as expected when pagination is added.
Inspect the Query: Use Laravel's query logging or debugging tools like Clockwork or Laravel Debugbar to inspect the actual SQL query being executed when pagination is applied. This can help identify if the query is being altered unexpectedly.
Verify Pagination Parameters: Ensure that the pagination parameters are correctly set. For example, paginate(1) will only return one item per page, which might not be what you expect. Try increasing the number to see if it affects the output.
Check for Empty Results: If the query returns no results, ensure that the filter criteria do not exclude all records. For instance, if there are no upcoming classes, the filter will result in an empty set.
Compare with Working Query: Since you have a similar query that works, compare the two queries to identify any differences in the setup or data that might affect the outcome.
Test Without Filters: Temporarily remove the DateFilter to see if pagination works without it. This can help isolate whether the issue is with the filter or the pagination itself.

Here's a revised version of your code with some additional debugging steps:

return Inertia::render('Classes/Index', [
    'classes' => QueryBuilder::for(OrientationClass::class)
        ->defaultSort('date')
        ->allowedFilters(AllowedFilter::custom('date', new DateFilter, 'date')->default('upcoming'))
        ->paginate(10) // Try a larger number to see more results
        ->withQueryString()
]);

If the issue persists, consider logging the query and results to gain more insight:

$classes = QueryBuilder::for(OrientationClass::class)
    ->defaultSort('date')
    ->allowedFilters(AllowedFilter::custom('date', new DateFilter, 'date')->default('upcoming'))
    ->paginate(10)
    ->withQueryString();

\Log::info('Classes Query', ['query' => $classes->toSql(), 'bindings' => $classes->getBindings()]);
\Log::info('Classes Results', ['results' => $classes->items()]);

return Inertia::render('Classes/Index', [
    'classes' => $classes
]);

By following these steps, you should be able to identify the root cause of the issue and resolve it.

wonder95

1 year ago

Best Answer

Level 5

So the problem was that the data is in classes.data in my Vue component.

Sigh.

Lumethys

1 year ago

Level 5

@wonder95 step 1 of debugging in any framework or language: pinpoint where the error occurs.

What happened: You change the query and see no data in the frontend.

Possible causes: - Query failed - Backend failed somewhere - Backend does not return data - Frontend did not receive data - Frontend failed somewhere

What you assume: Query failed

Which is why it took you so long to find the issue. Had you do a check on the possible cause first, you would have found the issues when you open the networks tab.

Debugging skill is very important, next time when something goes wrong, try thinking about a step-by-step approach, it will save you a lot of time

Please or to participate in this conversation.