Accessing third level relationship field in Spatie QueryBuilder filter query

In my app I have a relationship hierarchy of User -> Dues -> Payments, where each relationship is a one to many (e.g. User hasMany Dues and Dues belongsTo User). A Dues can have multiple Payments. The dues model has a nra_dues column, and so I need to get records where nra_dues >0, and then I need to get the payment_date column from the payments table. I am using the Spatie Laravel Query Builder, and so have the following query generated.

        return Inertia::render('Reports/NRA', [
            'payments' => NRAPaymentResource::collection(
                QueryBuilder::for(User::class)
                    ->whereRelation('dues', 'nra_dues', '>', 0)
                    ->with('dues.payments')
                    ->with('profile')
                    ->allowedFilters([
                        AllowedFilter::callback('between', function (Builder $query, $value) {
                            $query->whereBetween('dues.payments.payment_date', [$value[0], $value[1]]);
                        })
                    ])
                    ->paginate(20)
                    ->withQueryString())
        ]);

The issue I'm having is accessing the payment_date column. When I run this query, I get the error

Column not found: 1054 Unknown column 'dues.payments.payment_date' in 'where clause'

When I look at the generated query, it's obvious why it's throwing this error

select count(*) as aggregate from `users` where exists (select * from `dues` where `users`.`id` = `dues`.`user_id` and `nra_dues` > 0 and `dues`.`deleted_at` is null) and `dues`.`payments`.`payment_date` between 2023-09-01 and 2023-09-15 and `users`.`deleted_at` is null

because the payments table is not included in the from clause. From what I've read in the docs, I'm loading the relationships correctly.

What do I need to do to be able to query the payment_date field in the payments table?

LaryAI

2 years ago

Level 58

To query the payment_date field in the payments table, you need to adjust your filter to correctly reference the nested relationship. Since you are dealing with a nested relationship (dues.payments), you cannot directly use whereBetween on the dues.payments.payment_date as if it were a column on the dues table.

Instead, you should use the whereHas method to apply a constraint to the payments relationship. Here's how you can modify your query:

return Inertia::render('Reports/NRA', [
    'payments' => NRAPaymentResource::collection(
        QueryBuilder::for(User::class)
            ->whereRelation('dues', 'nra_dues', '>', 0)
            ->with('dues.payments')
            ->with('profile')
            ->allowedFilters([
                AllowedFilter::callback('between', function (Builder $query, $value) {
                    $query->whereHas('dues.payments', function (Builder $query) use ($value) {
                        $query->whereBetween('payment_date', [$value[0], $value[1]]);
                    });
                })
            ])
            ->paginate(20)
            ->withQueryString()
    )
]);

In this solution, the whereHas method is used to add a constraint to the payments relationship that is nested within the dues relationship. The whereBetween method is then used within the closure to filter the payment_date within the specified range.

This should resolve the error you're encountering and allow you to filter the payment_date from the payments table correctly.

1 like

wonder95

2 years ago

Level 5

OK, that got past the error, but now I get no results returned as payments. If I run the generated query, it returns a count of 14 which is correct, but no records.

Please or to participate in this conversation.