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

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

CodePudding user response：

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.