[FIXED] How to use CakePHP ORM to query belongsToMany relationship

Issue

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!

Solution

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.

Answered By - space97