Doctrine 1
  1. Doctrine 1
  2. DC-485

Limit gets lost when doing self join

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 1.2.1
    • Fix Version/s: None
    • Component/s: Behaviors
    • Labels:
      None
    • Environment:
      os x, snow leopard,

      Description

      hi doctrine team,
      today I encountered a very weird problem when I tried to do a selfjoin on a table. the goal was to list all people that have the same addresses but limit the result to 5

      the problem was, that the doctrine split it into 2 querys and first selected the correct address ids with the Limit 5. but in the second query the limit is lost. which gives me more than 5 results if a few people have the same address

      the DQL looks like this

      $q = DQ::create()
      ->from('address a INNER JOIN a.shared b ON a.city = b.city AND a.street = b.street')
      ->innerJoin('b.entity e')
      ->where('a.entity_id = ? AND a.is_active = ?', array($id, $is_active))
      ->limit(5)
      ->offset(0);

      SQL looks like this:

      SELECT * FROM address a
      INNER JOIN address a2 ON ((a.city = a2.city AND a.street = a2.street))
      INNER JOIN entity e ON a2.entity_id = e.id
      WHERE a.id IN ('5689677') AND (a.entity_id = ? AND a.is_active = ?)

      Problem: Limit 5 is gone

      expected was something like this:

      SELECT *
      FROM address a
      INNER JOIN address b ON a.city = b.city AND a.street = b.street
      INNER JOIN entity e ON b.entity_id = e.id
      where a.entity_id = 104294414
      limit 5

      my temporary solution:

      $q = new Doctrine_RawSql();
      $q->select('

      {b.*}

      ,

      {e.*}

      ,

      {p.*}

      ');
      $q->from('address a INNER JOIN address b ON a.city = b.city AND a.street = b.street
      INNER JOIN entity e ON b.entity_id = e.id
      INNER JOIN person p ON e.id = p.entity_id');
      $q->where('a.entity_id = ? AND a.is_active = ?', array($id, $isActive));
      $q->addComponent('a', 'address a');
      $q->addComponent('b', 'a.shared b');
      $q->addComponent('e', 'b.entity e');
      $q->addComponent('p', 'e.person p');
      $q->limit($limit);
      $q->offset($offset);

      my schema.yml is attached, do you need anything else?

      1. schema.yml
        3 kB
        Pascal Helfenstein

        Activity

        There are no comments yet on this issue.

          People

          • Assignee:
            Jonathan H. Wage
            Reporter:
            Pascal Helfenstein
          • Votes:
            1 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated: