How can I replace SQL query with UNION to receive same responce using JPA?

Question

I've got some troubles to get distinct sender and receiver from database for my chat application:

This is my message model:

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Integer id;

@NotNull
private String Content;

@Temporal(TemporalType.TIMESTAMP)
private Date date = new Date();

@ManyToOne
@JoinColumn(name = "sender_id")
private User sender;

@ManyToOne
@JoinColumn(name = "receiver_id")
private User receiver;

Assuming we are logged in as user A and there's database like this:

sender	receiver
A	B
B	A
A	C
D	A

The result I would like to achieve is:

B C D

Using SQL I can achieve this using this query:

select distinct receiver_id from message where sender_id =4 union 
select distinct sender_id from message as m where receiver_id = 4

But there is the problem, JPA does not support UNION :(

Send Help PLS

@TimBiegeleisen thats true, but then resault would be just an Integer not an User object — Numpek2k, Dec 08 '22 at 23:47
JPA doesn't support union, but check [this SO question](https://stackoverflow.com/questions/18958614/union-to-jpa-query) for a workaround using a join. Not sure how well it would perform. — Tim Biegeleisen, Dec 08 '22 at 23:48
Note that the DISTINCT in your SQL query is useless as `UNION` will already remove duplicates — , Dec 09 '22 at 07:26
@a_horse_with_no_name Oh it's true, yesterday I didn't even notice. — Numpek2k, Dec 09 '22 at 10:22
Is the amount of data big enough to fordib you to do 2 queries and make the union in memory ? — Pierre Demeestere, Dec 09 '22 at 11:39

score 1 · Accepted Answer · answered Dec 09 '22 at 20:21

You can select either the sender or the receiver based on specification of a target value. (see demo)

select string_agg(res, ' ' order by res) 
  from ( 
        select distinct 
               case when sender = 'A'
                    then receiver 
                    else sender 
                end res
           from test
          where 'A'  in (sender, receiver)
      ) sq;

That is of course straight SQL. Unfortunately I do not know your obscurification manager (JPA?) so you will have to translate. Or just use raw SQL.

How can I replace SQL query with UNION to receive same responce using JPA?

1 Answers1