Details
-
Type:
Bug
-
Status:
Open
-
Priority:
Major
-
Resolution: Unresolved
-
Affects Version/s: 1.2.2
-
Fix Version/s: None
-
Component/s: Nested Set
-
Labels:None
-
Environment:Debian
Description
The equal nest relation works incorrectly twice. The SQL it produces is wrong. And even if correct the SQL it still returns wrong data.
In my example below $profile->getFriends() returns incorrect data and even tries to write these wrong data when I do $profile->setEmail("..."); $profile->save()
I'm certain that SQL query in Nest.php is wrong, but not only SQL. Even after fixing SQL the data is still incorrect. I digged through the code and suspect that the problem is in Doctrine_Collection which seems to works with only one referenceField, while in an "equal relationship" this field varies row to row.
Here goes steps to reproduce.
Schema.yml
==========
Profile:
columns:
id:
type: integer
primary: true
autoincrement: true
email:
type: varchar(255)
notnull: true
relations:
Friends:
Fixtures:
======
Profile:
-
id: 1
email: one@example.org
-
id: 2
email: two@example.org
-
id: 3
email: three@example.org
-
id: 4
email: four@example.org
UserFriend:
-
requestor_user_id: 1
receiver_user_id: 2
-
requestor_user_id: 4
receiver_user_id: 1
-
requestor_user_id: 2
receiver_user_id: 3
PHP test
=======
$user = Doctrine_Core::getTable('Profile')->find(1);
var_dump($user->getFriends()->toArray());
Output
======
array(2) {
[0]=>
array(3) {
["id"]=>
string(1) "2"
["email"]=>
string(15) "two@example.org"
["UserFriend"]=>
array(2) {
[0]=>
array(3)
[1]=>
array(3)
}
}
[1]=>
array(3) {
["id"]=>
string(1) "4"
["email"]=>
string(16) "four@example.org"
["UserFriend"]=>
array(1) {
[0]=>
array(3)
}
}
}
As it can be seen from output, the relation "UserFriend" shows insane data:
- sometime there are two records in "UserFriend" - and that's wrong
- user 4 has friendship with 4 - that's also wrong, not in the fixtures.
If this will be useful. here's the SQL it produces:
SELECT
profile.id AS profile__id,
profile.email AS profile__email,
user_friend.requestor_user_id AS user_friend__requestor_user_id,
user_friend.receiver_user_id AS user_friend__receiver_user_id
FROM profile
INNER JOIN user_friend ON profile.id = user_friend.receiver_user_id OR profile.id = user_friend.requestor_user_id
WHERE
profile.id IN(SELECT receiver_user_id FROM user_friend WHERE requestor_user_id = 1)
OR profile.id IN (SELECT requestor_user_id FROM user_friend WHERE receiver_user_id = 1)
ORDER BY profile.id ASC
It's very clear that an OR in the inner join and another OR in where are not connected, while they must be. Here's the correct part (in my opinion)
...
WHERE
profile.id IN(SELECT receiver_user_id FROM user_friend WHERE requestor_user_id = 1) AND user_friend.receiver_user_id=1
OR profile.id IN (SELECT requestor_user_id FROM user_friend WHERE receiver_user_id = 1) AND user_friend.receiver_user_id=1
...
But this only fixes the problem with 2 records in "UserFriends", but not the second ("user 4 has friendship with 4 - that's also wrong, not in the fixtures.").
That another issue is somewhere inside Collection and how it works with the referenceField.