Making a deep related field searchable in OctoberCMS

Question

My my columns.yaml file for the Invoice model I have the following column:

transaction[user][email]:
    label: Login email
    type: text
    searchable: true // doesn't work!

Unfortunately, the searchable bit doesn't work and I understand that this is because I am using transaction[user][email] instead of having a relation and select as part of the column.

My question is, can I somehow use relation and select when I'm going 2 relations deep, like this, or do I need a different approach?

My Invoice model has this relation defined:

public $morphOne = [
    'transaction' => ['Namespace\Plugin\Models\Transaction', 'name' => 'transactionable']
];

And my Transaction model has this relation defined:

public $belongsTo = [
    'user' => ['Rainlab\User\Models\User'],
];

So, essentially, I want to be able have a backend list of invoices with the user's email address showing in one of the columns, and also make it possible for an email address to be entered in the search box in order to filter the list only for invoices associated with the user with that email address.

score 4 · Accepted Answer · answered Jan 24 '18 at 22:07

Hmm, I tried one demo in my local and its working just fine.

In Your Controller add this code we are going to extend query to add our custom field which can be searchable even its 2 relation deeper

<?php

use Rainlab\User\Models\User;
use Namespace\Plugin\Models\Invoice;
use Namespace\Plugin\Models\Transaction;

... your controller code ...

public function listExtendQuery($query)
{

    $invoiceTableName = (new Invoice)->getTable();
    $transactionTableName = (new Transaction)->getTable();
    $userTableName = (new User)->getTable();

    $query->addSelect($userTableName . '.email as email');

    $query->leftJoin($transactionTableName, $invoiceTableName . '.id', '=', $transactionTableName . '.invoice_id');
    $query->leftJoin($userTableName, $userTableName . '.id', '=', $transactionTableName . '.user_id');

}

...

and now inside your columns.yaml add this

columns:
    id:
        label: id
        type: number
        invisible: false

    ... other columns 

    email:
        label: user_email
        type: text
        default: none
        searchable: true
        select: email

now you are able to search from email as well, hope this will work

if you get any error please comment.

Thank you, once again @Hardik Satasiya for a clear and concise answer and a solution to my problem! :-) — Joseph, Jan 25 '18 at 12:17

Eugene B. · Answer 2 · 2020-02-18T10:09:36.787

There is a problem with this solution, when you try to search, it runs query for count records and throws exception "column not found", because in count query it doesn't have select statement but only "select count ..." in my case it looks like - Column not found: 1054 Unknown column 'cruises.pagetitle' in 'where clause' (SQL: select count(*) as aggregate from cruises left join cr_cab as crc on crc.cab_id = cruises.id left join chartercruise as chrc on chrc.id = crc.cruise_id where ... or (lower(cruises.pagetitle) LIKE %thaty%))))" on line 664 of C:\xampp\htdocs\12knots_october\vendor\laravel\framework\src\Illuminate\Database\Connection.php

columns.yaml pagetitle: label: 'Connected Cruise' type: text default: none searchable: true sortable: true

    public function listExtendQueryBefore($query) {

    $query->addSelect('chrc.pagetitle');
    $query->leftJoin('cr_cab as crc', 'crc.cab_id', '=', 'cruises.id');
    $query->leftJoin('chartercruise as chrc', 'chrc.id', '=', 
                                                         'crc.cruise_id');

     }

Making a deep related field searchable in OctoberCMS

2 Answers2

Linked