What is the correct syntax for CONCAT in modx xpdo statement

Question

I'm trying to deal with spaces in a search tool - specifically for dealing with first and last names, I can get my queries to work on the command line, but when I try to convert them to the modx/pdo syntax I'm not getting results. [because the criteria isn't translated correctly]

What I have is:

$criteria->where(array(
    'Entities.first_name:LIKE' => "%$searchQuery%",
    'OR:Entities.cams:LIKE' => "%$searchQuery%",
    'OR:Entities.last_name:LIKE' => "%$searchQuery%",
    'OR:Entities.company:LIKE' => "%$searchQuery%",
    'OR:CONCAT(`Entites`.`first_name`," ",`Entities`.`last_name`):LIKE' => "%$searchQuery%",
        ));

The problem is with the last OR:CONCAT line, if I dump the query my where clause looks like:

WHERE  ( 
`Entities`.`first_name` LIKE '%first last%' 
OR `Entities`.`cams` LIKE '%first last%' 
OR `Entities`.`last_name` LIKE '%first last%' 
OR `Entities`.`company` LIKE '%first last%' 
OR `CONCAT(`Entites`.`first_name`," ",`Entities` LIKE '%first last%' 
)

it's adding a backtick before CONCAT and omitting the .last_name)

What is the correct syntax?

Answer 1

Advanced conditions often need to be constructed as a string rather than a key => value pairing.

The following is not perfectly true to the original query but should give the same result:

$c->where(array(
    'Entities.first_name:LIKE' => "%$searchQuery%",
    'OR:Entities.cams:LIKE' => "%$searchQuery%",
    'OR:Entities.last_name:LIKE' => "%$searchQuery%",
    'OR:Entities.company:LIKE' => "%$searchQuery%",
));
$c->orCondition("CONCAT(Entities.first_name,' ',Entities.last_name) LIKE '%$searchQuery%'");

Or pass the whole set of OR conditions as a string to reproduce your query exactly:

$c->where("
    Entities.first_name LIKE '%$searchQuery%'
    OR Entities.cams LIKE '%$searchQuery%'
    OR Entities.last_name LIKE '%$searchQuery%'
    OR Entities.company LIKE '%$searchQuery%'
    OR CONCAT(Entities.first_name,' ',Entities.last_name) LIKE '%$searchQuery%'
");