#1579: WhereIn, Limit and M2M Relationship Query Building Issue
-----------------------------------+----------------------------------------
Reporter: Jay.Klehr | Owner: romanb
Type: defect | Status: new
Priority: major | Milestone: Unknown
Component: Query/Hydration | Version: 1.0.3
Keywords: | Has_test: 0
Mystatus: Pending Core Response | Has_patch: 0
-----------------------------------+----------------------------------------
I have a many to many relationship, say Users and Countries.
Models:
{{{
class User extends Doctrine_Record
{
public function setTableDefinition()
{
$this->setTableName('users');
$this->hasColumn('name', 'string', 255);
}
public function setUp()
{
// Relationships
$this->hasMany('Country as Countries', array('local' => 'user_id',
'foreign' => 'country_id', 'refClass' => 'UserCountry'));
}
}
class Country extends Doctrine_Record
{
public function setTableDefinition()
{
$this->setTableName('countries');
$this->hasColumn('name', 'string', 255);
$this->hasColumn('code', 'string', 2);
}
public function setUp()
{
$this->hasMany('User as Users', array('local' => 'country_id',
'foreign' => 'user_id', 'refClass' => 'UserCountry'));
}
}
class UserCountry extends Doctrine_Record
{
public function setTableDefinition()
{
$this->setTableName('users_countries');
$this->hasColumn('user_id', 'integer', 11);
$this->hasColumn('country_id', 'integer', 11);
}
}
}}}
And then I want to find only the users that belong to several countries
that I want to specify by the country's code, and I only want 25 of them:
{{{
Doctrine_Query::create()
->from('User u')
->whereIn('u.Countries.code', array('US','CA'))
->limit(25)
->offset(0)
->execute();
}}}
Unfortunately, when I run this query, Doctrine throws an Exception because
the SQL it tries to run isn't built properly:
{{{
SELECT DISTINCT u4.id FROM users u4 LEFT JOIN users_countries u6 ON u4.id
= u6.user_id LEFT JOIN countries u5 ON u5.id = u6.country_id WHERE u5.code
IN (?, ?) LIMIT 25
}}}
Note that there was no parameter replacement done here (that's why mysql
is complaining). Also note that this isn't the final query. Doctrine
believes that a subquery is necessary in this case, and what you see above
is the execution of the subquery with the limit. Somewhere the parameters
are getting lost.
If I specify the same IN condition using just the ->where() method and
writing out the IN as part of the where's condition, the query executes
just fine (and also performs the subquery).
{{{
Doctrine_Query::create()
->from('User u')
->where("u.Countries.code IN ('US','CA')")
->limit(25)
->offset(0)
->execute();
}}}
SQL generated:
{{{
SELECT DISTINCT u4.id FROM users u4 LEFT JOIN users_countries u6 ON u4.id
= u6.user_id LEFT JOIN countries u5 ON u5.id = u6.country_id WHERE u5.code
IN ('US', 'CA') LIMIT 25
SELECT u.id AS u__id, u.name AS u__name FROM users u LEFT JOIN
users_countries u3 ON u.id = u3.user_id LEFT JOIN countries u2 ON u2.id =
u3.country_id WHERE u.id IN ('2018', '2021') AND u2.code IN ('US', 'CA')
}}}
I'm running PHP 5.2.5 and Doctrine 1.0 branch revision 5104 (just about
1.0.3 I believe).
--
Ticket URL: <http://trac.doctrine-project.org/ticket/1579>
Doctrine <http://www.phpdoctrine.org>
PHP Doctrine Object Relational Mapper
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"doctrine-svn" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.co.uk/group/doctrine-svn?hl=en-GB
-~----------~----~----~----~------~----~------~--~---
