Admin serverside search resulting in Unknown column 'posts.post_belongsto_category_relationship'

[nathanpayne]

Laravel version

8.52.0

PHP version

7.4.9

Voyager version

1.x-dev aaee6ab

Database

MySQL 8.0.22

Description

While attempting to filter blogs based on category name using the supplied UI I get an error stating:

_Column not found: 1054 Unknown column 'posts.post_belongsto_category_relationship' in 'where clause' (SQL: select count(*) as aggregate from posts where posts.post_belongsto_category_relationship LIKE %cat%)_

Steps to reproduce

1. Using the Dummy Data change Posts to server-side and Categories to Browse
2. Go to Posts on Admin dashboard
3. Use filter drop down to select Categories, then contains, then use any search string (I used cat in my example)
4. Error is reproduced stating an unknown column.

Expected behavior

Posts should filter based on the search string within the input across the belongsto relationship.

Screenshots

No response

Additional context

No response

[jegardiner]

I am experiencing the same the same issue, but I noticed that you can search for the related display value by choosing the foreign key field from the dropdown, rather than the name of the displayed field, in the search controls.

For example, if you had models of cars related to makers, you can filter the cars by choosing "maker_id" as the search field, and then entering the display value you're looking for (e.g. "Ford").

This is counter intuitive, as you would expected a search on the foreign key field (e.g. "maker_id") to be a search for an ID number, but a search selecting the display field of the relationship results in the error mentioned by the OP.

However, As a quick and dirty solution - until someone much smarter than me can solve it properly - I overrode the index method of VoyagerBaseController and replaced lines 50-55 with:

$searchNames = [];
if ($dataType->server_side) {
    $searchRows = $dataType->browseRows->filter(function ($item, $key) {
        return $item->type !== 'relationship';
    });
    $searchNames = $searchRows->mapWithKeys(function ($row) {
        return [$row['field'] => $row->getTranslatedAttribute('display_name')];
    });
}

This removes the relationship display fields from the list of searchable fields that are passed to the view, avoiding the error condition. You can then change the "Display Name" of the foreign key field in the bread to remove "_id" (e.g. display "maker_id" as "Maker") and make the search more intuitive for the user.

[SneakyGerald]

I was able to fix it. You need to change your index method of VoyagerBaseController and replace:

if ($dataType->server_side) {
    $searchNames = $dataType->browseRows->mapWithKeys(function ($row) {
        return [$row['field'] => $row->getTranslatedAttribute('display_name')];
    });
}

By:

if ($dataType->server_side) {
    $searchNames = $dataType->browseRows->mapWithKeys(function ($row) {
        return $row['type'] == 'relationship'
                    ? [$row['details']->column => $row->getTranslatedAttribute('display_name')]
                    : [$row['field'] => $row->getTranslatedAttribute('display_name')];
        });
}

Also You need to change this line: $row->details->model::where($row->details->label, $search_filter, $search_value)->pluck('id')->toArray()

To: $row->details->model::where($row->details->label, $search_filter, $search_value)->pluck($row->details->key)->toArray()

Now, in your User bread you can search for example by role relationship using the value of the field you set up in your relationship, for example: 'Admin' that would be the value of the field display_name in role table.

[EmaX093]

@SneakyGerald this works perfectly, thank you men!

[enzi-dev]

change row on findSearchableRelationshipRow

if ($item->details->type != 'belongsTo' && $item->details->type != 'belongsToMany') {
    return false;
}

[Mazharqwe]

@SneakyGerlad thanks al lot working now

[Mazharqwe]

Make sure that, you have project_notes column in db table

On Thu, Sep 29, 2022, 9:00 PM Muhammad Omar @.***> wrote:

Hi, Surprisingly, I am also getting same issue in PHP 8.1.11 Laravel 9.17.0 Voyager 1.6.x-dev

Can anybody please help? I have already tried above suggestions in "VoyagerBaseController.php" that I have overridden in my 'app\Http\Controllers' folder but it is not working ...

[image: image] https://user-images.githubusercontent.com/10277690/193081019-6db1e741-9395-424b-8263-a4380a26bc42.png

Thanks.

— Reply to this email directly, view it on GitHub https://github.com/the-control-group/voyager/issues/5412#issuecomment-1262487822, or unsubscribe https://github.com/notifications/unsubscribe-auth/AHN3Q72AJVWLNCU7CMAUFNTWAW4KPANCNFSM5BFPMFRQ . You are receiving this because you commented.Message ID: @.***>

