Unable to add date sort from related fields with collection and Spatie Query Builder

I have a controller method that just generates items for a report

    public function report(): Response
    {
        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()
        ]);
    }

and I need to add date filters and the use of a collection. This works

    public function report(Request $request): Response
    {
        $filter = $request->query("filter") ?? '';
        $between = $filter === "" ? "" : explode(',', $filter['between']) ?? '';

        return Inertia::render('Reports/TreasuryReport', [
            'payments' => TreasuryReportResource::collection(QueryBuilder::for(Dues::class)
                    ->allowedIncludes(['payments', 'user'])
                    ->allowedFilters([
                        AllowedFilter::callback('between', function (Builder $query, $value) {
                            $query->whereHas('payments', function (Builder $query) use ($value) {
                                $query->whereBetween('payment_date', [$value[0], $value[1]]);
                            });
                        })
                    ])
                    ->paginate(25)
                    ->withQueryString()),
            'filters' => $between
        ]);
    }

However, I also want to add a date sort (both as default, and as a sortable field from the UI), which is using the payment_date column of the child Payment model (see this question for details). Looking at the code, AllowedSort also has a callback method (just like AllowedFilter), and so attempting to add that, I come up with this

    public function report(Request $request): Response
    {
        $filter = $request->query("filter") ?? '';
        $between = $filter === "" ? "" : explode(',', $filter['between']) ?? '';

        return Inertia::render('Reports/TreasuryReport', [
            'payments' => TreasuryReportResource::collection(QueryBuilder::for(Dues::class)
                    ->allowedIncludes(['payments', 'user'])
                    ->allowedFilters([
                        AllowedFilter::callback('between', function (Builder $query, $value) {
                            $query->whereHas('payments', function (Builder $query) use ($value) {
                                $query->whereBetween('payment_date', [$value[0], $value[1]]);
                            });
                        })
                    ])
                    ->defaultSort('paymentDate') // Default sort descending
                    ->allowedSorts([
                        AllowedSort::callback('paymentDate', function(Builder $query) {
                            $query->whereHas('payments', function (Builder $query) {
                                $query->orderBy('payments.payment_date', 'desc');
                            });

                        })
                    ])
                    ->paginate(25)
                    ->withQueryString()),
            'filters' => $between
        ]);
    }

However, when I run that I get this error

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'paymentDate' in 'order clause'

However, PaymentDate is the name of the filter, not the field, which almost seems to me like a bug. Am I seeing that correctly?

But even if I just want to add a default sort, as I had in the original code with ->orderBy(), I get an error if I try that

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

even though I had the relationships included. How can I just add a default sort?

LaryAI

2 years ago

Level 58

It seems like you're trying to sort a collection of Dues based on a related Payment model's payment_date field. The error you're encountering is because the orderBy clause is being applied directly to the Dues query, which doesn't have a payment_date column.

To sort by a related model's column, you need to join the tables or use a subquery. Since you're using Spatie's Query Builder, you can use the join method to join the related payments table and then sort by the payment_date column.

Here's how you can modify your report method to include the default sort by payment_date:

public function report(Request $request): Response
{
    $filter = $request->query("filter") ?? '';
    $between = $filter === "" ? "" : explode(',', $filter['between']) ?? '';

    return Inertia::render('Reports/TreasuryReport', [
        'payments' => TreasuryReportResource::collection(QueryBuilder::for(Dues::class)
                ->allowedIncludes(['payments', 'user'])
                ->allowedFilters([
                    AllowedFilter::callback('between', function (Builder $query, $value) {
                        $query->whereHas('payments', function (Builder $query) use ($value) {
                            $query->whereBetween('payment_date', [$value[0], $value[1]]);
                        });
                    })
                ])
                ->join('payments', 'dues.id', '=', 'payments.dues_id') // Join the payments table
                ->defaultSort('payments.payment_date') // Default sort descending
                ->allowedSorts([
                    AllowedSort::field('paymentDate', 'payments.payment_date') // Sort by payment_date
                ])
                ->paginate(25)
                ->withQueryString()),
        'filters' => $between
    ]);
}

Please note the following changes:

I've added a join clause to join the payments table with the dues table on the dues.id and payments.dues_id columns.
I've changed the defaultSort and allowedSorts to use the actual column name payments.payment_date instead of the filter name paymentDate.
The AllowedSort::field method is used to specify the column name directly.

Make sure that the dues.id and payments.dues_id are the correct columns for joining the tables based on your database schema.

This should resolve the SQL error you're encountering and allow you to sort the Dues collection by the payment_date of the related Payment model.

wonder95

2 years ago

Level 5

Ok, that works, but to an extent: the code works without the error. However, as I stated, I want sort by descending order, so per the docs,

You can use - if you want to have the default order sorted descendingly.

// GET /users
$users = QueryBuilder::for(User::class)
    ->defaultSort('-name')
    ->allowedSorts('name', 'street')
    ->get();

but I try this

->defaultSort('-payments.payment_date')

I get an error later on in my resource because there are no payments records. However, if I just take away the -, I get the results as expected.

Even if I try with just ->orderByDesc(), I get no records in my resource, as if just trying to do a descending sort negates the results. What am I missing?

Please or to participate in this conversation.