Error when executing DQL query

Hello,

In our EM based application, we are structuring a DQL query to retrieve products from EM according to some criteria, here is the query:

select p._uuid as prodId,p.name as prodName from productArtifact p
left join businessServiceArtifact feature using p.aggregates
left join goalArtifact goal using p.associatedWithIncoming
left join driverArtifact driver using p.influencedBy
left join constraintArtifact constraint using p.influencedBy
where (lower(p.name) like :searchcriteriaWildcard OR lower(p.description) like :searchcriteriaWildcard OR lower(p.keyword.val) like :searchcriteriaWildcard
or lower(feature.name) like :searchcriteriaWildcard OR lower(feature.description) like :searchcriteriaWildcard
or lower(goal.name) like :searchcriteriaWildcard OR lower(goal.description) like :searchcriteriaWildcard
or lower(driver.name) like :searchcriteriaWildcard OR lower(driver.description) like :searchcriteriaWildcard
or lower(constraint.name) like :searchcriteriaWildcard OR lower(constraint.description) like :searchcriteriaWildcard
)

when executing it, I get the attached error (error1.png), I noticed that the error occurrs when both clauses (left join driverArtifact driver using p.influencedBy) and (left join constraintArtifact constraint using p.influencedBy) exist at the same time because when I remove one of them and keep the other the query run successfully, I am guessing the reason is that in both those clauses we are using (using p.influencedBy), I tried to change the two clauses to be like this

left join driverArtifact driver on bind(p.influencedBy)
left join constraintArtifact constraint on bind(p.influencedBy)

but got the same error, when I change one of the clauses to be like this:
left join driverArtifact driver on driver._uuid = p.influencedBy.target
I get the error attached (error2.png)

can you please advise how to structure the query correctly keeping both the relations

Thanks,
Hossam.

Tags:

Parents
  • Verified Answer

    Hi Hassan,

    There are several problems in DQL that appear when using incoming relationships (associatedWithIncoming, influencedBy) to join tables and/or when joining tables with the using keyword. To avoid these problems, use only outgoing relationships and join tables using the the approach "on bind(sourceAlias.outgoingProperty,targetAlias) ".

    After the modifications, your query starts with:

    select p._uuid as prodId,p.name as prodName from productArtifact p
    left join businessServiceArtifact feature on bind(p.aggregates,feature)
    left join goalArtifact goal on bind(goal.associatedWith,p)
    left join driverArtifact driver on bind(driver.influences,p)
    left join constraintArtifact constraint on bind(constraint.influences,p)

    Best Regards

     -pavel-

Reply
  • Verified Answer

    Hi Hassan,

    There are several problems in DQL that appear when using incoming relationships (associatedWithIncoming, influencedBy) to join tables and/or when joining tables with the using keyword. To avoid these problems, use only outgoing relationships and join tables using the the approach "on bind(sourceAlias.outgoingProperty,targetAlias) ".

    After the modifications, your query starts with:

    select p._uuid as prodId,p.name as prodName from productArtifact p
    left join businessServiceArtifact feature on bind(p.aggregates,feature)
    left join goalArtifact goal on bind(goal.associatedWith,p)
    left join driverArtifact driver on bind(driver.influences,p)
    left join constraintArtifact constraint on bind(constraint.influences,p)

    Best Regards

     -pavel-

Children