laravel-livewire-tables Relationship sorting issue

Hello guys, I'm experiencing an issue with the relationship sorting.

In my case I have:

Order model (various fields including order_status_id)
Order status model (id, name)

The Order model has a relationship method status() to retrieve the order's status.

I have set up the column for the datatable in this way:


public function columns(): array
    {
        return [
            Column::make("ID", "id")
                ->format(
                    fn($value, $row, Column $column) => '<strong>#'.$row->id.'</strong>'
                )
                ->html()
                ->sortable(),
            Column::make("Order status id", "status.name")
                ->searchable()
                ->sortable(),
            ]
}

My issue is that the resulting sort is wrong. Debugging the query in Debug bar I see that the generated query is the below:

SELECT
	`orders`.`id` AS `id`,
	`order_statuses`.`name` AS `status.name`
FROM
	`orders`
	LEFT JOIN `order_statuses` ON `orders`.`order_status_id` = `order_statuses`.`id`
ORDER BY
	"status.name" ASC
LIMIT 100 OFFSET 0

I believe that the error is related to the LIMIT/OFFSET in the query. Should I use a custom sortable function with a subquery?

Sep 15 '22 09:09 nocodelab

I think the issue is the " " around "status.name". If you changed the "status.name" to status.name the query will work in mySql, but doesn't work in the datatable.

Sep 16 '22 15:09 Rich594

I think the issue is the " " around "status.name". If you changed the "status.name" to status.name the query will work in mySql, but doesn't work in the datatable.

Well, not actually. In that case the sort will be correct but If the LIMIT is smaller than the total record it will display just a chunk of the data.

See the examples below: Screenshot 2022-09-16 at 17 44 01