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
– Numpek2k, Commented Dec 8, 2022 at 23:47
JPA doesn't support union, but check this SO question for a workaround using a join. Not sure how well it would perform. — Tim Biegeleisen
– Tim Biegeleisen, Commented Dec 8, 2022 at 23:48
Note that the DISTINCT in your SQL query is useless as UNION will already remove duplicates — user330315
– user330315, Commented Dec 9, 2022 at 7:26

Belayer · Accepted Answer · 2022-12-09 20:21:10Z

1

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.

answered Dec 9, 2022 at 20:21

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related