php mysql cakephp cakephp-3.0 cakephp-3.x

CakePHP 3 - use different table aliases when separating JOIN conditions

I'm rebuilding a vanilla PHP/MySQL application using CakePHP 3.5.13.

One of the queries in the original application needs to build a JOIN condition on the same table and does so by using separate table aliases. The query is as follows - and gives the correct results that we expect:

SELECT DISTINCT(s.id) FROM substances s
JOIN display_substances AS dsUses
ON (s.id = dsUses.substance_id AND dsUses.display_id = 128 AND  (dsUses.value LIKE '%dye%') ) 
JOIN display_substances AS displays
ON (s.id = displays.substance_id AND displays.display_id NOT IN (1,2,3,4,6,128) AND  (displays.value LIKE '%bpr%'))

The reason this is needed is because the query is doing a search for 2 separate items of user input - within the same table (display_substances) but against different display_substances .display_id fields. In terms of the query above it means:

Search for "%dye%" where display_id = 128
Search for "%bpr%" where display_id is not 1,2,3,4,6 or 128

In Cake I have written it like this in a Controller that handles the search functionality:

$query = $Substances->find()->select(['id' => 'Substances.id'])->distinct();

// Search for "dye"
$query = $query->matching('DisplaySubstances', function ($q) use ($uses_summary) {
return $q->where([
        'DisplaySubstances.value LIKE' => '%dye%', // "dye" is dynamic and comes from $uses_summary
        'DisplaySubstances.display_id' => 128
    ]);
});

// Search for "bpr"
$query = $query->matching('DisplaySubstances', function ($q) use ($regulatory_information) {
    return $q->where([
            'DisplaySubstances.value LIKE' => '%bpr%', // "bpr" is dynamic and comes from $regulatory_information
            'DisplaySubstances.display_id NOT IN' => [1,2,3,4,6,128]
        ]);
 });

This produces the wrong SQL, because when I debug $query->sql(); it gives a different JOIN condition:

INNER JOIN display_substances DisplaySubstances ON 
(
    DisplaySubstances.value like "%dye%" AND DisplaySubstances.display_id = 128 
    AND DisplaySubstances.value like "%bpr%" 
    AND DisplaySubstances.display_id not in (1,2,3,4,6,128) 
    AND Substances.id = (DisplaySubstances.substance_id)
)

I'm not sure how to rewrite this query such that it will treat each of the two search inputs as a JOIN condition, as per the original query.

The main thing I've noticed is that the original query has separate aliases for the same table (AS dsUses and AS displays). I'm not sure if this is relevant?

I'm trying to use the ORM as opposed to writing the SQL manually so would prefer to know how to use it to do this.

Solution

It's not yet possible to specify custom aliases for matching() calls, currently multiple matching() calls on the same association will merge the conditions as can be seen in your generated SQL snippet.

For now you'd have to either create additional associations (ie additionally to your DisplaySubstances association in your SubstancesTable class) with different aliases:

$this->hasMany('DsUses', [
    'className' => 'DisplaySubstances'
]);
$this->hasMany('Displays', [
    'className' => 'DisplaySubstances'
]);

on which you can then match:

$query->matching('DsUses', function ($q) use ($regulatory_information) {
    return $q->where([
        'DsUses.value LIKE' => '%dye%',
        'DsUses.display_id' => 128
    ]);
});

$query->matching('Displays', function ($q) use ($regulatory_information) {
    return $q->where([
        'Displays.value LIKE' => '%bpr%',
        'Displays.display_id NOT IN' => [1, 2, 3, 4, 6, 128]
    ]);
});

or build the joins manually, for example using innerJoin():

$query->innerJoin(
    [
        'DsUses' => 'display_substances'
    ],
    [
        'Substances.id' => new \Cake\Database\Expression\IdentifierExpression('DsUses.substance_id'),
        'DsUses.display_id' => 128,
        'DsUses.value LIKE' => '%dye%'
    ],
    [
        'DsUses.display_id' => 'integer',
        'DsUses.value' => 'string'
    ]
);

$query->innerJoin(
    [
        'Displays' => 'display_substances'
    ],
    [
        'Substances.id' => new \Cake\Database\Expression\IdentifierExpression('Displays.substance_id'),
        'Displays.display_id NOT IN' => [1, 2, 3, 4, 6, 128],
        'Displays.value LIKE' => '%bpr%'
    ],
    [
        'Displays.display_id' => 'integer',
        'Displays.value' => 'string'
    ]
);