Hibernate @ManyToMany joinTable - OrderBy using join table's field

Question

There are 3 tables:

TABLE_A
ID_A
field1
fieldN

TABLE_B
ID_B
field1
fieldN

TABLE_A_B
ID_A
ID_B
orderField

public class A(){
    @ManyToMany
    @JoinTable(name="TABLE_A_B", joinColumns={@JoinColumn(name="ID_A")}, inverseJoinColumns={@JoinColumn(name="ID_B")})
    @OrderBy(value="orderField")
    private List<TABLE_B> BList;
}

But it isn't working, instead I get a runtime error:

Caused by: org.postgresql.util.PSQLException: ERROR: column B1_.orderField doesn't exist Position: 1437

Hibernate searches the field in TABLE_B. Is it possible to use "OrderBy" in a many to many relationship using a join table's field? Or any other way to get to order based in the same field?

Answer 1

Your code is correct, did you try that, it will work. One problem is that you are using list so while you having that list in cache probably wouldn't be sorted, but if you clear current session and fetch it again it will be ordered as you set with @OrderBy(value="orderField").

Also there is another issue with @OrderBy regarding to a hibernate documentation;

Lists can be mapped in two different ways:

• as ordered lists, where the order is not materialized in the database
• as indexed lists, where the order is materialized in the database

To order lists in memory, add @javax.persistence.OrderBy to your property.

But I've just tried your problem and query on database has order by parameter (hibernate.show_sql=true), so I'm not sure what they meant with upper statement.

My query example;

select ... from ... inner join ... where users0_.event_id=? order by user1_.fullname

I'll conclude, if ordering is done on database then you can safely use @OrderBy annotation.

UPDATE:

@OrderBy should have native sql value, for example;

@OrderBy(value="orderField")
private List<TABLE_B> BList;

where orderField is database name of the column in table TABLE_B, also it is possible to do this @OrderBy(value="lower(fullname) desc")

If you want to create column in join TABLE_A_B which keeps same order as it is in your list in java, then you should use @OrderColumn(name="orderField")

Try this;

@ManyToMany
@JoinTable(name="TABLE_A_B", joinColumns={@JoinColumn(name="ID_A")}, inverseJoinColumns={@JoinColumn(name="ID_B")})
@OrderColumn(name="orderField")
private List<TABLE_B> BList;