[nhusers] HQL Hibernate INNER JOIN SUBQuery

[Koushik Paul]

I have some experience in SQL, but my knowledge in HQL is limited

Here is my SQL query

select * from
(select * from readings join
(select max(signal) as signal,sender as mac ,receiver as rc from readings where created_datetime between '2018-02-05 10:25:52' AND '2018-04-05 10:25:52' group by readings.sender,readings.receiver)a
on
a.signal=readings.signal and
a.mac=readings.sender and
a.rc=readings.receiver ) c
group by c.sender,c.receiver;

Note:The outer most query is to get didtinct results only.

And the DTO

public class Readings implements java.io.Serializable {
private Integer id;
private String sender;
private String major;
private String minor;
private int signal;
private BigDecimal temperature;
private String batteryLife;
private String receiver;
private Date createdDatetime;
}
Criteria cr = session.createCriteria(com.xyz.hibernate.dto.Readings.class)
.setProjection(Projections.projectionList()
.add(Projections.max("signal"))
.add(Projections.groupProperty("sender"))
.add(Projections.groupProperty("receiver")));
cr.add(Restrictions.between("createdDatetime", from, to));



String query="select max(r.signal) as signal,r.sender as mac ,r.receiver as rc from com.xyz.hibernate.dto.Readings r where r.createdDatetime between :from AND :to group by r.sender,r.receiver";
Query createQuery = session.createQuery(query);
createQuery.setParameter("from", from);
createQuery.setParameter("to", to);

So far I was able to complete only the inner most query in above two ways,
but I am kind of stuck there.

This one works >>select max(r.signal) as signal,r.sender as mac ,r.receiver
as rc from com.xyz.hibernate.dto.Readings r where r.createdDatetime between
:from AND :to group by r.sender,r.receiver

But this one doesnt >>select first.signal from (select max(r.signal) as
signal,r.sender as mac ,r.receiver as rc from
com.xyz.hibernate.dto.Readings r where r.createdDatetime between :from AND
:to group by r.sender,r.receiver)first

If possible I would like to go with the first approach, like pure HQL, but
I really doubt if thats possible.

Any suggestion will be much helpful