ChristopheBrun ChristopheBrun - 29 days ago 10
PHP Question

How can I use CONCAT in JOIN conditions with CakePHP?

I'm working on a CakePHP 3.0 backend where I have the following tables : 'prescriptions' and 'profils' (names are french).

'prescriptions' has a foreign key directed to 'profils' : prescriptions.profils_id.

'profils' has two fields that can be searched : 'nom' and 'prenom'

The relationship has been declared like this :

class PrescriptionsTable extends Table
{
public function initialize(array $config)
{
$this->table('prescriptions');
$this->belongsTo('Profils', [
'foreignKey' => 'profils_id',
]);
}
}

class ProfilsTable extends Table
{
public function initialize(array $config)
{
$this->table('profils');
$this->hasMany('Prescriptions', [
'foreignKey' => 'profils_id',
]);
}
}


The PrescriptionsController has a search action where the user can set various filters. The ones I am working on allows him to search a Prescription after a substring of the name of the person (Profil) owning that Prescription.

Thus, 'al' could match 'Albert Dupont' or 'Marc Vidal' but not 'Eugène Brun'. Likewise, 'ert dup' should match 'Albert Dupont' (as 'ont Al' should', but that's another story).

The search() method has the following lines for the query :

$q1 = $this->Prescriptions->find()->where(['Prescriptions.users_id = ' . $userId]);

$q1->matching('Profils', function ($q) use($filter) {
return $q->where(["CONCAT(Profils.prenom, ' ', Profils.nom) like" => "%$filter%"]);
});
[...]
return $q1->select(['id'])->toArray();


Unfortunately when I run the query, CakePHP throws me the following error :


Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column
'profils.nom' in 'on clause'


I wanted to check the query object so I tried :

$q1->matching('Profils', function ($q) use($filter) {
return $q->where(["CONCAT(Profils.prenom, ' ') like" => "%$filter%"]);


which is obvously not a very interesting CONCAT, but it works. The error only rises when I use two fields : the second one does not use the alias Profils but the table name 'profils' instead.

I've been stuck on this error for several hours now and I'll appreciate any insights on what I am doing wrong here.

ndm ndm
Answer

The used syntax isn't ment to support that

The syntax that you are using is ment to support constructs like

TableAlias.column SQL_EXPRESSION

The compiler will lowercase the expression part, ie everything found after a possible TableAlias.column identifier, so in your case CONCAT(Profils.prenom, will be treated as the identifier, and the rest that is being seen as separated via whitespace, ' ', Profils.nom) like, is being treated as the expression (neither is being checked for validity), hence being lowercased, and that's where it happens, the finaly query will look for profils.nom, which doesn't exist, as the alias is Profils.

Use expressions

You should use proper expressions, they are portable, combinable, and support value binding.

CakePHP ships with support for various SQL functions out the box, including CONCAT. It also supports various comparison expressions that can be combined with other expressions, ie you can simply pass a concat function expression to a like comparison expression and you should be good:

return $q
    ->where(function (\Cake\Database\Expression\QueryExpression $exp, \Cake\ORM\Query $query) {
        return $exp
            ->like(
                $query->func()->concat([
                    'Profils.prenom' => 'identifier',
                    ' ',
                    'Profils.nom' => 'identifier'
                ]),
                "%$filter%",
                'string'
            );
    });

See also