How to use CakePHP ORM to query belongsToMany relationship

Question

I am working on a custom query builder piece of code that supports a CakePHP 3 based API. I recently got a request to make it search against a Task item for associated Users.

The association from a Task to users is a belongsToMany, which has not been supported in the custom query functionality up to this point. The relationship looks like this in the TasksTable.php:

<?php

$this->belongsToMany('Users', [
    'foreignKey' => 'task_id',
    'targetForeignKey' => 'user_id',
    'through' => 'TasksUsers'
]);

The query builder uses Cake\Database\Expression\QueryExpression to execute queries, since it has to handle more complex queries than I am able to handle with Cake\Database\Query. However I am now trying to nest a QueryExpression to handle querying Users on this belongsToMany relationship.

The code is pretty abstracted. For the sake of simplicity, I simplified my implementation of Search\Model\Filter\Base on the TasksTable to the code below. This demonstrates the issue I am dealing with precisely when I run it.

<?php

public function process(): bool
{
    $this->getQuery()->where(function (QueryExpression $exp, Query $query) {
        return $query->newExpr()->eq('Users.id', 621048);
    })->contains('Users');
}

The above gives me:

"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Users' in 'where clause'"

I am wondering how do I modify the return on the nested function in the where clause and the contents of the contains to accomplish a join on the belongToMany relationship against Users?

On a side note, I am aware that the example query can be performed using the matching() method against the Query, as demonstrated here. However this does not work for my solution, due to the fact that everything in the custom search has to be checked using QueryExpressions to be compatible with the rest of the query builder.

Thanks in advance for your input!

What does using matching() (you'd better use innerJoinWith() though to avoid the select list being manipulated) have to do with using query expressions? Nothing forces you to use expressions for a simple equality comparison with a column. — ndm
Thanks I did not know that about matching(). The custom query builder this is being used in dynamically generates queries loaded from configurations in other parts of the software. It allows the deep nesting for and / or operations. I am not aware of a way to do that without an expression. I am just wondering what the proper way is to join a belongsToMany using the association. I would be happy to reword the question as needed. — space97
Regular array style comparisons will be converted to expressions when they are being added to the query, so in the end you often end up with a similar expression tree. And you can use expressions in the join query builder just like in your example snippet too. That being said, I don't see why you'd need any special actions to use matching() or *joinWith() here, just call it on your query. — ndm
Regardless of the rest, surely you want to be comparing 'Users.id', not 'Users'? — Greg Schmidt

space97 space97 · Accepted Answer · 2021-03-19T21:45:08

As helpfully noted in the comments, the solution to my question was to add a leftJoinsWith() to the query, as opposed to the contains():

<?php

public function process(): bool
{
    $this->getQuery()->where(function (QueryExpression $exp, Query $query) {
        return $query->newExpr()->eq('Users.id', 621048);
    })->leftJoinWith('Users');
}

QueryExpressions are for checking conditions and not joining data.

More on using leftJoinWith() can be found here.

How to use CakePHP ORM to query belongsToMany relationship

1 Answers