[FIXED] How to query associations using an alias in CakePHP?

Issue

I have the following query in CakePHP 3 which is being used to do a search against a Task entity:

<?php

    public function search($q = "")
    { 
        $tasks = TableRegistry::getTableLocator()->get('Tasks')->find()
            ->where(function (QueryExpression $exp, Query $query) use ($q) {
                return $exp->or_(function (QueryExpression $or) use ($query, $q) {
                    $or->like('Tasks.name', '%' . $q . '%');
                    $or->like('Asset.name', '%' . $q . '%');
                    $or->like('AssignedToUsers.first_name', '%' . $q . '%');
                    $or->like('AssignedToUsers.last_name', '%' . $q . '%');
                    $or->like($query->func()->concat([
                        'AssignedToUsers.first_name' => 'identifier',
                        "' '" => 'literal',
                        'AssignedToUsers.last_name' => 'identifier'
                    ]), '%' . $q . '%');
                    return $or;
                });
            })
            ->contain(["Asset", "Asset.AssignedToUsers", "AssignedToUsers"])
            ->limit(5)
            ->order(["Tasks.modified" => "DESC"]);
    }

The Task table is associated with an Asset and AssignedToUsers table as follows:

<?php

public function initialize(array $config)
{
    $this->belongsTo('Asset', [
        'className' => 'Assets',
        'foreignKey' => 'asset_id',
        'joinType' => 'LEFT'
    ]);
    $this->belongsTo('AssignedToUsers', [
        'className' => 'Users',
        'foreignKey' => 'assigned_to_user_id',
        'joinType' => 'LEFT'
    ]);
}

And then the Asset table has an AssignedToUsers association as well:

<?php

public function initialize(array $config)
{
    $this->belongsTo('AssignedToUsers', [
        'className' => 'Users',
        'foreignKey' => 'assigned_to_user_id',
        'joinType' => 'LEFT'
    ]);
}

The current query does a concatenated search of the AssignedToUsers frist and last name. However I need to make it include searching the Asset.AssignedToUsers first and last name as well.

The query will search the Asset.AssignedToUsers first and last name fields in the concat if I remove the AssignedToUsers associations from the contain in the search, so I am assuming that cake is not able to tell the difference between AssignedToUsers and Asset.AssignedToUsers.

Is there a way that I can alias both the AssignedToUsers associations in the contain() call so I can query the version AssignedToUsers that is associated with both the Task and Asset individually? Or is there another way to go about performing this query?

Thanks in advance!

Solution

You cannot use the same alias multiple times in a single query, that's first and foremost an SQL limitation, and lastly not supported by CakePHP. Changing aliases on the fly also isn't supported.

For containing the data you could try a different strategy, specifically the select strategy, which will retrieve the associated data in a separate query instead of joining it in. For filtering you could then use a custom join with a custom alias, something along the lines of this:

->where(function (QueryExpression $exp, Query $query) use ($q) {
    return $exp->or_(function (QueryExpression $or) use ($query, $q) {
        // ...
        $or->like(
            $query->func()->concat([
                'AssetAssignedToUsers.first_name' => 'identifier',
                "' '" => 'literal',
                'AssetAssignedToUsers.last_name' => 'identifier'
            ]),
            '%' . $q . '%'
        );
        
        return $or;
    });
})
->join([
    'table' => 'users',
    'alias' => 'AssetAssignedToUsers',
    'type' => 'LEFT',
    'conditions' => 'AssetAssignedToUsers.id = Asset.assigned_to_user_id',
])
->contain([
    'Asset',
    'Asset.AssignedToUsers' => [
        'strategy' => \Cake\ORM\Association::STRATEGY_SELECT,
    ],
    'AssignedToUsers',
])

Another option would be to rename one, or even all of the associations, for example prefix them with the parents name, like TasksAssignedToUsers, AssetAssignedToUsers, etc.

See also

• Cookbook > Database Access & ORM > Retrieving Data & Results Sets > Changing Fetching Strategies
• Cookbook > Database Access & ORM > Retrieving Data & Results Sets > Passing Conditions to Contain
• Cookbook > Database Access & ORM > Query Builder > Adding Joins

Answered By - ndm