Error accessing field in multi-level nested relationship in Query Builder

I am attempting to do some filtering using a multi-level nested relationship (User-> Dues ->Payment is the model hierarchy), like so.

        return Inertia::render('TreasuryReport', [
            'payments' => QueryBuilder::for(Payment::class)
                ->with('dues.user')
                ->allowedFields('dues.user.first_name', 'dues.user.last_name')
                ->allowedFilters([
                    AllowedFilter::exact('mode'),
                    AllowedFilter::callback('name', function(Builder $query, $value) {
                        $query->where('dues.user.first_name', 'like', "%{$value}%")
                            ->orWhere('dues.user.last_name', 'like', "%{$value}%");
                    })
                ])
                ->orderBy('payment_date', 'desc')
                ->paginate(15)
                ->withQueryString()
        ]);

but I get the following error

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'dues.user.first_name' in 'where clause' 

select count(*) as aggregate from `payments` where (`dues`.`user`.`first_name` like %Smith% or `dues`.`user`.`last_name` like %Smith%) and `deleted_at` is null

From what I read in the Query Builder docs and this past issue, this should work.

Am I doing something wrong? Do I need to manually build the join in my callback?

LaryAI

2 years ago

Best Answer

Level 58

The error message suggests that the column 'dues.user.first_name' is not found in the database table. This error occurs because the Query Builder is not able to automatically join the nested relationships.

To fix this issue, you can manually join the nested relationships in your callback function. Here's an example of how you can modify your code to achieve this:

use Illuminate\Database\Eloquent\Builder;
use Spatie\QueryBuilder\AllowedFilter;
use Spatie\QueryBuilder\QueryBuilder;

return Inertia::render('TreasuryReport', [
    'payments' => QueryBuilder::for(Payment::class)
        ->with('dues.user')
        ->allowedFields('dues.user.first_name', 'dues.user.last_name')
        ->allowedFilters([
            AllowedFilter::exact('mode'),
            AllowedFilter::callback('name', function (Builder $query, $value) {
                $query->whereHas('dues.user', function (Builder $query) use ($value) {
                    $query->where('first_name', 'like', "%{$value}%")
                        ->orWhere('last_name', 'like', "%{$value}%");
                });
            })
        ])
        ->orderBy('payment_date', 'desc')
        ->paginate(15)
        ->withQueryString()
]);

In the callback function, we use the whereHas method to manually join the nested relationships. This ensures that the Query Builder can access the correct columns in the database.

By making this modification, the error should be resolved, and you should be able to filter the results based on the nested relationship fields.

1 like

Please or to participate in this conversation.