CODEWITHSUNDEEP
javahibernatejpaspring-data-jpa
Sebastiaan van den Broek
Sebastiaan van den Broek

Reputation: 6331

JPA Query with 2 inner joins and a sort based on either one of the joined objects

I'm creating a JPA query where I want to sort on an email address. The table I'm querying is a Member table. This Member can EITHER point at an Account OR an Invite. Whether one of those associations is filled can be seen by the MemberStatus enumeration.

@Entity
public class Member {
    @JoinColumn @ManyToOne private Account account;
    @JoinColumn @OneToOne private Invite invite;
    @Enumerated private MemberStatus status; //value can be INVITED or JOINED
}

So BOTH Account and Invite contain a String field called emailAddress. For the intents of this question, consider them to look like this: 

@Entity
public class Account/Invite {
    private String emailAddress;
}

I want to retrieve all members, left join on Account and Invite and sort on emailAddress. If I write a query like this:

@NamedQuery(
    name="findMembers",
    query="select m from Member m
        left join m.invite i
        left join m.account a
        order by emailAddress asc"
)

Then I get an exception saying:

org.postgresql.util.PSQLException: ERROR: column "emailaddress" does not exist Hint: Perhaps you meant to reference the column "invite1_.email_address" or the column "account2_.email_address".

Which makes sense of course. But is there a way to add some alias to this emailAddress field depending on which left joined table is present? Is this even possible in SQL, let alone JPA? From a database perspective I'm not sure how it would work.

Btw, I do not want to go in the direction of database inheritance where both these referenced entities have the emailAddress field. That has too many downsides compared to the benefit.

Upvotes: 1

Views: 1381

Answers (1)

Halko Karr-Sajtarevic
Halko Karr-Sajtarevic

Reputation: 2268

You could use SQLs coalesce function which should be supported in JPA>=2.0. e.g.

@NamedQuery(
name="findMembers",
query="select m from Member m
    left join m.invite i
    left join m.account a
    order by coalesce(i.emailAddress, a.emailAddress) asc"
)

Upvotes: 4

Related Questions