How to query doctrine odm class' multivalue field?

[lowki]

My document class, contains multivalue fields, how can I query them with the query builder? I've tried like() and fullTextSearch() methods, but both failed.
I found that the generated query specifies the first index for the value array.

EXTRACTVALUE(n0.props, '//sv:property[@sv:name="style"]/sv:value[1]') = 2)

[dantleech]

->eq()->field('foo')->literal('bar') should do it?

[lowki]

This is exactly what i do.

[dantleech]

Can you check which version of jackalope doctrine-dbal you are using?

$ composer show --installed | grep jackalope

The following JCR-SQL2 query works for me locally:

PHPCRSH> SELECT * FROM [nt:unstructured] WHERE multivalue_field = 'any of the values will match this';

[lowki]

I'm using jackalope 1.0.1 and jackalope-doctrine-dbal 1.0.0
Should I update?

[dantleech]

I think your issue is probably fixed by this PR: jackalope/jackalope-doctrine-dbal#243

I think this is only available in the recent 1.2 release.

[lowki]

Ok, juste updated to jackalope 1.2 and problem still there.

Here the querybuilder code :

$qb->andWhere()->orX()->like()->field("p.style")->literal(2);

And the MySQL query generated :

    SELECT n0.path AS n0_path, n0.identifier AS n0_identifier, n0.props AS n0_props FROM phpcr_nodes n0 WHERE n0.workspace_name = 'default' AND n0.type IN ('nt:unstructured', 'rep:root') AND ((0 != FIND_IN_SET("2", REPLACE(EXTRACTVALUE(n0.props, '//sv:property[@sv:name="type"]/sv:value'), " ", ",")) AND CAST(EXTRACTVALUE(n0.props, '//sv:property[@sv:name="style"]/sv:value[1]') AS DECIMAL) LIKE 2) AND (EXTRACTVALUE(n0.props, 'count(//sv:property[@sv:name="phpcr:class"]/sv:value[text()="Detours\\MainBundle\\Document\\Product"]) > 0') OR EXTRACTVALUE(n0.props, 'count(//sv:property[@sv:name="phpcr:classparents"]/sv:value[text()="Detours\\MainBundle\\Document\\Product"]) > 0'))) ORDER BY CAST(EXTRACTVALUE(n0.numerical_props, '//sv:property[@sv:name="phpcr_locale:en-villaPackage"]/sv:value[1]') AS DECIMAL) ASC, EXTRACTVALUE(n0.props, '//sv:property[@sv:name="phpcr_locale:en-villaPackage"]/sv:value[1]') ASC

[dbu]

ping @dantleech

[dantleech]

@lowki could you try the query in the PHPCR shell, this works for me using jackalope:

PHPCRSH> ls
/cmf/stanton/contents/foo [nt:unstructured] > nt:base
+-----------------+--------------+-----------------+
| jcr:primaryType | NAME (15)    | nt:unstructured |
| numbers         | LONG (1,1,1) | [0] 5           |
|                 |              | [1] 3           |
|                 |              | [2] 1           |
+-----------------+--------------+-----------------+
1 nodes in set (0.001381 sec)
PHPCRSH > select * from [nt:unstructured] where numbers = 3;
// returns the correct result

[dbu]

ping @lowki

[lowki]

Thank you @dantleech, I didn't know there is such a tool, I will give it a try.

[dantleech]

fyi, it would probably be best to install it as a dependency:

http://phpcr.readthedocs.org/en/latest/phpcr-shell/installation.html#install-as-an-embedded-application

if you are using Symfony then you can probably start it with ./app/console doctrine:phpcr:shell

Otherwise you can start it manually, e.g.

$ ./vendor/bin/phpcrsh --transport=doctrine-dbal --db-name=... --db-password=... --db-driver=pdo_mysql

[dantleech]

fyi, it would probably be best to install it as a dependency, as this means that you will be using the same lobrary versions as your application:

http://phpcr.readthedocs.org/en/latest/phpcr-shell/installation.html#install-as-an-embedded-application

if you are using Symfony then you can probably start it with ./app/console doctrine:phpcr:shell

Otherwise you can start it manually, e.g.

$ ./vendor/bin/phpcrsh --transport=doctrine-dbal --db-name=... --db-password=... --db-driver=pdo_mysql

[dantleech]

Reopened as I don't think this has been resolved.

[dbu]

@dantleech can you update the description or open a new issue that explains what needs to be fixed? i understood that a newer version of jackalope solved the problem

[dantleech]

Well, I can't reproduce it. @lowki did you have any luck with installing PHPCR shell as a dep? Did my query work for you? Does your query work as you expected?

[dbu]

ping @lowki