Image 3 entities: Item, Collection (many-many to items), Ownership (connecting a user to either an Item or a Collection). What I’m trying to do is to load data into a table showing which items a user owns whether by owning the item directly or by owning the collection as a whole. What I’ve tried is:
select coalesce(ownedItem, collItem), coll, ownership.id from Ownership ownership left join ownership.item ownedItem left join ownership.collection coll left join coll.items collItem
However, it does not like that coalesce function. I’ve also tried translating that coalesce into a CASE statement but that did not work either. It doesn’t seem to like that fact that I am trying to return an entity reference from the coalesce instead of columns, even though both ownedItem and collItem both point to the same table in the end.
Is this intended behavior? If so, any advice on how to write such a query within the constraints of Cuba’s JPQL implementation?