[Mazharqwe]

I was able to fix it. You need to change your index method of VoyagerBaseController and replace:

if ($dataType->server_side) { $searchNames = $dataType->browseRows->mapWithKeys(function ($row) { return [$row['field'] => $row->getTranslatedAttribute('display_name')]; }); }

By:

if ($dataType->server_side) { $searchNames = $dataType->browseRows->mapWithKeys(function ($row) { return $row['type'] == 'relationship' ? [$row['details']->column => $row->getTranslatedAttribute('display_name')] : [$row['field'] => $row->getTranslatedAttribute('display_name')]; }); }

Also You need to change this line: $row->details->model::where($row->details->label, $search_filter, $search_value)->pluck('id')->toArray()

To: $row->details->model::where($row->details->label, $search_filter, $search_value)->pluck($row->details->key)->toArray()

Now, in your User bread you can search for example by role relationship using the value of the field you set up in your relationship, for example: 'Admin' that would be the value of the field display_name in role table.

On Thu, Sep 29, 2022, 9:00 PM Muhammad Omar @.***> wrote:

Hi, Surprisingly, I am also getting same issue in PHP 8.1.11 Laravel 9.17.0 Voyager 1.6.x-dev

Can anybody please help? I have already tried above suggestions in "VoyagerBaseController.php" that I have overridden in my 'app\Http\Controllers' folder but it is not working ...

[image: image] https://user-images.githubusercontent.com/10277690/193081019-6db1e741-9395-424b-8263-a4380a26bc42.png

Thanks.

— Reply to this email directly, view it on GitHub https://github.com/the-control-group/voyager/issues/5412#issuecomment-1262487822, or unsubscribe https://github.com/notifications/unsubscribe-auth/AHN3Q72AJVWLNCU7CMAUFNTWAW4KPANCNFSM5BFPMFRQ . You are receiving this because you commented.Message ID: @.***>

[samik-os]

I created a trait for this very purpose and use that trait in controllers that are using relation fields with server-side pagination enabled in BREAD. Then have the controller extend \TCG\Voyager\Http\Controllers\VoyagerBaseController. In BREAD manager, the custom controller needs to be set.

Also this adds support for explicitly mentioning the relationship name in Voyager Relationship Details like so

{
    "relation": "user"
}

otherwise it will just try to figure out the relationship name from the model. For this to work the relationship method must be defined in the model.

trait PatchedVoyagerBaseController 
{
    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')];

        $searchNames = [];
        if ($dataType->server_side) {
            $searchNames = $dataType->browseRows->mapWithKeys(function ($row) {
                return [$row['field'] => $row->getTranslatedAttribute('display_name')];
            });
        }

        $orderBy = $request->get('order_by', $dataType->order_column);
        $sortOrder = $request->get('sort_order', $dataType->order_direction);
        $usesSoftDeletes = false;
        $showSoftDeleted = false;

        // Next Get or Paginate the actual content from the MODEL that corresponds to the slug DataType
        if (strlen($dataType->model_name) != 0) {
            $model = app($dataType->model_name);

            $query = $model::select($dataType->name.'.*');

            if ($dataType->scope && $dataType->scope != '' && method_exists($model, 'scope'.ucfirst($dataType->scope))) {
                $query->{$dataType->scope}();
            }

            // Use withTrashed() if model uses SoftDeletes and if toggle is selected
            if ($model && in_array(SoftDeletes::class, class_uses_recursive($model)) && Auth::user()->can('delete', app($dataType->model_name))) {
                $usesSoftDeletes = true;

                if ($request->get('showSoftDeleted')) {
                    $showSoftDeleted = true;
                    $query = $query->withTrashed();
                }
            }

            // 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.'%';

                $searchField = $dataType->name.'.'.$search->key;
                if ($row = $this->findSearchableRelationshipRow($dataType->rows->where('type', 'relationship'), $search->key)) {
                    $query->whereRelation($row->relation, $row->field, $search_filter, $search_value);
                } else {
                    if ($dataType->browseRows->pluck('field')->contains($search->key)) {
                        $query->where($searchField, $search_filter, $search_value);
                    }
                }
            }

