Details

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

      Description

      Users & Books are linked by CompoUsersBooks. So, It's a m:m relationship.

      Here, a crazy DQL query to fetch users & specifics books.

      [code]
      $query = Doctrine_Query::create ()
      ->select ("users., books.")
      ->from ("Users users")
      ->leftJoin ("users.alternateBooks alternateBooks")
      ->leftJoin ("users.books books ON books.id_book = alternateBooks.id_book");
      [/code]

      The SQL query
      [code]
      SELECT `s`.`id_show` AS `s__id_show`
      FROM `users` `u`
      LEFT JOIN `compo_users_books` `c1`
      ON ( `u`.`id_user` = `c2`.`id_user` )
      LEFT JOIN `books` `b1`
      ON `c1`.`id_book` = `b1`.`id_book`
      LEFT JOIN `compo_users_books` `c2`
      ON `u`.`id_user` = `c2`.`id_user`
      LEFT JOIN `books` `b2`
      ON `b1`.`id_book` = `b2`.`id_book`
      [/code]

      As you can see,
      The SQL query is correctly build.
      But, this SQL Query isn't optimized at all.
      Because the following part of code isn't use :
      [code]
      LEFT JOIN `compo_users_books` `c2`
      ON `u`.`id_user` = `c2`.`id_user`
      [/code]

      And also, the left join is a cartesian product ! The number of row return is really big for nothing...

      That really sux because It's performance killer.

        Activity

        There are no comments yet on this issue.

          People

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

            Dates

            • Created:
              Updated: