CODEWITHSUNDEEP
phpmysqlcakephpsearchcakephp-3.x
eve
eve

Reputation: 247

How to filter associated HABTM data with FriendsOfCake Search for CakePHP 3

Plugin: FriendsOfCake/Search

CakePHP: 3.1.4

I'm using the plugin to filter my index.ctp view data with a form. This similar question: How to Filter on Associated Data is about a belongsTo association. My question is specifically about associated HABTM data where my associated table is linked through a joinTable and not directly. The normal setup in the Model like the following is not working in this case: 

->value('painting', [
    field' => $this->Paintings->target()->aliasField('id')
)]

My tables are set up like:

Here is the main setup:

class TicketsTable extends Table
{
    public function initialize(array $config)
    {
       ...

       $this->belongsToMany('Paintings', [
        'foreignKey' => 'ticket_id',
        'targetForeignKey' => 'painting_id',
        'joinTable' => 'tickets_paintings'
       ]);
    }

   public function searchConfiguration()
   {
        $search = new Manager($this);

        $search->value('status', [
                'field' => $this->aliasField('active'),
            ])->like('member_name', [
               'field' => $this->Members->target()->aliasField('surname'),
               'filterEmpty' => true
            ])->value('painting', [
              'field' => $this->Paintings->target()->aliasField('id'),  // not working
            ]);

        return $search;

    }

class TicketsController extends AppController
{
    public function index()
    {
    $query = $this->Tickets
        ->find('search', 
            $this->Tickets->filterParams($this->request->query))
        ->contain(['Members', 'Paintings', 'Appointments']);

    ...
    }

Everything else is working and the parameters are added to the URL when I filter etc., so I only put in the parts where sth has to be wrong.

After filtering I get an error: 

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Paintings.id' in 'where clause'

The contain works properly when just displaying data from the Paintings table in the Tickets view.

But in the code from the SQL query I can see, that all contained tables (Members, Appoinments) are joined for the query except the Paintings table, so obviously it can not find the column...And I guess it can't really join it directly anyway since they are only connected through the joinTable.

I'm new to CakePHP and I can't really figure out what I'm doing wrong here, so hopefully someone can help me out a bit.

Do I have to use a different syntax in the plugin settings? Do I have to set up my Tables differently? Or how exactly can I tell the query to incorporate the habtm related table in the search?

Thanks!

Upvotes: 1

Views: 1887

Answers (1)

ndm
ndm

Reputation: 60473

The available search methods rely on the field being available in the main query (hasMany and belongsToMany associations are being being retrieved in separate queries).

While you could join it in manually in the controller, using a callback- or a finder-filter is probably the better approach, that way you can modify the query in the model layer, and you could easily utilize Query::matching() to filter by associated data.

Here's an (untested) example that should give you a hint:

use Cake\ORM\Query;
use Search\Type\Callback; // This changed in master recently
                          // now it's Search\Model\Filter\Callback

// ...

public function searchConfiguration()
{
    $search = new Manager($this);

    $search
        // ...
        ->callback('painting', [
            'callback' => function (Query $query, array $args, Callback $type) {
                return $query
                    ->distinct($this->aliasField('id'))
                    ->matching('Paintings', function (Query $query) use ($args) {
                        return $query
                            ->where([
                                $this->Paintings->target()->aliasField('id') => $args['painting']
                            ]);
                    });
            }
        ]);

    return $search;

}

See also

Upvotes: 2

Related Questions