is it possible to transform a SQL query with a subquery in 'FROM' clause because of HQL

Question

I have 2 tables: Machine and MachineAvailability. I want to retrieve availabilities from a given machine. Even if there's no availabilities matching my criterias, I would still want to retrieve my machine. So I wrote this native SQL query :

select *
from (
       select ma.*
       from machine m1
       join machine_availability ma on m1.id = ma.id_machine
       where m1.id = 43
         and ma.available is true
         and ma.id_day > '20190228'
     ) as m
       right join machine m2 on m2.id = m.id_machine
where m2.id = 43;

The thing is that i'm obligated to make it an HQL query because my @OneToMany association between Machine and MachineAvailability is LAZY. But i read in the doc that HQL doesn't support subqueries in 'FROM' clause.

Anyone has an idea how I could transform it to make it an HQL?

Or maybe I could use my native query if I change something in my java code?

Answer 1

Simplify your SQL first

First off, simplify your SQL to this (there's no point in accessing machine twice):

select *
from machine m
left join machine_availability ma 
  on m.id = ma.id_machine
  and ma.available is true
  and ma.id_day > '20190228'
where m.id = 43;

And now, this should be trivial to convert to HQL, or, just execute a native query.

A side note on using strings as dates

If you can, I strongly recommend not using human readable strings for your dates. It will only lead to major confusion in the optimiser's cardinality estimates for your predicates, leading to full table scans when indexes would be suitable and vice versa. I've blogged about this here.