            $row = $dataType->rows->where('field', $orderBy)->firstWhere('type', 'relationship');
            if ($orderBy && (in_array($orderBy, $dataType->fields()) || !empty($row))) {
                $querySortOrder = (!empty($sortOrder)) ? $sortOrder : 'desc';
                if (!empty($row)) {
                    $query->select([
                        $dataType->name.'.*',
                        'joined.'.$row->details->label.' as '.$orderBy,
                    ])->leftJoin(
                        $row->details->table.' as joined',
                        $dataType->name.'.'.$row->details->column,
                        'joined.'.$row->details->key
                    );
                }

                $dataTypeContent = call_user_func([
                    $query->orderBy($orderBy, $querySortOrder),
                    $getter,
                ]);
            } elseif ($model->timestamps) {
                $dataTypeContent = call_user_func([$query->latest($model::CREATED_AT), $getter]);
            } else {
                $dataTypeContent = call_user_func([$query->orderBy($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
        $isModelTranslatable = is_bread_translatable($model);

        // Eagerload Relations
        $this->eagerLoadRelations($dataTypeContent, $dataType, 'browse', $isModelTranslatable);

        // Check if server side pagination is enabled
        $isServerSide = isset($dataType->server_side) && $dataType->server_side;

        // Check if a default search key is set
        $defaultSearchKey = $dataType->default_search_key ?? null;

        // Actions
        $actions = [];
        if (!empty($dataTypeContent->first())) {
            foreach (Voyager::actions() as $action) {
                $action = new $action($dataType, $dataTypeContent->first());

                if ($action->shouldActionDisplayOnDataType()) {
                    $actions[] = $action;
                }
            }
        }

        // Define showCheckboxColumn
        $showCheckboxColumn = false;
        if (Auth::user()->can('delete', app($dataType->model_name))) {
            $showCheckboxColumn = true;
        } else {
            foreach ($actions as $action) {
                if (method_exists($action, 'massAction')) {
                    $showCheckboxColumn = true;
                }
            }
        }

        // Define orderColumn
        $orderColumn = [];
        if ($orderBy) {
            $index = $dataType->browseRows->where('field', $orderBy)->keys()->first() + ($showCheckboxColumn ? 1 : 0);
            $orderColumn = [[$index, $sortOrder ?? 'desc']];
        }

        // Define list of columns that can be sorted server side
        $sortableColumns = $this->getSortableColumns($dataType->browseRows);

        $view = 'voyager::bread.browse';

        if (view()->exists("voyager::$slug.browse")) {
            $view = "voyager::$slug.browse";
        }

        return Voyager::view($view, compact(
            'actions',
            'dataType',
            'dataTypeContent',
            'isModelTranslatable',
            'search',
            'orderBy',
            'orderColumn',
            'sortableColumns',
            'sortOrder',
            'searchNames',
            'isServerSide',
            'defaultSearchKey',
            'usesSoftDeletes',
            'showSoftDeleted',
            'showCheckboxColumn'
        ));
    }

    protected function findSearchableRelationshipRow($relationshipRows, $searchKey)
    {
        $row = $relationshipRows->filter(function ($item) use ($searchKey) {
            if ($item->field != $searchKey) {
                return false;
            }
            if ($item->details->type != 'belongsTo') {
                return false;
            }

            return !$this->relationIsUsingAccessorAsLabel($item->details);
        })->first();

        $relation = $row->details->relation ?? \Str::camel(class_basename(app($row->details->model)));

        return (object) ['relation' => $relation, 'field' => $row->details->label];
    }
}

[bhargavraviya]

find this line

if ($search->value != '' && $search->key && $search->filter) {

change like this for custom logic

     if ($search->value != '' && $search->key && $search->filter) {
         if ($search->key == 'log_customers_activity_belongsto_customer_relationship_1') {
                $query->whereHas('customer', function ($query) use ($search) {
                    if ($search->filter == 'equals') {
                        $query->where('name', $search->value);
                    } else {
                        $query->where('name', 'LIKE', '%' . $search->value . '%');
                    }
                });
            } 
        }
...

add model

public function customer(): BelongsTo
    {
        return $this->belongsTo(Customer::class, 'user_id');
    }

[ayhamnajem]

Unfortunately, none of the above solutions provide the same search experience that worked before.

And looking at the VoyagerBaseController, I see no changes that should produce this error. Is there a clean solution that make it work normally?

[liemarr]

可惜的是我用最新版这个问题也是没有解决，不过@SneakyGerald 完美解决了问题，我安装最新版的还是这样的问题。