Skip to content

Loading…

DDC-2401: INDEX BY not working on multiple columns #3114

Open
doctrinebot opened this Issue · 7 comments

2 participants

@doctrinebot

Jira issue originally created by user quintenvk:

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.

@doctrinebot

Comment created by @FabioBatSilva:

Hi Quintenvk

Could you please try to write a failing test case ?

Thanks

@doctrinebot

Comment created by quintenvk:

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.

@doctrinebot

Comment created by quintenvk:

Fabio,

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

Thanks,
Quinten

@doctrinebot

Comment created by @FabioBatSilva:

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

@doctrinebot

Comment created by quintenvk:

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

Thanks,
Quinten

@doctrinebot

Comment created by @FabioBatSilva:

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();
@doctrinebot

Comment created by quintenvk:

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!

@beberlei beberlei was assigned by doctrinebot
@doctrinebot doctrinebot added the Bug label
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.