How to sort by translatable column in a custom module?

vargvinter - 3 weeks ago

Hello,

Like in the title... I would like to sort by a translatable column in a custom module. I know that I should join translations table. Where to start? Please provide some code snippet. Thanks in advance.

vargvinter - 3 weeks ago

Ok, I see that I can put onQuerying method in TableBuilder class and join tables.

    public function onQuerying(Builder $query)
    {
         // Join tables and sort
    }

squatto - 2 weeks ago

Here is an example where I directly queried the translation table of a model. You can use the same process and apply the sort/order by to the translation table.

This was in the repository for a Feature model:

 /**
     * Find a feature by feature type name and feature name
     *
     * @param string $featureTypeName
     * @param string $featureName
     *
     * @return null|FeatureModel
     */
    public function findByFeatureTypeAndName($featureTypeName, $featureName)
    {
        $featureType = $this->featureType->findByName($featureTypeName);
        if (! $featureType) {
            throw new ModelNotFoundException("Feature type '$featureTypeName' not found");
        }
        $result = DB::table('products_features_translations')
            ->select('entry_id')
            ->where('name', $featureName)
            ->join('products_features', 'products_features_translations.entry_id', '=', 'products_features.id')
            ->where('products_features.feature_type_id', $featureType->id)
            ->first();
        return ($result ? $this->find($result->entry_id) : null);
    }

squatto - 2 weeks ago

If you need more insight into how to apply the sort I would be happy to help.

vargvinter - 2 weeks ago

Thanks @squatto I have applied onQuerying method in TableBuilder class. Something like this:

    public function onQuerying($query)
    {
        $order_by = request('order_by') ?: 'name';
        $sort = request('sort') ?: 'asc';

        return $query
            ->join('portfolio_objects_translations', 'portfolio_objects.id', '=', 'portfolio_objects_translations.entry_id')
            ->where('portfolio_objects_translations.locale', config('app.locale'))
            ->orderBy($order_by, $sort);
    }

squatto - 2 weeks ago

Great idea! That's a good way to dynamically handle sorting.

alanbruce - 2 weeks ago

Great idea. Nice work done. Rare Wine Prices , Collectible Wine Prices , Wine Auction Houses

ryanthompson - 2 weeks ago

You can select a joined column as a name - and then in your table column definition use sortable_column = your_custom_select_col.

vargvinter - 2 weeks ago

Do you mean this?

    /**
     * The table columns.
     *
     * @var array|string
     */
    protected $columns = [
        'name' => [
            'sort_column' => 'name'
        ]
    ];