Details
Description
WHERE LIKE doesn't work on JOIN SELECTs.
Params are not passed to the query.
Reproduce code:
public function addSearchTerm(Doctrine_Query $query, array $fields) { $phrase = $this->getRequest()->getPost('query'); if(!empty($phrase) && !empty($fields)) { $phrase = '%'. $phrase .'%'; $searchString = array(); $searchParams = array(); foreach($fields As $field) { $searchString[] = "$field LIKE ?"; $searchParams[] = $phrase; } $searchString = implode(' OR ', $searchString); $query->andWhere($searchString, $searchParams); } } $this->addSearchTerm($query, array( 'username', 'email', 'last_login_ip', 'Data.title', 'Data.first_name', 'Data.last_name', 'Data.company', 'Roles.name' ));
Result attached as screenshot.
Query produced is correct, however '?' are not replaced with params from the array.
Error message is:
Doctrine_Connection_Mysql_Exception: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? OR `c8`.`email` LIKE ? OR `c8`.`last_login_ip` LIKE ? OR `c9`.`title` LIKE ? O' at line 1
Query passed to MySQL by Doctrine:
SELECT DISTINCT `c8`.`id` FROM `core_users` `c8` LEFT JOIN `core_users_data` `c9` ON `c8`.`id` = `c9`.`id_user` LEFT JOIN `core_users_roles` `c11` ON (`c8`.`id` = `c11`.`id_user`) LEFT JOIN `core_roles` `c10` ON `c10`.`id` = `c11`.`id_role` LEFT JOIN `core_users_roles` `c13` ON (`c8`.`id` = `c13`.`id_user`) LEFT JOIN `core_roles` `c12` ON `c12`.`id` = `c13`.`id_role` LEFT JOIN `core_users_roles` `c14` ON `c12`.`id` = `c14`.`id_role` WHERE (`c8`.`username` LIKE ? OR `c8`.`email` LIKE ? OR `c8`.`last_login_ip` LIKE ? OR `c9`.`title` LIKE ? OR `c9`.`first_name` LIKE ? OR `c9`.`last_name` LIKE ? OR `c9`.`company` LIKE ? OR `c10`.`name` LIKE ?) ORDER BY `c8`.`username` ASC, `c14`.`position` LIMIT 20