[Bug]: Making a column sortable doesn't work for translated columns

[Jelmerkeij]

What happened?

Using the GalaxyStore demo as an example here. In ProductResource.php::table() (line 241) set the translation to sortable:

Tables\Columns\TextColumn::make('translation.title')->sortable(),

When sorting in the table this doesn't do anything and I think it has to do with the query that's being built. Using Laravel debug bar we can see the query that's being generated:

SELECT
    *
FROM
    `products`
ORDER BY
    (
    SELECT
        `title`
    FROM
        `product_translations`
    INNER JOIN(
        SELECT
            MAX(`product_translations`.`id`) AS `id_aggregate`,
            MIN(
                `product_translations`.`product_id`
            ) AS `product_id_aggregate`,
            `product_translations`.`product_id`
        FROM
            `product_translations`
        INNER JOIN(
            SELECT
                MAX(
                    `product_translations`.`product_id`
                ) AS `product_id_aggregate`,
                `product_translations`.`product_id`
            FROM
                `product_translations`
            WHERE
                `locale` = 'en' AND `product_translations`.`product_id` IS NULL AND `product_translations`.`product_id` IS NOT NULL
            GROUP BY
                `product_translations`.`product_id`
        ) AS `translation`
    ON
        `translation`.`product_id_aggregate` = `product_translations`.`product_id` AND `translation`.`product_id` = `product_translations`.`product_id`
    WHERE
        `locale` = 'en'
    GROUP BY
        `product_translations`.`product_id`
    ) AS `translation`
ON
    `translation`.`id_aggregate` = `product_translations`.`id` AND `translation`.`product_id_aggregate` = `product_translations`.`product_id` AND `translation`.`product_id` = `product_translations`.`product_id`
WHERE
    `products`.`id` = `product_translations`.`product_id`
)
ASC
LIMIT 10 OFFSET 0;

The part that seems to cause the problem is:

`locale` = 'en' AND `product_translations`.`product_id` IS NULL AND `product_translations`.`product_id` IS NOT NULL

Stating that the product_id must be NULL AND also be NOT NULL at the same time. This never returns any record of course so there's nothing to sort on.

As a workaround I've added a custom sortable like this:

Tables\Columns\TextColumn::make('translation.title')
                    ->sortable(query: function (Builder $query, string $direction): Builder {
                    // Custom sortable to make it work with translation
                    return $query->orderByTranslation('title', $direction);
                }),

How to reproduce the bug

1. Checkout the GalaxyStore demo from https://github.com/CactusGalaxy/GalaxyStoreExample
2. In ProductResource.php::table() (line 241) set the translation to sortable: Tables\Columns\TextColumn::make('translation.title')->sortable(),
3. See the sorting not doing anything on the frontend and check the query to see it's generating a IS NULL AND IS NOT NULL paradox

Package Version

0.2.1

PHP Version

8.3.0

Laravel Version

10.48.8

Which operating systems does with happen with?

macOS

Notes

No response

[jhiguerasa]

Hi,
Is any maintenance expected? or this plgin is abandoned?

[Oleksandr-Moik]

I will try to add it soon. Sorry for the wait 😊

[Oleksandr-Moik]

Ok. How can I help here?)

Yes, the default sorting is now working correctly and we need to change it. I tested it and now it works (why I didn't do it before - I don't know)

CactusGalaxy/GalaxyStoreExample@66cdfff

        Tables\Columns\TextColumn::configureUsing(function (Tables\Columns\TextColumn $column): void {
            if (Str::match('@^translations?\.(\w+)$@', $column->getName())) {
                $column
                    ->searchable(query: function (Builder $query, string $search) use ($column): Builder {
                        $columnName = Str::after($column->getName(), '.');
                        if ($query->hasNamedScope('whereTranslationLike')) {
                            /* @var WithTranslationsTrait|Translatable $query */
                            return $query->whereTranslationLike($columnName, "%{$search}%");
                        }

                        return $query->where($columnName, 'like', "%{$search}%");
                    })
                    ->sortable(query: function (Builder $query, string $direction) use ($column): Builder {
                        $columnName = Str::after($column->getName(), '.');
                        if ($query->hasNamedScope('orderByTranslation')) {
                            /* @var WithTranslationsTrait|Translatable $query */
                            return $query->orderByTranslation($columnName, $direction);
                        }

                        return $query->orderBy($columnName, $direction);
                    });
            }
        });