[DDC-2401] INDEX BY not working on multiple columns Created: 16/Apr/13  Updated: 18/Apr/13

Status: Open
Project: Doctrine 2 - ORM
Component/s: Documentation, ORM
Affects Version/s: 2.3.3
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Quintenvk Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None

Attachments: Zip Archive Testcase.zip    

 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.



 Comments   
Comment by Fabio B. Silva [ 18/Apr/13 ]

Hi Quintenvk

Could you please try to write a failing test case ?

Thanks

Comment by Quintenvk [ 18/Apr/13 ]

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.

Comment by Quintenvk [ 18/Apr/13 ]

Fabio,

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

Thanks,
Quinten

Comment by Fabio B. Silva [ 18/Apr/13 ]

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
Comment by Quintenvk [ 18/Apr/13 ]

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

Thanks,
Quinten

Comment by Fabio B. Silva [ 18/Apr/13 ]

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();
Comment by Quintenvk [ 18/Apr/13 ]

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!

Generated at Sat Dec 20 18:49:00 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.