[FIXED] How to Filter Associated Data using FriendsOfCake Search plugin for CakePHP 3.1.0

Issue

Plugin: FriendsOfCake\Search

CakePHP: 3.1.0

I'm currently adding the ability to filter my Orders controller on the index() method. I'm needing to be able to search Orders my the name of the User who placed the order. Each order is associated with the Users model:

    $this->belongsTo('Users', [
        'foreignKey' => 'user_id',
        'joinType' => 'INNER'
    ]);

When I build my searchConfiguration() method in the OrdersTable.php file I have the following:

    ->value('first_name', [
        'field' => $this->aliasField('Users.first_name')
    ])
    ->value('last_name', [
        'field' => $this->aliasField('Users.last_name')
    ])

The Orders index() method

    $query = $this->Orders->find('search', 
        $this->Orders->filterParams($this->request->query))->contain(['Users', 'PaymentMethods', 'Industries']
    )->order(['Orders.created' => 'DESC']);
    $this->set('orders', $this->paginate($query));

This loads fine when I'm not passing any parameters to the query, however, as soon as I try and search by first_name or last_name I get the error:

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Orders.Users.first_name' in 'where clause'

Based on the error, it's appending Orders. to the field that I'm trying to search by. From what I can tell CakePHP has 2 methods aliasField() one in \var\www\<project_name>\vendors\cakephp\cakephp\src\ORM\Query.php

public function aliasField($field, $alias = null)
{
    $namespaced = strpos($field, '.') !== false;
    $aliasedField = $field;

    if ($namespaced) {
        list($alias, $field) = explode('.', $field);
    }

    if (!$alias) {
        $alias = $this->repository()->alias();
    }

    $key = sprintf('%s__%s', $alias, $field);
    if (!$namespaced) {
        $aliasedField = $alias . '.' . $field;
    }

    return [$key => $aliasedField];
}

And one in \var\www\<project_name>\vendors\cakephp\cakephp\src\ORM\Table.php

public function aliasField($field)
{
    return $this->alias() . '.' . $field;
}

It appears that Query.php will allow you to specify the $alias for the field, however, Table.php does not, so I assume that's the method that's being used here.

Can anyone give me guidance on how to filter on data contained in an associated table?

Solution

Simply either do not use aliasField() when you're already supplying the alias

'field' => 'Users.first_name'

or use aliasField() on the associated Users table

'field' => $this->Users->target()->aliasField('first_name')

Using Query::aliasField() would be totally wrong, as this will return a key => value array for use with Query::select().

Answered By - ndm