Answered How to write complex xPDO Query criteria?

$criteria = $this->modx->newQuery('Tipa');

$criteria->leftJoin('Entities','Entities');

$criteria->leftJoin('Program','Program');

$criteria->leftJoin('Location','Location', 'Program.location_id = Location.id');

$criteria->where(array('Tipa.id' => $id));

$criteria->where(array('Tipa.status_id' => 2,'OR:Tipa.status_id:=' => 3));

$criteria->where(array(
	'Entities.first_name:LIKE' => "%$searchQuery%",
	'OR:Entities.cams:LIKE' => "%$searchQuery%",
	'OR:Entities.last_name:LIKE' => "%$searchQuery%",
	'OR:CONCAT(`Entities`.`first_name`, " ", `Entities`.`last_name`):LIKE' => 'CONCAT("%", replace("'.$searchQuery.'", " ", "%"), "%")',
	));
	
$criteria->where(array('Location.id:IN' => $mygroups));

$options = $this->modx->getCollectionGraph('Tipa', '{ "Entities": {}, "Program": { "ProgramName": {}, "Location": {}, }, }', $criteria);

'OR:CONCAT(`Entities`.`first_name`, " ", `Entities`.`last_name`):LIKE' => 'CONCAT("%", replace("'.$searchQuery.'", " ", "%"), "%")',

SELECT `Tipa`.`id` AS `Tipa_id`, `Tipa`.`created` AS `Tipa_created`, `Tipa`.`modified` AS `Tipa_modified`, `Tipa`.`token` AS `Tipa_token`, `Tipa`.`unixtime` AS `Tipa_unixtime`, `Tipa`.`status_id` AS `Tipa_status_id`, `Tipa`.`client_id` AS `Tipa_client_id`, `Tipa`.`payee_id` AS `Tipa_payee_id`, `Tipa`.`program_id` AS `Tipa_program_id`, `Tipa`.`requester_uid` AS `Tipa_requester_uid`, `Tipa`.`approver_uid` AS `Tipa_approver_uid`, `Tipa`.`start_date` AS `Tipa_start_date`, `Tipa`.`end_date` AS `Tipa_end_date`, `Tipa`.`comment` AS `Tipa_comment` FROM `flow_tipa` AS `Tipa` LEFT JOIN `flow_entities` `Entities` ON `Tipa`.`client_id` =  `Entities`.`id` LEFT JOIN `flow_program` `Program` ON `Tipa`.`program_id` =  `Program`.`id` LEFT JOIN `flow_location` `Location` ON Program.location_id = Location.id

 WHERE  (  ( `Tipa`.`status_id` = ? OR `Tipa`.`status_id` = ? )  
AND  ( `Entities`.`first_name` LIKE ? OR `Entities`.`cams` LIKE ? OR `Entities`.`last_name` LIKE ? OR `CONCAT(`Entities`.`first_name`, " ", `Entities` LIKE ? )  
AND `Location`.`id` IN (1,2,3,5,6,7,8,10,11,12,13,15,17,18,19) ) 

$q = '
    `Entities`.`first_name` LIKE "%' . $searchQuery . '%"
    OR `Entities`.`cams` LIKE "%' . $searchQuery . '%"
    OR `Entities`.`last_name` LIKE "%' . $searchQuery . '%"
    OR CONCAT(`Entities`.`first_name`, " ", `Entities`.`last_name`) LIKE CONCAT("%", replace("' . $searchQuery . '", " ", "%"), "%")';

$criteria->where($q, xPDOQuery::SQL_AND);

$q = 'YOUR SQL HERE WITH :PDOPARAM';

$query = new xPDOCriteria($modx, $q);
$query->prepare();
$query->stmt->bindParam(':PDOPARAM', $whatever, PDO::PARAM_STR);
$query->stmt->execute();

//run the query to get our results
return $query->stmt->fetchAll(PDO::FETCH_ASSOC);

$q = "SELECT fullname, email, extended FROM modx_user_attributes WHERE extended like '%:code%'";
$query = new xPDOCriteria($modx, $q);
$query->prepare();
$query->stmt->bindParam(':code', $code, PDO::PARAM_STR);
$query->stmt->execute();

//run the query to get our results
$r = $query->stmt->fetchAll(PDO::FETCH_ASSOC);
print_r($r);