Left Join an Element collection using JPQL

Question

Suppose I have this classes, notice that entities are not related in anyway.

@Entity
class Laptop {
 @Id
 String userId;
 @Column
 String name;
 ...
}

@Entity
class Foo {
 ...

 @Column
 @ElementCollection
 List<String> idsPointingToAnEntity;

 ...
}

Given that I have this Foo class which has an attribute of idsPointingToAnEntity, I could store there a list of Laptop ids or any ids of String. Let's say Foo has idsPointingToAnEntity=[1,2,3], this [1,2,3] has an equivalent Laptop entry in the database.

How can I left join/ join them with ordering in JPQL such given that the result is a list of Foo sorted by Laptop names.

In theory I think it is something like this.

Select f From Foo f LEFT JOIN Laptop l ON l.id IN f.idsPoitingToAnEntity ORDER by l.name

But this is having an error for me since f.idsPoitingToAnEntity is a Join table.

Note: idsPointingToAnEntity can also be another entity making List<Laptop> is not an option

Can't you use `@OrderColumn` on `idsPointingToAnEntity` ? Target table contains a column `name` so `@OrderColumn(name = "name")` should work with `@ElementCollection`... Example [here](https://vladmihalcea.com/how-to-optimize-unidirectional-collections-with-jpa-and-hibernate/), check header "**Adding an OrderColumn**" — buræquete, Feb 27 '19 at 04:16
@OrderColumn will not work since it is not tied up to a name, it can be any entities' attribute — bumblebeen, Mar 06 '19 at 07:15

Peter Šály · Answer 1 · 2019-03-03T21:08:55.180

1

If you are using JPA 2.1 or higher you use JOIN ON in JPQL on unrelated entities. Join the IDs table first, like this:

select f from Foo f 
join f.idsPointingToAnEntity id 
join Laptop l ON id=l.id
join AnotherEntity a ON id=a.id

edited Mar 03 '19 at 21:08

answered Mar 03 '19 at 20:36

Peter Šály

2,848
2
12
26

score 0 · Answer 2 · answered Feb 27 '19 at 03:48

You can use no Joins to obtain this result:

SELECT l FROM Laptop l, Foo f
WHERE l.id IN f.idsPointingToAnEntity ORDER BY l.name

Or use CROSS JOIN, a special kind of join that don't require any matching conditions:

SELECT l FROM Laptop l
CROSS JOIN Foo f
WHERE l.id IN f.idsPointingToAnEntity ORDER BY l.name

PS: the problem of using cross join in this scenario, is that it generates the Cartesian Product, not all the rows from left side.

score 0 · Answer 3 · answered Mar 03 '19 at 22:49

Short answer here is simply don't do that. It is misusing the specific feature and likely to lead to troubles as your list grows. You have no good justification for doing this. If you think the list is small then the database load is minimal and there is no reason to put references into an ElementCollection. If the list gets large you definitely don't want an ElementCollection.

Reference Difference between @OneToMany and @ElementCollection?. An ElementCollection is for creating a OneToMany between, in your case, Foo and idsPointingToAnEntity. It's definitely not meant to be used as a One-To-Many Relationship with Join Table.

Left Join an Element collection using JPQL

3 Answers3