How to sort by translatable column in a custom module?
Created 4 years ago by vargvinter


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  —  4 years 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  —  4 years 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')
            ->where('name', $featureName)
            ->join('products_features', 'products_features_translations.entry_id', '=', '')
            ->where('products_features.feature_type_id', $featureType->id)
        return ($result ? $this->find($result->entry_id) : null);
squatto  —  4 years ago

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

vargvinter  —  4 years 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_translations.entry_id')
            ->where('portfolio_objects_translations.locale', config('app.locale'))
            ->orderBy($order_by, $sort);
squatto  —  4 years ago

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

alanbruce  —  4 years ago
ryanthompson  —  4 years 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  —  4 years ago

Do you mean this?

     * The table columns.
     * @var array|string
    protected $columns = [
        'name' => [
            'sort_column' => 'name'
mitch  —  4 years ago

I added the following onQuerying method and was able to subsequently use the column in protected $options['order_by']: public function onQuerying($query){ return $query ->join('events_categories_translations','', '=', 'events_categories_translations.entry_id') ->where('events_categories_translations.locale',config('app.locale')) ; }