Doctrine 1
  1. Doctrine 1
  2. DC-581

ORACLE: Missing fields on subquery with Many:Many

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 1.2.1
    • Fix Version/s: None
    • Component/s: Query
    • Labels:
      None

      Description

      I use this schema:

      ---
      Actuality:
        connection:         web
        tableName:          actuality
        actAs:
          softDelete:       ~
          Timestampable:    ~
          Sluggable:
            fields:         [name]
            indexName:      actuality_sluggable
            canUpdate:      true
        columns:
          id:
            type:           integer(4)
            primary:        true
            unsigned:       true
            sequence:       actuality
          created_at:
            type:           timestamp
          updated_at:
            type:           timestamp
          published_at:
            type:           timestamp
            notnull:        true
          unpublished_at:
            type:           timestamp
          name:
            type:           string(255)
            notnull:        true
          description_short:
            type:           string(1000)
            notnull:        true
          description:
            type:           clob
            notnull:        true
          is_professional:
            type:           boolean
            default:        true
        relations:
          Categories:
            class: ActualityCategory
            local: actuality_id
            foreign: actuality_category_id
            refClass: ActualityActualityCategory
            foreignAlias: Actualities
      
      
      ActualityActualityCategory:
        connection:         web
        tableName:          actuality_actuality_category
        options:
          symfony:
            form:   false
            filter: false
        columns:
          actuality_id:
            type:           integer(4)
            primary:        true
            unsigned:       true
          actuality_category_id:
            type:           integer(2)
            primary:        true
            unsigned:       true
        relations:
          Actuality:
            onDelete:       CASCADE
          
      
      ActualityCategory:
        connection:         web
        tableName:          actuality_category
        actAs:
          softDelete:       ~
          Timestampable:    ~
          Sluggable:
            fields:         [name]
            indexName:      actualityc_sluggable
            canUpdate:      true
        columns:
          id:
            type:           integer(2)
            primary:        true
            unsigned:       true
            sequence:       actualitycategory
          created_at:
            type:           timestamp
          updated_at:
            type:           timestamp
          name:
            type:           string(60)
            notnull:        true
      

      Function in my model Actuality

      public function retrieveActive($is_authenticated = false)
      {
        $q = $this->createQuery('a')
        ->leftJoin('a.Categories c')
        ->orderBy('a.published_at DESC');
        
        if (!$is_authenticated)
        {
          $q->where('a.is_professional = ?', false);
        }
        
        return $q;
      }
      

      with that, i receive the message "Doctrine_Connection_Oracle_Exception" because Oracle is strict and in the subselect, the field published_at is missing in select. This is the query generate by doctrine:

      SELECT a.id AS a__id, a.published_at AS a__published_at, a.name AS a__name, a.description_short AS a__description_short, a.slug AS a__slug, a2.id AS a2__id, a2.name AS a2__name FROM actuality a LEFT JOIN actuality_actuality_category a3 ON (a.id = a3.actuality_id) LEFT JOIN actuality_category a2 ON a2.id = a3.actuality_category_id WHERE a.id IN (SELECT a4.id FROM ( SELECT DISTINCT a4.id FROM actuality a4 LEFT JOIN actuality_actuality_category a6 ON (a4.id = a6.actuality_id) LEFT JOIN actuality_category a5 ON a5.id = a6.actuality_category_id WHERE a4.is_professional = 0 ORDER BY a4.published_at DESC ) a4 WHERE ROWNUM <= 5) AND (a.is_professional = 0) ORDER BY a.published_at DESC
      

      If i execute this query and add the field published_at in subquery, this is work.

      SELECT a.id AS a__id, a.published_at AS a__published_at, a.name AS a__name, a.description_short AS a__description_short, a.slug AS a__slug, a2.id AS a2__id, a2.name AS a2__name FROM actuality a LEFT JOIN actuality_actuality_category a3 ON (a.id = a3.actuality_id) LEFT JOIN actuality_category a2 ON a2.id = a3.actuality_category_id WHERE a.id IN (SELECT a4.id FROM ( SELECT DISTINCT a4.id, a4.published_at  FROM actuality a4 LEFT JOIN actuality_actuality_category a6 ON (a4.id = a6.actuality_id) LEFT JOIN actuality_category a5 ON a5.id = a6.actuality_category_id WHERE a4.is_professional = 0 ORDER BY a4.published_at DESC ) a4 WHERE ROWNUM <= 5) AND (a.is_professional = 0) ORDER BY a.published_at DESC
      

      Thanks for your help.

        Activity

        Hide
        Bertrand Zuchuat added a comment -

        I specify another thing. I use this with a pager.

        Show
        Bertrand Zuchuat added a comment - I specify another thing. I use this with a pager.
        Hide
        Bertrand Zuchuat added a comment -

        I create this testcase to find the bug but with that, the test is OK.

        I execute the same code with symfony/doctrine but the query isn't the same. Why ????

        Extract from symfony exception (Doctrine_Connection->execute)

        SELECT
        a.id AS a__id, a.published_at AS a__published_at, a.name AS a__name, a.description_short AS a__description_short,
        a.slug AS a__slug, a2.id AS a2__id, a2.name AS a2__name
        FROM actuality a
        LEFT JOIN actuality_actuality_category a3 ON (a.id = a3.actuality_id)
        LEFT JOIN actuality_category a2 ON a2.id = a3.actuality_category_id WHERE a.id
        IN (SELECT a4.id FROM ( SELECT DISTINCT a4.id FROM actuality a4 LEFT JOIN actuality_actuality_category a6 ON (a4.id = a6.actuality_id)
        LEFT JOIN actuality_category a5 ON a5.id = a6.actuality_category_id
        WHERE a4.is_professional = ? ORDER BY a4.published_at DESC ) a4 WHERE ROWNUM <= 5) AND (a.is_professional = ?) ORDER BY a.published_at DESC
        
        Show
        Bertrand Zuchuat added a comment - I create this testcase to find the bug but with that, the test is OK. I execute the same code with symfony/doctrine but the query isn't the same. Why ???? Extract from symfony exception (Doctrine_Connection->execute) SELECT a.id AS a__id, a.published_at AS a__published_at, a.name AS a__name, a.description_short AS a__description_short, a.slug AS a__slug, a2.id AS a2__id, a2.name AS a2__name FROM actuality a LEFT JOIN actuality_actuality_category a3 ON (a.id = a3.actuality_id) LEFT JOIN actuality_category a2 ON a2.id = a3.actuality_category_id WHERE a.id IN (SELECT a4.id FROM ( SELECT DISTINCT a4.id FROM actuality a4 LEFT JOIN actuality_actuality_category a6 ON (a4.id = a6.actuality_id) LEFT JOIN actuality_category a5 ON a5.id = a6.actuality_category_id WHERE a4.is_professional = ? ORDER BY a4.published_at DESC ) a4 WHERE ROWNUM <= 5) AND (a.is_professional = ?) ORDER BY a.published_at DESC

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            Bertrand Zuchuat
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated: