ReferenceMany only matches First

[swimson]

Heyo, we ran into a strange bug, and are trying to figure out how to work around it.

We have two Documents - Tombstone, and TombstoneCategory.
Each Tombstone can be in multiple categories.

Here's the QueryBuilder code....

    if ($categories) {
        $qb
            ->addJoinInner()
            ->right()->document('Mindgruve\Cms\OpusBundle\Doctrine\PHPCR\TombstoneCategory', 'tc')->end()
            ->condition()->equi('t.categories', 'tc.uuid')->end()->end();

        $node = $qb->andWhere()->orX();
        foreach ($categories as $category) {
            $node->eq()->localName('tc')->literal($category)->end();
        }
        $qb->end();
    }

The generated SQL statement is ....

SELECT n0.path AS n0_path, n0.identifier AS n0_identifier, n0.props AS n0_props, n1.path AS n1_path, n1.identifier AS n1_identifier, n1.props AS n1_props 
FROM phpcr_nodes n0 
INNER JOIN phpcr_nodes n1 ON ( n0.workspace_name = n1.workspace_name AND n1.type IN ('nt:unstructured', 'rep:root') AND EXTRACTVALUE(n0.props, '//sv:property[@sv:name="categories"]/sv:value[1]') = n1.identifier ) 
WHERE n0.workspace_name = 'default' AND n0.type IN ('nt:unstructured', 'rep:root') AND ((n1.local_name = 'commercial-real-estate-banking' AND (EXTRACTVALUE(n0.props, 'count(//sv:property[@sv:name="phpcr:class"]/sv:value[text()="Mindgruve\\Cms\\OpusBundle\\Doctrine\\PHPCR\\Tombstone"]) > 0') OR EXTRACTVALUE(n0.props, 'count(//sv:property[@sv:name="phpcr:classparents"]/sv:value[text()="Mindgruve\\Cms\\OpusBundle\\Doctrine\\PHPCR\\Tombstone"]) > 0'))) AND (EXTRACTVALUE(n1.props, 'count(//sv:property[@sv:name="phpcr:class"]/sv:value[text()="Mindgruve\\Cms\\OpusBundle\\Doctrine\\PHPCR\\TombstoneCategory"]) > 0') OR EXTRACTVALUE(n1.props, 'count(//sv:property[@sv:name="phpcr:classparents"]/sv:value[text()="Mindgruve\\Cms\\OpusBundle\\Doctrine\\PHPCR\\TombstoneCategory"]) > 0')))

The problem is that in the inner join there is this line...

'//sv:property[@sv:name="categories"]/sv:value[1]') = n1.identifier

The problem is that the tombstone is in two categories, and the first one isn't the one we are searching for. SQL statement always matches the first element even when the property is multivalued.....

<sv:property sv:name="categories" sv:type="WeakReference" sv:multi-valued="1">
   <sv:value length="36">ced3ab5d-bb56-4817-885b-2cef09d7d272</sv:value>
   <sv:value length="36">bfdf6816-a2dd-416e-9adb-795b68dd824d</sv:value>
</sv:property>

Is there a better way to query multivalued properties multivalued properties?

Thanks!

[dbu]

so what you are looking for is the inverse relation, right? the query builder query looks sensible to me, so i would consider this a bug indeed.

the queries are generated in the QOMWalker class of jackalope-doctrine-dbal, i think in the method sqlXpathValueExists. can you play around with the generated sql query and see if you find a way that works? if there is a way to make that working we hopefully can fix the QOMWalker to generate the right query. (well, just putting [2] instead of [1| is obviously not a general fix ;-) ).