Doctrine 1
  1. Doctrine 1
  2. DC-627

Work on link-table and leftJoin

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Blocker Blocker
    • Resolution: Unresolved
    • Affects Version/s: 1.2.2
    • Fix Version/s: None
    • Component/s: Query
    • Labels:
      None
    • Environment:
      Windows Seven - WAMP 2.0i

      Description

      Looking at the attachment to see the little schema.
      Tree table : items, items_children and children.

      The link-table items_children is here to create a many-to-many relationships between Items and Children.

      So, after configure the YAML and generated Models.
      We can do something like that :

      $query = Doctrine_Query::create ()
      ->select ("items.title, children.title")
      ->from ("Item items")
      ->leftJoin ("item.children children");
      $items = $query->execute ();

      $items //An array of items
      $items[0]->title //A string
      $items[0]->children //An array of children

      The SQL Output of the query is :

      SELECT `i`.`id_item` AS `i_id_item`, `i`.`title` AS `ititle`, `c`.`id_child` AS `cid_child`, `c`.`title` AS `c_title` FROM `items` `i` LEFT JOIN `items_children` `i2` ON (`i`.`id_item` = `i2`.`id_item`) LEFT JOIN `children` `c` ON `i2`.`id_child` = `c`.`id_child`

      All array are order by something, in this case it's a natural order which come from the items_children order into database.

      Now, I want to apply a condition, or a filter on the items_children table.
      If I translate my demand to SQL Query :

      SELECT `i`.`id_item` AS `i_id_item`, `i`.`title` AS `ititle`, `c`.`id_child` AS `cid_child`, `c`.`title` AS `c_title` FROM `items` `i` LEFT JOIN `items_children` `i2` ON (`i`.`id_item` = `i2`.`id_item`) LEFT JOIN `children` `c` ON `i2`.`id_child` = `c`.`id_child` ORDER BY `c1`.`index`

      Or something crazy :

      SELECT `i`.`id_item` AS `i_id_item`, `i`.`title` AS `ititle`, `c`.`id_child` AS `cid_child`, `c`.`title` AS `c_title` FROM `items` `i` LEFT JOIN `items_children` `i2` ON (`i`.`id_item` = `i2`.`id_item`) LEFT JOIN `children` `c` ON `i2`.`id_child` = `c`.`id_child` WHERE `c1`.`index` > 2

      But how can I do that with DQL ? Because I have no access to the Link-table.

      Link-Table are use to create many-to-many, that could be OOP array. So we have to be allowed to add "index" to order the result. Also, we have to be allowed to add condition on this tables, like "datePromote" or simply "enabled".

      The solution should be on the following DQL Query :

      $query = Doctrine_Query::create ()
      ->select ("items.title, children.title")
      ->from ("Item items")
      ->leftJoin ("item.children children")
      ->leftJoin ("ItemsChildren link_table")
      ->orderBy ("link_table.index")
      $items = $query->execute ();

      But the SQL Output is :

      SELECT `i`.`id_item` AS `i_id_item`, `i`.`title` AS `ititle`, `c`.`id_child` AS `cid_child`, `c`.`title` AS `c_title` FROM `items` `i` LEFT JOIN `items_children` `i2` ON (`i`.`id_item` = `i2`.`id_item`) LEFT JOIN `children` `c` ON `i2`.`id_child` = `c`.`id_child`, `items_children` `i3` ORDER BY `i3`.`index`

      To conclude, it's a blocker problem. Because I can't use a standard DB schema.

      Regards,
      Armetiz.

        Activity

        Thomas Tourlourat - Armetiz created issue -
        Thomas Tourlourat - Armetiz made changes -
        Field Original Value New Value
        Description Looking at the attachment to see the little schema.
        Tree table : items, items_children and children.

        The link-table items_children is here to create a many-to-many relationships between Items and Children.

        So, after configure the YAML and generated Models.
        We can do something like that :
        {quote}
        $query = Doctrine_Query::create ()
             ->select ("items.title, children.title")
             ->from ("Item items")
             ->leftJoin ("item.children children");
        $items = $query->execute ();

        $items //An array of items
        $items[0]->title //A string
        $items[0]->children //An array of children
        {quote}

        The SQL Output of the query is :
        {quote}
        SELECT `i`.`id_item` AS `i__id_item`, `i`.`title` AS `i__title`, `c`.`id_child` AS `c__id_child`, `c`.`title` AS `c__title` FROM `items` `i` LEFT JOIN `items_children` `i2` ON (`i`.`id_item` = `i2`.`id_item`) LEFT JOIN `children` `c` ON `i2`.`id_child` = `c`.`id_child`
        {quote}

        All array are order by something, in this case it's a natural order which come from the items_children order into database.

        Now, I want to apply a condition, or a filter on the items_children table.
        If I translate my demand to SQL Query :
        {quote}
        SELECT `i`.`id_item` AS `i__id_item`, `i`.`title` AS `i__title`, `c`.`id_child` AS `c__id_child`, `c`.`title` AS `c__title` FROM `items` `i` LEFT JOIN `items_children` `i2` ON (`i`.`id_item` = `i2`.`id_item`) LEFT JOIN `children` `c` ON `i2`.`id_child` = `c`.`id_child` ORDER BY `c1`.`index`
        {quote}

        Or something crazy :
        {quote}
        SELECT `i`.`id_item` AS `i__id_item`, `i`.`title` AS `i__title`, `c`.`id_child` AS `c__id_child`, `c`.`title` AS `c__title` FROM `items` `i` LEFT JOIN `items_children` `i2` ON (`i`.`id_item` = `i2`.`id_item`) LEFT JOIN `children` `c` ON `i2`.`id_child` = `c`.`id_child` WHERE `c1`.`index` > 2
        {quote}

        But how can I do that with DQL ? Because I have no access to the Link-table.

        Link-Table are use to create many-to-many, that could be OOP array. So we have to be allowed to add "index" to order the result. Also, we have to be allowed to add condition on this tables, like "datePromote" or simply "enabled".

        How can I solve my problem ?

        Thanks.
        Looking at the attachment to see the little schema.
        Tree table : items, items_children and children.

        The link-table items_children is here to create a many-to-many relationships between Items and Children.

        So, after configure the YAML and generated Models.
        We can do something like that :
        {quote}
        $query = Doctrine_Query::create ()
             ->select ("items.title, children.title")
             ->from ("Item items")
             ->leftJoin ("item.children children");
        $items = $query->execute ();

        $items //An array of items
        $items[0]->title //A string
        $items[0]->children //An array of children
        {quote}

        The SQL Output of the query is :
        {quote}
        SELECT `i`.`id_item` AS `i__id_item`, `i`.`title` AS `i__title`, `c`.`id_child` AS `c__id_child`, `c`.`title` AS `c__title` FROM `items` `i` LEFT JOIN `items_children` `i2` ON (`i`.`id_item` = `i2`.`id_item`) LEFT JOIN `children` `c` ON `i2`.`id_child` = `c`.`id_child`
        {quote}

        All array are order by something, in this case it's a natural order which come from the items_children order into database.

        Now, I want to apply a condition, or a filter on the items_children table.
        If I translate my demand to SQL Query :
        {quote}
        SELECT `i`.`id_item` AS `i__id_item`, `i`.`title` AS `i__title`, `c`.`id_child` AS `c__id_child`, `c`.`title` AS `c__title` FROM `items` `i` LEFT JOIN `items_children` `i2` ON (`i`.`id_item` = `i2`.`id_item`) LEFT JOIN `children` `c` ON `i2`.`id_child` = `c`.`id_child` ORDER BY `c1`.`index`
        {quote}

        Or something crazy :
        {quote}
        SELECT `i`.`id_item` AS `i__id_item`, `i`.`title` AS `i__title`, `c`.`id_child` AS `c__id_child`, `c`.`title` AS `c__title` FROM `items` `i` LEFT JOIN `items_children` `i2` ON (`i`.`id_item` = `i2`.`id_item`) LEFT JOIN `children` `c` ON `i2`.`id_child` = `c`.`id_child` WHERE `c1`.`index` > 2
        {quote}

        But how can I do that with DQL ? Because I have no access to the Link-table.

        Link-Table are use to create many-to-many, that could be OOP array. So we have to be allowed to add "index" to order the result. Also, we have to be allowed to add condition on this tables, like "datePromote" or simply "enabled".

        The solution should be on the following DQL Query :
        {quote}
        $query = Doctrine_Query::create ()
             ->select ("items.title, children.title")
             ->from ("Item items")
             ->leftJoin ("item.children children")
             ->leftJoin ("ItemsChildren link_table")
             ->orderBy ("link_table.index")
        $items = $query->execute ();
        {quote}

        But the SQL Output is :
        {quote}
        SELECT `i`.`id_item` AS `i__id_item`, `i`.`title` AS `i__title`, `c`.`id_child` AS `c__id_child`, `c`.`title` AS `c__title` FROM `items` `i` LEFT JOIN `items_children` `i2` ON (`i`.`id_item` = `i2`.`id_item`) LEFT JOIN `children` `c` ON `i2`.`id_child` = `c`.`id_child`, `items_children` `i3` ORDER BY `i3`.`index`
        {quote}

        To conclude, it's a blocker problem. Because I can't use a standard DB schema.

        Regards,
        Armetiz.

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            Thomas Tourlourat - Armetiz
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated: