I have a simple doctrine code:
$dql = Doctrine_Query::create() ->select('u.ident, u.username, u.email') ->from('Users u, u.Attributes ua'); if ($query) { $dql->where('u.username LIKE ?', "%$query%") ->orWhere('u.name LIKE ?', "%$query%") ->orWhere('u.email LIKE ?', "%$query%"); } $dql->offset($start)->limit($count); $users = $dql->execute();
With the profiler, I found out that Doctrine is running this as two separate queries:
SELECT DISTINCT e3.ident FROM users e3 LEFT JOIN userpolicies e4 ON e3.ident = e4.user_id WHERE e3.username LIKE ? OR e3.name LIKE ? OR e3.email LIKE ? LIMIT 20 Params: Array ( [0] => %fam% [1] => %fam% [2] => %fam% )
and
SELECT e.ident AS e__ident, e.username AS e__username, e.email AS e__email FROM users e LEFT JOIN userpolicies e2 ON e.ident = e2.user_id WHERE e.ident IN ('2', '40', '42', '44', '52', '53', '54', '55', '56', '58', '60', '61', '62', '64', '65', '66', '68', '70', '74', '82') AND e.username LIKE ? OR e.name LIKE ? OR e.email LIKE ? Params: Array ( [0] => %fam% [1] => %fam% [2] => %fam% )
This is not exactly what I have in mind because the second query applies the WHERE conditions a second time even after the matching rows from 'Users' are selected in the first query (i.e., the second query should stop with WHERE e.ident IN ()
). Is this my screw-up or the expected behaviour of Doctrine? I'm using doctrine v1.1.6.