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

Question

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?

ndm · Accepted Answer · 2015-10-27T22:06:48.217

2

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().

edited Oct 27 '15 at 22:06

answered Oct 27 '15 at 21:11

ndm

59,784
9
71
110

Using the code above worked great. I do have 2 questions. 1. When I first modified my code, I left the ->target() out of the line and it worked as well, what purpose is the target() method providing? 2. You had mentioned not using aliasField() when providing the alias, how would I code that? – hulk66049 Oct 27 '15 at 21:49
@hulk66049 1.) Using `target()` is more specific. It works without too, because internally the association class (the `Users` property does _not_ hold a table class instance!) will delegate unknown method calls to `target()` [**using a magic `__call()` handler**](https://github.com/cakephp/cakephp/blob/3.1.3/src/ORM/Association.php#L894). 2.) See my updated answer. – ndm Oct 27 '15 at 22:08

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

1 Answers1

Linked