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.

Answer 1

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.