Doctrine 1
  1. Doctrine 1
  2. DC-807

Equal nest relation uses incorrect SQL and returns incorrect data

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major 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:

      { class: Profile, refClass: UserFriend, local: requestor_user_id, foreign: receiver_user_id, equal: true }

      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)

      { ["requestor_user_id"]=> string(1) "1" ["receiver_user_id"]=> string(1) "2" ["Receiver"]=> bool(false) }

      [1]=>
      array(3)

      { ["requestor_user_id"]=> string(1) "2" ["receiver_user_id"]=> string(1) "2" ["Receiver"]=> bool(false) }

      }
      }
      [1]=>
      array(3) {
      ["id"]=>
      string(1) "4"
      ["email"]=>
      string(16) "four@example.org"
      ["UserFriend"]=>
      array(1) {
      [0]=>
      array(3)

      { ["requestor_user_id"]=> string(1) "4" ["receiver_user_id"]=> string(1) "4" ["Receiver"]=> bool(false) }

      }
      }
      }

      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.

        Activity

        Hide
        Denis Chmel added a comment -

        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.

        Show
        Denis Chmel added a comment - 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.

          People

          • Assignee:
            Roman S. Borschel
            Reporter:
            Denis Chmel
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated: