make it easier to query multiple documents without a join

[lsmith77]

Building a query like the following:

SELECT * FROM [nt:unstructured] AS a
               WHERE (
                [phpcr:class] = 'AppBundle\Document\Article’ OR 
                [phpcr:class] = 'AppBundle\Document\Video’  OR
                [phpcr:class] = 'AppBundle\Document\Crew’
             )
                    AND 
            ( 
                a.tags LIKE '%tag1%’ OR
                a.tags LIKE '%tag2%’ OR
                a.tags LIKE '%tag3%’
            )

Maybe we could introduce a new method $from->documents() to be able to define that the [phpcr:class] filtering should be done on a set of documents?

wdyt @dantleech ?

[dbu]

i guess we did not have it because doctrine ORM thinks in database tables too much. with phpcr-odm it seems a valid use case to support this, and the resulting query is straightforward

[dantleech]

Well .. it makes sense when we are using a property to determine the Document class. But when or if we every get around to allowing the PHPCR node type to be used for that purpose then the analogy with the ORM is stronger - would it still make sense?

[dbu]

could we select from a bunch of different node types in that case or is that not supported in jcr sql2?

[dantleech]

You would need to do joins, but I think there is a deeper problem.

i.e. above @lsmith77 is selecting from a which is [nt:unstructured] - i..e we are operating on the PHPCR node itself, not the Document.

So how would that look with a query builder?

$qb->from('a')->documents([ 'Article', 'Post' ])->where()->field('a.tags')

So to start with we have already said the a is our primary document.

$qb->from(['a', 'b'])->documents([ 'Article', 'Post' ]);
$qb->where()->field('a.tags')->contains('foo');
$qb->orWhere()->field('b.tags')->contains('foo');
$qb->orWhere()->field('a.tags')->contains('bar');
$qb->orWhere()->field('b.tags')->contains('bar');

So we could make from() accept an array, but then we need to reference the document fields individually -- as the ODM has no concept of [nt:unstructured].

[dbu]

good point dan. i think if the documents in the original example all share a common ancestor, it would work like this:

$qb->from('a')->documents('AppBundle\Document\TaggableContent');
$qb->where()->field('a.tags')->contains('foo');

as the query will not only look for documents of class TaggableContent but also for documents that inherit from TaggableContent.

[dantleech]

Indeed, and using an abstract mapped class as the document source should
already work, but it does mean forcing inheritance which, especially in
the case of tags, is less than ideal.

This is where mixin's come to the rescue in PHPCR (at least
theoretically), as you can use a mixin as a source. But implementing
that functionality in the ODM (and maybe Jackalope too) is another matter.

[dbu]

yeah, ideally we would map traits as mixins and allow to query for traits too.

[mssimi]

Hey, any news on this feature?

[dbu]

not that i would be aware of. if you want to work on it, please let us know.