JPQL union and Join

Support
#1

This JPQL doesn’t work as expected what did I do wrong?

SELECT distinct ec.event FROM callbook$EventCard ec FULL OUTER JOIN callbook$Event e ON ec.event.id = e.id where ec.id is null 
union
SELECT distinct ec.event FROM callbook$EventCard ec FULL OUTER JOIN callbook$Event e ON ec.event.id = e.id WHERE ec.Card.id <> :session$currentMemberID

I want it to act same way as this plain sql:
image
And also I want to clarify: what exactly is used in Cuba: JPA, hibernate, or ECLIPSELINK Or it depends on component? Oh it seems to be ECLIPSELINK but why doesn’t it then say “Union is not supported” error?

#2

I don’t think JPQL supports UNION command. Here is an article I sometimes refer to when I’m trying to do something in JPQL.

Perhaps you can execute two queries and merge the results in Java? Or run a SQL query?

1 Like
#3

Thanks Keith, but how do you think: is each of the two queries itself correct? (Behave the same as the original native query)?
Turned out it’s eclipseLink which supports UNION
I used “custom Datasource” where I did whatever I wanted in Java and returned Collection but the problem in that approach is that fts (filter and some other features probably) don’t work with such Datasources (without query)… It seemed to me that Ds.commit didn’t work…

#4

The problem seems that https://thoughts-on-java.org/how-to-join-unrelated-entities/ my entities are somehow unrellated But I don’t quite understand how should they be related…

#5

I also don’t understand why
SELECT e FROM callbook$Event e
works and
SELECT ec.event FROM callbook$EventCard ec where ec.card.id <> :session$currentMemberID
works but
SELECT e FROM callbook$Event e UNION SELECT ec.event FROM callbook$EventCard ec where ec.card.id <> :session$currentMemberID
doesn’t? Causes error: Query argument session$currentMemberID not found in the list of parameters provided during query execution

#6

Wow on Russian forum i’s said that union is not supported at all by cuba https://www.cuba-platform.ru/discuss/t/oshibka-v-jpql-nabore-dannyh-s-ispolzovaniem-union-all/2222 Maybe “union all” was meant? Waiting comments from cuba authors

#7

Yes, that is interesting. I’m sorry I can’t confirm with certainty the ability to do UNION. I hope someone can jump in and clarify. It is the feature I miss most from SQL.

#8

I’ve been thinking about the custom datasource approach. It does seem tough without a single query. But is it possible to have a single query that grabs too much but override the getEntities method to filter out the results that your union logic would have taken care of? I’m not sure of volumes and performance conditions… You would need to override getCount too… Just a wild idea.

#9

Unfortunately, JPA specification doesn’t support UNION statement in JPQL. CUBA only works with JPQL defined in the JPA specification.

#10

I paraphrased this question here JPQL select from association recipe2 - CUBA.Platform