Change formatting of relational column to allow proper sorting.

[korki696]

All Submissions:

• [x] Have you followed the guidelines in our Contributing document?
• [x] Have you checked to ensure there aren't other open Pull Requests for the same update/change?

New Feature Submissions:

1. [x] Does your submission pass tests and did you add any new tests needed for your feature?
2. [ ] Did you update all templates (if applicable)?
3. [ ] Did you add the relevant documentation (if applicable)?
4. Did you test locally to make sure your feature works as intended?

Changes to Core Features:

• [x] Have you added an explanation of what your changes do and why you'd like us to include them?
• [ ] Have you written new tests for your core changes, as applicable?
• [x] Have you successfully ran tests with your changes locally?

When sorting a relational column, the table wouldn't actually sort. Changing the query to use a backquote (`) instead of a double quote (") seems to fix this.

[rappasoft]

We need a way for this to both work in MySQL and postgres. Personally, I can't replicate this though.

[korki696]

I am running on MySQL8 and even just a raw query will only work with backquotes. I don't have a Postgres spun up or am to familiar with it. Are you able to sort a relational column on MySQL with the current implementation?

[rappasoft]

Should we do a conditional and just set it differently for the different drivers?

[damiandejong]

Dear Anthony,

This pull request deals with the same problem I described in my reply to #734. As this pull request is just rewinding the changes in #734, I agree the query should be defined conditionally by the database driver being used.

Therefore, I made a change that uses the Laravel logic for query grammar. Maybe there is a simpeler approach, but I believe in this way, the proper alias/column is used and the quotes are set depending on the database driver. I haven't been able to test it for Postgresql, but for MySQL it works.

I wanted to create a pull request for it, but since this one is still open, I thought it might be better to put it here first. Please let me know if I need to create a pull request anyway.

https://github.com/ddejong98/laravel-livewire-tables/commit/ad96a80bcbc215ab06a41ee5a8eff02424301758

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[mitulgolakiya]

Facing the same issue. I had a column with relationship sorting. Everything was working fine for version 2.1.1. After upgrading to 2.2.0+, none of the related columns are working.

Seems like the problem is when order by column name is replaced with double quotes " instead of `, then sorting is stopped with MySQL.

[vishalinfyom]

Yes, facing same issue.

after upgrading to latest version in MySQL, sorting is not working.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[bskiefer]

I was able to resolve by adding this method to my controllers:

    public function getRows()
    {
        $this->baseQuery();
        $query = $this->getBuilder()->getQuery();
        $orders = $query->orders;
        $query->orders = [];

        foreach ($orders as $order) {
            $query->orders[] = [
                'type' => 'Raw',
                'sql' => str_replace('"', '`', $order['sql'])
            ];
        }
        $this->getBuilder()->setQuery($query);

        return $this->executeQuery();
    }

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[rappasoft]

Closing this in favor of this fix: https://github.com/ddejong98/laravel-livewire-tables/commit/ad96a80bcbc215ab06a41ee5a8eff02424301758