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

        Quintenvk created issue -
        Quintenvk made changes -
        Field Original Value New Value
        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 id. 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.
        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.
        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.
        Quintenvk made changes -
        Attachment Testcase.zip [ 11524 ]
        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!

        This list may be incomplete, as errors occurred whilst retrieving source from linked applications:

        • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DDC-2401, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

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

            Dates

            • Created:
              Updated: