[FIXED] I want to do "or where" and "concat" with the query builder of cakephp4

Issue

At first , I apologize for my poor English.

environmental information
language: PHP 7.4.13
framework: CakePHP 4.2.6

At the SQL DB table, there are 6 colmuns.
(columnA , columnB , columnC , columnD , columnE , columnF)

I want to search from the concatnated columns.
I want to "OR search" with 4 conditions.

Whether '${columnA}.${columnC}' matches $str
or
Whether '${columnB}.${columnC}' matches $str
or
Whether '${columnD}.${columnF}' matches $str
or
Whether '${columnE}.${columnF}' matches $str

Now , I write as this to search with one condition .

$query->where(function ($exp, $q) use($str) {
    $concat = $q->func()->concat([
        'columnA' => 'identifier', 
        'columnC' => 'identifier'
    ]);
    return $exp->like($concat, '%' . $str . '%' );
});

If I search from "not concatnated columns" , I write this .

$query->where([
    0 => [
        'or' => [
            '${columnA} LIKE' => '%' . $str . '%' ,
            '${columnB} LIKE' => '%' . $str . '%' ,
            '${columnC} LIKE' => '%' . $str . '%' ,
            '${columnD} LIKE' => '%' . $str . '%' ,
        ]
    ],

]);

Finally , I want to do something like this.(Ofcourse , this throws a syntax error )

$query->where([
    0 => [
        'or' => [
            '${columnA}.${columnC} LIKE' => '%' . $str . '%' ,
            '${columnB}.${columnC} LIKE' => '%' . $str . '%' ,
            '${columnD}.${columnF} LIKE' => '%' . $str . '%' ,
            '${columnE}.${columnF} LIKE' => '%' . $str . '%' ,
        ]
    ],

]);

Can someone help me?

Solution

The query expression object has an or() method that you can use, it either takes an array of conditions that are combined using OR, or a callable that receives a new query expression object that uses the OR operator.

In any case you need to be aware that QueryExpression::like() will not return a new expression object, but it will add a new like expression to the current query expression object's stack instead.

That being said, the callback variant would look something like this:

$query->where(function (
    \Cake\Database\Expression\QueryExpression $exp,
    \Cake\ORM\Query $q
) use (
    $str
) {
    return $exp->or(function ($exp) use ($q, $str) {
        $concatAC = $q->func()->concat([
            'columnA' => 'identifier',
            'columnC' => 'identifier'
        ]);
        $concatBC = $q->func()->concat([
            'columnB' => 'identifier', 
            'columnC' => 'identifier'
        ]);
        
        return $exp
            ->like($concatAC, '%' . $str . '%')
            ->like($concatBC, '%' . $str . '%')
            // ...
    });
});

The former variant can be used by passing an empty array, that way you receive an empty query expression object that you can then fill with your conditions:

$query->where(function (
    \Cake\Database\Expression\QueryExpression $exp,
    \Cake\ORM\Query $q
) use (
    $str
) {
    // ...

    return $exp
        ->or([])
        ->like($concatAC, '%' . $str . '%')
        ->like($concatBC, '%' . $str . '%')
        // ...
});

Lastly you can also at any point change the string used for concatenating the expressions by any query expression object using QueryExpression::setConjunction():

$query->where(function (
    \Cake\Database\Expression\QueryExpression $exp,
    \Cake\ORM\Query $q
) use (
    $str
) {
    // ...

    return $exp
        ->setConjunction('OR')
        ->like($concatAC, '%' . $str . '%')
        ->like($concatBC, '%' . $str . '%')
        // ...
});

All three variants will result in the same SQL, that is:

WHERE (
    CONCAT(columnA, columnC) LIKE %...% OR
    CONCAT(columnB, columnC) LIKE %...% OR
    ...
)

See also

• Cookbook > Database Access & ORM > Query Builder > Advanced Conditions

Answered By - ndm