Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-2401

INDEX BY not working on multiple columns

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 2.3.3
    • Fix Version/s: None
    • Component/s: Documentation, ORM
    • Security Level: All
    • Labels:
      None

      Description

      According to the docs on this page:
      http://docs.doctrine-project.org/en/latest/reference/dql-doctrine-query-language.html#using-index-by

      The following "multi-dimensional index" should be perfectly possible, with a default hydration mode:
      SELECT b as business, p as product FROM Businesses b INDEX BY b.id JOIN Products p WITH b.id = p.businessid INDEX BY p.id

      However, b.id is completely ignored (it is a numeric primary key).

      I tried to go further, giving 2 products a matching barcode and indexing by barcode and then a (unique, numeric) productid. Only the barcode worked as a key and only one of the products with a matching barcode was selected. I used this query to test:
      SELECT p FROM Products p INDEX BY p.barcode JOIN p.businessid b INDEX BY p.id

      I also flagged the docs, because I don't think a userid should/could be starting from 0.

        Activity

        Hide
        Fabio B. Silva added a comment -

        Hi Quintenvk

        Could you please try to write a failing test case ?

        Thanks

        Show
        Fabio B. Silva added a comment - Hi Quintenvk Could you please try to write a failing test case ? Thanks
        Hide
        Quintenvk added a comment -

        I added a testcase. Please note that the database settings are to be configured in Core/simplys/simplys.php, and that the dump is in dummy.sql.

        Apart from that all should run well immediately.

        Show
        Quintenvk added a comment - I added a testcase. Please note that the database settings are to be configured in Core/simplys/simplys.php, and that the dump is in dummy.sql. Apart from that all should run well immediately.
        Hide
        Quintenvk added a comment -

        Fabio,

        Please check the zip I just attached. I hope this helps you in finding the problem.

        Thanks,
        Quinten

        Show
        Quintenvk added a comment - Fabio, Please check the zip I just attached. I hope this helps you in finding the problem. Thanks, Quinten
        Hide
        Fabio B. Silva added a comment -

        Thanks Quintenvk,

        SELECT p.barcode, p.id, p.name FROM \core\Simplys\Entity\Products p INDEX BY p.barcode JOIN p.businessid b INDEX BY p.id

        In this DQL you are trying to index by scalar values,
        I think we does not support that, and a single dimensional array is the expected result in this case.

        Also the INDEX BY documentations seems wrong to me.

        The given DQL :

         SELECT u.id, u.status, upper(u.name) nameUpper FROM User u INDEX BY u.idJOIN u.phonenumbers p INDEX BY p.phonenumber 

        Show the following result :

        array
          0 =>
            array
              1 =>
                object(stdClass)[299]
                  public '__CLASS__' => string 'Doctrine\Tests\Models\CMS\CmsUser' (length=33)
                  public 'id' => int 1
                  ..
              'nameUpper' => string 'ROMANB' (length=6)
          1 =>
            array
              2 =>
                object(stdClass)[298]
                  public '__CLASS__' => string 'Doctrine\Tests\Models\CMS\CmsUser' (length=33)
                  public 'id' => int 2
                  ...
              'nameUpper' => string 'JWAGE' (length=5)
        

        Which IMHO represents another DQL, something like :

         SELECT u, p , upper(u.name) nameUpper FROM User u INDEX BY u.id JOIN u.phonenumbers p INDEX BY p.phonenumber
        Show
        Fabio B. Silva added a comment - Thanks Quintenvk, SELECT p.barcode, p.id, p.name FROM \core\Simplys\Entity\Products p INDEX BY p.barcode JOIN p.businessid b INDEX BY p.id In this DQL you are trying to index by scalar values, I think we does not support that, and a single dimensional array is the expected result in this case. Also the INDEX BY documentations seems wrong to me. The given DQL : SELECT u.id, u.status, upper(u.name) nameUpper FROM User u INDEX BY u.idJOIN u.phonenumbers p INDEX BY p.phonenumber Show the following result : array 0 => array 1 => object(stdClass)[299] public '__CLASS__' => string 'Doctrine\Tests\Models\CMS\CmsUser' (length=33) public 'id' => int 1 .. 'nameUpper' => string 'ROMANB' (length=6) 1 => array 2 => object(stdClass)[298] public '__CLASS__' => string 'Doctrine\Tests\Models\CMS\CmsUser' (length=33) public 'id' => int 2 ... 'nameUpper' => string 'JWAGE' (length=5) Which IMHO represents another DQL, something like : SELECT u, p , upper(u.name) nameUpper FROM User u INDEX BY u.id JOIN u.phonenumbers p INDEX BY p.phonenumber
        Hide
        Quintenvk added a comment -

        Thanks for your reply Fabio.
        Do you think there could be alternatives (apart from a foreach-loop) to achieve the expected result?

        Thanks,
        Quinten

        Show
        Quintenvk added a comment - Thanks for your reply Fabio. Do you think there could be alternatives (apart from a foreach-loop) to achieve the expected result? Thanks, Quinten
        Hide
        Fabio B. Silva added a comment - - edited

        Not sure if it's exactly the result you need but you can try

        Something like :

        SELECT p, b FROM \core\Simplys\Entity\Products p INDEX BY p.barcode JOIN p.businessid b INDEX BY p.id

        or something like :

        SELECT PARTIAL p.{id, barcode, name}, b.{id, attributesYouNeed} FROM \core\Simplys\Entity\Products p INDEX BY p.barcode JOIN p.businessid b INDEX BY p.id

        And than :

        $result = $query->getArrayResult();
        
        Show
        Fabio B. Silva added a comment - - edited Not sure if it's exactly the result you need but you can try Something like : SELECT p, b FROM \core\Simplys\Entity\Products p INDEX BY p.barcode JOIN p.businessid b INDEX BY p.id or something like : SELECT PARTIAL p.{id, barcode, name}, b.{id, attributesYouNeed} FROM \core\Simplys\Entity\Products p INDEX BY p.barcode JOIN p.businessid b INDEX BY p.id And than : $result = $query->getArrayResult();
        Hide
        Quintenvk added a comment -

        Both produce the same result as the query I had. I think i'll move on to loops after a bit more research, too bad it can't be done (at least for now) though... Would've been nice.

        Thanks for your help though!

        Show
        Quintenvk added a comment - Both produce the same result as the query I had. I think i'll move on to loops after a bit more research, too bad it can't be done (at least for now) though... Would've been nice. Thanks for your help though!

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Quintenvk
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated: