7 Replies Latest reply on Mar 11, 2015 4:59 PM by brmeyer

Help with JCR-SQL2 query involving ISCHILDNODE and multiple conjunctions

brmeyer Mar 4, 2015 3:11 PM

Background: [ARTIF-656] Cannot query with 2 relationship predicates - JBoss Issue Tracker

Artificer includes an artifact -> relationship -> target node structure. Relevant bits from the CND:

[sramp:target]
- sramp:type (string)
- sramp:targetArtifact (weakreference) < 'sramp:baseArtifactType'
...

[sramp:relationship]
- sramp:relationshipType (string)
+ * (sramp:target)
...

[sramp:baseArtifactType] > mix:created, mix:lastModified, mix:referenceable abstract mixin
+ * (sramp:relationship)
...

The specific Artificer query looks something like:

/s-ramp/wsdl/Part[relatedDocument[@uuid = '%1$s'] and element]

Ie, find me all "Part" artifacts that have 1.) a relationship of type "relatedDocument" that targets the given UUID and 2.) a relationship of type "element"

So, we need to somehow query for sramp:baseArtifactType nodes that have sramp:relationship children. Those children must include a node with relationshipType "relatedDocument" and another node with "element". Those could be the same sramp:relationship nodes, as well as different ones.

If ModeShape allowed selectors to be transitive, we might be able to do something with "ISCHILDNODE" and the conditions in subselects. But, since it doesn't, our parser uses INNER JOINs. Each time a relationship predicate is hit in an Artificer query, the parser assumes it needs to add a join and conditions. With a conjunction, this fails and results in something like:

