Doctrine 1
  1. Doctrine 1
  2. DC-138

WHERE LIKE doesn't work on JOIN SELECTs.

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.2.0-ALPHA3
    • Fix Version/s: 1.2.0-RC1
    • Component/s: Query
    • Labels:
      None
    • Environment:
      SVN 1.2 branch

      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

        Activity

        Hide
        Jonathan H. Wage added a comment -

        Something else is wrong, this is not a bug in Doctrine. The params are not replaced by Doctrine, they are simply passed to PDO and PDO replaces the values internally. If you have more information, i.e. a failing test case you can re-open the issue.

        Show
        Jonathan H. Wage added a comment - Something else is wrong, this is not a bug in Doctrine. The params are not replaced by Doctrine, they are simply passed to PDO and PDO replaces the values internally. If you have more information, i.e. a failing test case you can re-open the issue.
        Hide
        Marcin Gil added a comment -

        More information:

        Last working revision was 6394 - after this the functionality is broken.

        Show
        Marcin Gil added a comment - More information: Last working revision was 6394 - after this the functionality is broken.
        Hide
        Marcin Gil added a comment -

        Last working revision was 6394 - after this the functionality is broken.

        Show
        Marcin Gil added a comment - Last working revision was 6394 - after this the functionality is broken.
        Hide
        Marcin Gil added a comment -

        I attach test case later.

        Show
        Marcin Gil added a comment - I attach test case later.
        Hide
        Marcin Gil added a comment -

        It seems this is the same problem as brought up here:
        http://www.doctrine-project.org/jira/browse/DC-241

        Show
        Marcin Gil added a comment - It seems this is the same problem as brought up here: http://www.doctrine-project.org/jira/browse/DC-241
        Hide
        Jonathan H. Wage added a comment -

        Are you sure that revision is right?

        http://trac.doctrine-project.org/changeset/6394
        http://trac.doctrine-project.org/changeset/6395

        Those two revisions are unrelated to the code that is in question here. Take a look.

        Show
        Jonathan H. Wage added a comment - Are you sure that revision is right? http://trac.doctrine-project.org/changeset/6394 http://trac.doctrine-project.org/changeset/6395 Those two revisions are unrelated to the code that is in question here. Take a look.
        Hide
        Jonathan H. Wage added a comment -

        Can you try the patch in the comments here?

        http://www.doctrine-project.org/jira/browse/DC-241

        Show
        Jonathan H. Wage added a comment - Can you try the patch in the comments here? http://www.doctrine-project.org/jira/browse/DC-241
        Hide
        Maciej Hołyszko added a comment - - edited

        Actually, the revision with changed a lot in Doctrine_Query was that one: http://trac.doctrine-project.org/changeset/6396
        But seems like that wasn't the one which causes the problem, sorry.
        I will investigate further.

        Show
        Maciej Hołyszko added a comment - - edited Actually, the revision with changed a lot in Doctrine_Query was that one: http://trac.doctrine-project.org/changeset/6396 But seems like that wasn't the one which causes the problem, sorry. I will investigate further.
        Hide
        Jonathan H. Wage added a comment -

        I think this should be fixed now

        Show
        Jonathan H. Wage added a comment - I think this should be fixed now

          People

          • Assignee:
            Jonathan H. Wage
            Reporter:
            Marcin Gil
          • Votes:
            1 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: