Sort by relationship column

[artemmolotov]

• Laravel Version: 5.5.34
• Voyager Version: 1.0.15
• PHP Version: 7.1.0
• Database Driver & Version: mysql

Description:

Sorting by relationships, not work for me. Maybe it's because of my changes, but I think I saw the problem after installation too. Can you check if the sorting works for you?

We can see getRelationships method where I found next code:

        $relationships = [];

        $dataType->browseRows->each(function ($item) use (&$relationships) {
            $details = json_decode($item->details);
            if (isset($details->relationship) && isset($item->field)) {
                 ...
            }
        });

        return $relationships;

But in relationship details:

{"model":"App\\Product","table":"products","type":"belongsTo","column":"product_id","key":"id","label":"name","pivot_table":"admins","pivot":"0"}

Steps To Reproduce:

1. Create relationship (https://voyager.readme.io/docs/relationships)
2. Sort table by new relationship

[vblinden]

Could you further explain what you mean with "Sort table by new relationship"? This is unclear to me. Thanks!

[artemmolotov]

When you click on 'Продукт' nothing happens.

Link

After 2-3 hours I wrote temporary solution. My code is very bad now. It's based on left join...

Link

[artemmolotov]

Sort = Order by

[artemmolotov]

I forgot to say that the problem is with server-side rendering and "normal" relationships. With the recent PR (#2871), when creating relationships through column options, sorting works.

[anhvu-hcmiu]

Hi @ArtemMolotov , currently I am running into the same problem you suggested above. Could you please provide a more clear solution to this problem? As I understand, modify the code like #2871 and set relationship though column options will solve the problem, is it right? If that is the case, how can I set relationship through column options? Thank you in advance.

P/s: Is "normal relationship" https://voyager.readme.io/docs/relationships?

[artemmolotov]

Hi!

As I understand, modify the code like #2871 and set relationship though column options will solve the problem, is it right?

I do not remember exactly, but most likely you are right.

If that is the case, how can I set relationship through column options?

https://voyager.readme.io/v0.11/docs/relationships

Is "normal relationship" https://voyager.readme.io/docs/relationships?

Yes. Currently I wrote controller for all breads and overrode index method with next code:

Some code of the 'index' method

```php ... if ($orderBy && $row = $dataType->rows->firstWhere('field', $orderBy)) { $querySortOrder = (!empty($sortOrder)) ? $sortOrder : 'DESC'; if ($row->type === 'relationship'){ $details = json_decode($row->details); $tmpOrderBy = $orderBy; $orderBy = "{$details->table}.{$details->label}"; $model->belongsTo($details->model); if (!static::isJoined($query, $details->table)){ /** @var \Illuminate\Database\Eloquent\Builder $query */ $query = $query->leftJoin($details->table, $details->column, '=', $details->table . '.' . $details->key) ->select($query->getQuery()->from.'.*'); } } else { $tmpOrderBy = $orderBy; $orderBy = "{$model->getTable()}.{$orderBy}"; } $dataTypeContent = call_user_func([ $query->orderBy($orderBy, $querySortOrder), $getter, ]); $orderBy = $tmpOrderBy; } elseif ($model->timestamps) { $dataTypeContent = call_user_func([$query->latest($model->getTable() . '.' . $model::CREATED_AT), $getter]); } else { $dataTypeContent = call_user_func([$query->orderBy($model->getTable() . '.' . $model->getKeyName(), 'DESC'), $getter]); } ... ```

All code of the 'index' method

```php public function index(Request $request) { // GET THE SLUG, ex. 'posts', 'pages', etc. $slug = $this->getSlug($request); // GET THE DataType based on the slug $dataType = Voyager::model('DataType')->where('slug', '=', $slug)->first(); // Check permission $this->authorize('browse', app($dataType->model_name)); $getter = $dataType->server_side ? 'paginate' : 'get'; $search = (object) ['value' => $request->get('s'), 'key' => $request->get('key'), 'filter' => $request->get('filter')]; $searchable = $dataType->server_side ? array_keys(SchemaManager::describeTable(app($dataType->model_name)->getTable())->toArray()) : ''; $orderBy = $request->get('order_by'); $sortOrder = $request->get('sort_order', null); // Next Get or Paginate the actual content from the MODEL that corresponds to the slug DataType if (strlen($dataType->model_name) != 0) { $relationships = $this->getRelationships($dataType); $model = app($dataType->model_name); $query = $model::select('*')->with($relationships); // If a column has a relationship associated with it, we do not want to show that field $this->removeRelationshipField($dataType, 'browse'); if ($search->value && $search->key && $search->filter) { $search_filter = ($search->filter == 'equals') ? '=' : 'LIKE'; $search_value = ($search->filter == 'equals') ? $search->value : '%'.$search->value.'%'; $query->where($search->key, $search_filter, $search_value); } if ($orderBy && $row = $dataType->rows->firstWhere('field', $orderBy)) { $querySortOrder = (!empty($sortOrder)) ? $sortOrder : 'DESC'; if ($row->type === 'relationship'){ $details = json_decode($row->details); $tmpOrderBy = $orderBy; $orderBy = "{$details->table}.{$details->label}"; $model->belongsTo($details->model); if (!static::isJoined($query, $details->table)){ /** @var \Illuminate\Database\Eloquent\Builder $query */ $query = $query->leftJoin($details->table, $details->column, '=', $details->table . '.' . $details->key) ->select($query->getQuery()->from.'.*'); } } else { $tmpOrderBy = $orderBy; $orderBy = "{$model->getTable()}.{$orderBy}"; } $dataTypeContent = call_user_func([ $query->orderBy($orderBy, $querySortOrder), $getter, ]); $orderBy = $tmpOrderBy; } elseif ($model->timestamps) { $dataTypeContent = call_user_func([$query->latest($model->getTable() . '.' . $model::CREATED_AT), $getter]); } else { $dataTypeContent = call_user_func([$query->orderBy($model->getTable() . '.' . $model->getKeyName(), 'DESC'), $getter]); } // Replace relationships' keys for labels and create READ links if a slug is provided. $dataTypeContent = $this->resolveRelations($dataTypeContent, $dataType); } else { // If Model doesn't exist, get data from table name $dataTypeContent = call_user_func([DB::table($dataType->name), $getter]); $model = false; } // Check if BREAD is Translatable if (($isModelTranslatable = is_bread_translatable($model))) { $dataTypeContent->load('translations'); } // Check if server side pagination is enabled $isServerSide = isset($dataType->server_side) && $dataType->server_side; $view = 'voyager::bread.browse'; if (view()->exists("voyager::$slug.browse")) { $view = "voyager::$slug.browse"; } return Voyager::view($view, compact( 'dataType', 'dataTypeContent', 'isModelTranslatable', 'search', 'orderBy', 'sortOrder', 'searchable', 'isServerSide' )); } ```

'isJoined' method

```php /** * @param \Illuminate\Database\Eloquent\Builder $query * @param string $table * * @return bool */ public static function isJoined($query, $table){ $joins = collect($query->getQuery()->joins); return $joins->pluck('table')->contains($table); } ```

 

This works in Voyager v1.0, in v1.1 the code has been radically changed. BUT be careful, because at the beginning I had a bug - the data of the first table was replaced by the data of the second table if the column names were the same. It seems to me that I solved this problem.

You can set custom controller in BREAD Builder or "using custom HTTP controllers" (or change routes).

[artemmolotov]

@anhvu-hcmiu, please check this solution if you have the time. Thank you!

[anhvu-hcmiu]

@ArtemMolotov Thank you so much Artem, I have tried the leftJoin method you suggested and everything works just fine. However, this is still a bug in Voyager right? If we do not customize the index method, the sort won't work given the relationship which has been set?

[artemmolotov]

@anhvu-hcmiu

However, this is still a bug in Voyager right? If we do not customize the index method, the sort won't work given the relationship which has been set?

Yes, this is a bug. You can use one controller for all breads (or 2+ controllers with your custom FixBreadRelationshipSort trait).

[Stehos]

Is there any news regarding this issue and voyager 1.4.2? Thank you.

[MrCrayon]

@Stehos you can check this PR #5120

[github-actions[bot]]

This issue has been automatically locked since there has not been any recent activity after it was closed. If you have further questions please ask in our Slack group.