Details
-
Type:
Bug
-
Status:
Open
-
Priority:
Major
-
Resolution: Unresolved
-
Affects Version/s: 1.2.1
-
Fix Version/s: None
-
Component/s: Query
-
Labels:None
-
Environment:php 5.3.0, mysql 5.1.36, symfony 1.4.3
Description
1)Code
Doctrine_Query::create ()
->select ( "u.user_id,mr.message_id,mr.message_senddate,mr.message_title,mr.message_content,mr.message_alias,mr.message_categoryid," )
->from ( "user u" )
->leftJoin ( "u.messages mr ON u.user_id = mr.message_sender" )
->where ( "u.user_id = ?", 2 )
->limit(1)
->fetchArray ();
This code must return only one result but it returns all results in which user_id is 2 in the database
If i looked it in Symfony Debug bar, i saw that;
2)Symfony debug toolbar values:
#Query A
SELECT DISTINCT u2.user_id FROM users u2 LEFT JOIN messages m2 ON (u2.user_id = m2.message_sender) WHERE u2.user_id = 2 LIMIT 1
#Query B
SELECT u.user_id AS u_user_id, m.message_id AS mmessage_id, m.message_senddate AS mmessage_senddate, m.message_title AS mmessage_title, m.message_content AS mmessage_content, m.message_alias AS mmessage_alias, m.message_categoryid AS m_message_categoryid FROM users u LEFT JOIN messages m ON (u.user_id = m.message_sender) WHERE u.user_id IN ('2') AND (u.user_id = 2)
And if i test this code in MYSQL query browser i got a result like that;
SELECT u.user_id AS u_user_id, m.message_id AS mmessage_id, m.message_senddate AS mmessage_senddate, m.message_title AS mmessage_title, m.message_content AS mmessage_content, m.message_alias AS mmessage_alias, m.message_categoryid AS m_message_categoryid
FROM users u
LEFT JOIN messages m ON ( u.user_id = m.message_sender )
WHERE u.user_id
IN (
'2'
)
AND (
u.user_id =2
)
LIMIT 0 , 30
If i changed SQL code like below and put the limit at end, the problem solved:
#Query A
SELECT DISTINCT u2.user_id FROM users u2 LEFT JOIN messages m2 ON (u2.user_id = m2.message_sender) WHERE u2.user_id = 2
#Query B
SELECT u.user_id AS u_user_id, m.message_id AS mmessage_id, m.message_senddate AS mmessage_senddate, m.message_title AS mmessage_title, m.message_content AS mmessage_content, m.message_alias AS mmessage_alias, m.message_categoryid AS m_message_categoryid FROM users u LEFT JOIN messages m ON (u.user_id = m.message_sender) WHERE u.user_id IN ('2') AND (u.user_id = 2) LIMIT 1
But doctrine's limit(1) metod is Select Distinct
The query which starts with ... is at query A but it needs to be at the end of query B
My schema file.