SELECT artifact1.* FROM [sramp:baseArtifactType] AS artifact1
    INNER JOIN [sramp:relationship] AS relationship2 ON ISCHILDNODE(relationship2,artifact1)
    INNER JOIN [sramp:target] AS target3 ON ISCHILDNODE(target3,relationship2)
    INNER JOIN [sramp:baseArtifactType] AS artifact4 ON target3.[sramp:targetArtifact] = artifact4.[jcr:uuid]
    INNER JOIN [sramp:relationship] AS relationship5 ON ISCHILDNODE(relationship5,artifact1)
    WHERE
        artifact1.[sramp:artifactType] = 'Part'
        AND relationship2.[sramp:relationshipType] = 'relatedDocument'
        AND artifact4.[sramp:uuid] = 'cea13510-e7a6-4195-a18e-c091eb5a939e'
        AND relationship5.[sramp:relationshipType] = 'element'
        AND ISDESCENDANTNODE([sramp:baseArtifactType],'/s-ramp'

The multiple sramp:relationship inner joins are obviously problematic and ModeShape ends up with an assertion failure.

So, if anyone has any ideas about what a JCR-SQL2 query would look like, I'd sincerely appreciate it! In a nutshell, how do you select A, given that it must have children B, where each B selection potentially has its own conditional?

1. Re: Help with JCR-SQL2 query involving ISCHILDNODE and multiple conjunctions

brmeyer Mar 4, 2015 3:20 PM (in response to brmeyer)

Only option I can currently think of is to loosen the query and filter in-memory, but that's less than ideal...
Actions
2. Re: Help with JCR-SQL2 query involving ISCHILDNODE and multiple conjunctions

rhauch Mar 4, 2015 3:32 PM (in response to brmeyer)
If ModeShape allowed selectors to be transitive...

Can you explain what you mean by "allowed selectors to be transitive"?

... we might be able to do something with "ISCHILDNODE" and the conditions in subselects. But, since it doesn't, our parser uses INNER JOINs. Each time a relationship predicate is hit in an Artificer query, the parser assumes it needs to add a join and conditions. With a conjunction, this fails and results in something like:

SELECT artifact1.* FROM [sramp:baseArtifactType] AS artifact1
    INNER JOIN [sramp:relationship] AS relationship2 ON ISCHILDNODE(relationship2,artifact1)
    INNER JOIN [sramp:target] AS target3 ON ISCHILDNODE(target3,relationship2)
    INNER JOIN [sramp:baseArtifactType] AS artifact4 ON target3.[sramp:targetArtifact] = artifact4.[jcr:uuid]
    INNER JOIN [sramp:relationship] AS relationship5 ON ISCHILDNODE(relationship5,artifact1)
    WHERE
        artifact1.[sramp:artifactType] = 'Part'
        AND relationship2.[sramp:relationshipType] = 'relatedDocument'
        AND artifact4.[sramp:uuid] = 'cea13510-e7a6-4195-a18e-c091eb5a939e'
        AND relationship5.[sramp:relationshipType] = 'element'
        AND ISDESCENDANTNODE([sramp:baseArtifactType],'/s-ramp'

Why does this last 'ISDESCENDANTNODE` criteria use 'sramp:baseArtifactType' rather than 'artifact1' or 'artifact4'?

Can you include the query plan for this query? That will more easily show the structure of the resulting query.
Actions
3. Re: Re: Help with JCR-SQL2 query involving ISCHILDNODE and multiple conjunctions

brmeyer Mar 4, 2015 3:40 PM (in response to rhauch)

Can you explain what you mean by "allowed selectors to be transitive"?

Something like:
select artifact1 from ... where "foo" = (select relationship1.relationshipType from ... where ISCHILDNODE(relationship1, artifact1))

The artifact1 selector cannot be used within the subselect.

Why does this last 'ISDESCENDANTNODE` criteria use 'sramp:baseArtifactType' rather than 'artifact1' or 'artifact4'?

Good point -- it should be.

Can you include the query plan for this query? That will more easily show the structure of the resulting query.

Sorry Randall, not exactly sure what you mean by "query plan"

Thanks!
Actions
4. Re: Re: Help with JCR-SQL2 query involving ISCHILDNODE and multiple conjunctions

rhauch Mar 4, 2015 4:20 PM (in response to brmeyer)

Brett Meyer wrote:

Can you explain what you mean by "allowed selectors to be transitive"?

Something like:

select artifact1 from ... where "foo" = (select relationship1.relationshipType from ... where ISCHILDNODE(relationship1, artifact1))

The artifact1 selector cannot be used within the subselect.

Okay, that's just a correlated subquery. Yes, ModeShape only supports non-correlated subqueries, where the subquery is completely independent of the outer query.

Can you include the query plan for this query? That will more easily show the structure of the resulting query.

Sorry Randall, not exactly sure what you mean by "query plan"

The org.modeshape.jcr.api.query.QueryResult interface has a getPlan() method that returns a string representation of the query plan, which is a string representation of the actual optimized plan for how ModeShape executes a particular query. It is extremely helpful in diagnosing why a query is not behaving as expected.

If you don't want to execute the query, you can also cast your Query object to org.modeshape.jcr.api.query.Query and call its explain() method that plans and optimizes the query (like usual) but that does not execute the query. You get back a QueryResult that has no results but it does have the aforementioned plan.
Actions
5. Re: Re: Help with JCR-SQL2 query involving ISCHILDNODE and multiple conjunctions

brmeyer Mar 11, 2015 4:45 PM (in response to rhauch)

Randall, apologies for the delay.

Okay, that's just a correlated subquery. Yes, ModeShape only supports non-correlated subqueries, where the subquery is completely independent of the outer query.

Thanks -- was having a brainless moment

The plan is here -- sorry for the length: gist:0a95580ad049435d21ca

However, I know the query is flawed from the beginning. The duplicated inner joins will be problematic. What I'll ultimately need is something like a union of the following.
select A.* from A inner join B where ISCHILDNODE(B,A) and B.prop1 = 'val1'
select A.* from A inner join B where ISCHILDNODE(B,A) and B.prop2 = 'val2'

IE, "find me all A that 1.) have a B child with prop1 = val1 and 2.) have a B child with prop2 = val2.

My query builder incorrectly adds an inner join and conditional for each child property it runs into. Is building up a union the better route? Any alternatives?

Thanks!
Actions
6. Re: Re: Help with JCR-SQL2 query involving ISCHILDNODE and multiple conjunctions

rhauch Mar 11, 2015 4:53 PM (in response to brmeyer)

However, I know the query is flawed from the beginning. The duplicated inner joins will be problematic. What I'll ultimately need is something like a union of the following.

select A.* from A inner join B where ISCHILDNODE(B,A) and B.prop1 = 'val1'

select A.* from A inner join B where ISCHILDNODE(B,A) and B.prop2 = 'val2'

IE, "find me all A that 1.) have a B child with prop1 = val1 and 2.) have a B child with prop2 = val2.

My query builder incorrectly adds an inner join and conditional for each child property it runs into. Is building up a union the better route? Any alternatives?

These queries are not quite like your other queries, which are using lots of join criteria; there's no way to specify a disjunction in the join criteria.

However, if you can articulate your query in terms of a set operation on other smaller and simpler queries, then by all means you can use UNION, INTERSECT or EXCEPT. Have you tried this approach?
Actions
7. Re: Re: Help with JCR-SQL2 query involving ISCHILDNODE and multiple conjunctions

brmeyer Mar 11, 2015 4:59 PM (in response to rhauch)

These queries are not quite like your other queries, which are using lots of join criteria; there's no way to specify a disjunction in the join criteria.

My mistake -- correction:

UNION
select A.* from A inner join B on ISCHILDNODE(B,A) where B.prop1 = 'val1'
select A.* from A inner join B on ISCHILDNODE(B,A) where B.prop2 = 'val2'

However, if you can articulate your query in terms of a set operation on other smaller and simpler queries, then by all means you can use UNION, INTERSECT or EXCEPT. Have you tried this approach?

Haven't tried it, but it sounds like that's the best approach. Thanks much.
Actions

Go to original post