Left outer join restriction problem

pavel.biely · **Joined:** Wed Mar 05, 2014 5:06 am **Posts:** 4

Hi everybody!

I'm trying to add restriction on a right table in the LEFT OUTER JOIN, but it turns into INNER JOIN.

I would like to get all rooms and for those rooms that are booked for current date the visitor's name also (LEFT OUTER JOIN).
But I get only those rooms that are booked for today (INNER JOIN).

My code:

Code:

Timestamp currentTimestamp = CalendarUtils.getCurrentTimestamp();
      
Criteria criteria = session.createCriteria(Room.class, "r")
   .createAlias("bookings", "b", JoinType.LEFT_OUTER_JOIN)
   .createAlias("bookings.account", "a", JoinType.INNER_JOIN)
   .createAlias("bookings.room", "rm", JoinType.NONE)
   .add(Restrictions.le("b.checkinDate", currentTimestamp))
   .add(Restrictions.ge("b.checkoutDate", currentTimestamp));

Generated HQL:

Code:

Hibernate: 
select 
this_.pk_room_id as pk_room_1_3_2_, 
this_.description as descript2_3_2_, 
this_.double_beds as double_b3_3_2_, 
this_.bathroom as bathroom4_3_2_, 
this_.picture_path as picture_5_3_2_, 
this_.price as price6_3_2_, 
this_.single_beds as single_b7_3_2_, 
b1_.fk_room_id as fk_room_5_3_, 
b1_.pk_booking_id as pk_booki1_1_, 
b1_.pk_booking_id as pk_booki1_1_0_, 
b1_.fk_account_id as fk_accou4_1_0_, 
b1_.checkin_date as checkin_2_1_0_, 
b1_.checkout_date as checkout3_1_0_, 
b1_.fk_room_id as fk_room_5_1_0_, 
a2_.pk_account_id as pk_accou1_0_1_, 
a2_.email as email2_0_1_, 
a2_.first_name as first_na3_0_1_, 
a2_.last_name as last_nam4_0_1_, 
a2_.middle_name as middle_n5_0_1_, 
a2_.password as password6_0_1_, 
a2_.fk_role_id as fk_role_7_0_1_ 
from rooms 
this_ left outer join bookings b1_ on this_.pk_room_id=b1_.fk_room_id 
inner join accounts a2_ on b1_.fk_account_id=a2_.pk_account_id 
where b1_.checkin_date<=? and b1_.checkout_date>=?

Is there a way to perform such query in Criteria API?
Thanks in advance.

VampBoy · **Posted:** Wed Mar 05, 2014 5:00 pm

Try specifying the checkin date restriction using the option that allows you to use the with clause.

createAlias(String associationPath, String alias, JoinType joinType, Criterion withClause)

This will create the left outer join with an ON sql snippet and I believe it should do what you want it to do.

pavel.biely · **Joined:** Wed Mar 05, 2014 5:06 am **Posts:** 4

VampBoy, thank you for prompt reply!

I did what you had suggested but unfortunately the result is still not what is needed.

I would like to perform a outer join with a subquery that is selected with restrictions.
But these restriction are applied to join result but not subquery.

New code with you suggestions:

Code:

Criteria criteria = session.createCriteria(Room.class, "r")
   .createAlias("bookings", "b", JoinType.LEFT_OUTER_JOIN, Restrictions.and(
      Restrictions.le("b.checkinDate", currentTimestamp),
      Restrictions.ge("b.checkoutDate", currentTimestamp)))
   .createAlias("bookings.account", "a", JoinType.INNER_JOIN)
   .createAlias("bookings.room", "rm", JoinType.NONE);

Generated SQL:

Code:

Hibernate: 
select 
this_.pk_room_id as pk_room_1_3_2_, 
this_.description as descript2_3_2_, 
this_.double_beds as double_b3_3_2_, 
this_.bathroom as bathroom4_3_2_, 
this_.picture_path as picture_5_3_2_, 
this_.price as price6_3_2_, 
this_.single_beds as single_b7_3_2_, 
b1_.fk_room_id as fk_room_5_3_, 
b1_.pk_booking_id as pk_booki1_1_, 
b1_.pk_booking_id as pk_booki1_1_0_, 
b1_.fk_account_id as fk_accou4_1_0_, 
b1_.checkin_date as checkin_2_1_0_, 
b1_.checkout_date as checkout3_1_0_, 
b1_.fk_room_id as fk_room_5_1_0_, 
a2_.pk_account_id as pk_accou1_0_1_, 
a2_.email as email2_0_1_, 
a2_.first_name as first_na3_0_1_, 
a2_.last_name as last_nam4_0_1_, 
a2_.middle_name as middle_n5_0_1_, 
a2_.password as password6_0_1_, 
a2_.fk_role_id as fk_role_7_0_1_ 
from rooms this_ 
left outer join bookings b1_ 
on this_.pk_room_id=b1_.fk_room_id 
   and ( (b1_.checkin_date<=? and b1_.checkout_date>=?) ) 
inner join accounts a2_ 
on b1_.fk_account_id=a2_.pk_account_id

And I would like to get this SQL code analog:

Code:

SELECT 
r.`pk_room_id`, 
r.`single_beds`, 
r.`double_beds`, 
r.`picture_path`, 
r.`description`, 
r.`bathroom`, 
r.`price`, 
today_bookings.`pk_account_id`, 
today_bookings.`last_name` 
FROM `marcinova_gus_db`.`rooms` AS r 
LEFT OUTER JOIN 
   (SELECT b.`fk_room_id`, a.`pk_account_id`, a.`last_name` FROM `marcinova_gus_db`.`bookings` AS b  
       INNER JOIN `marcinova_gus_db`.`accounts` as a 
       ON a.`pk_account_id`=b.`fk_account_id`
       WHERE b.`checkin_date` <= CURDATE() 
       AND b.`checkout_date` >= CURDATE()) 
   as today_bookings 
ON r.`pk_room_id`=today_bookings.`fk_room_id`;

VampBoy · **Posted:** Thu Mar 06, 2014 1:31 pm

Unfortunately, I think you might be out of luck if you need the subquery on the join depending on which database you're using.

I was playing with the same type of query the other day and there was no way to inject the subquery directly into the join via the CriteriaAPI.

However, I was able to set up a filter on the association and while it would create the SQL that I wanted Oracle doesn't allow subqueries in outer joins and I was basically done. I believe some DBs allow it and you might be able to inject it via a filter but I don't have any guarantees.

pavel.biely · **Joined:** Wed Mar 05, 2014 5:06 am **Posts:** 4

VampBoy,
Thank you for your help!

It's a pity that it is impossible to implement with Criteria API or HQL :(
So I ended up with Native SQL query:

Code:

Query q = session.createSQLQuery("SELECT r.pk_room_id, r.single_beds, r.double_beds, r.picture_path, r.description, "
            +       "r.bathroom, r.price, today_bookings.pk_account_id, today_bookings.last_name "
            + "FROM rooms AS r " 
            + "LEFT OUTER JOIN "
            +    "(SELECT b.fk_room_id, b.pk_booking_id, a.pk_account_id, a.last_name FROM bookings AS b " 
            +    "INNER JOIN accounts as a " 
            +    "ON a.pk_account_id=b.fk_account_id "
            +    "WHERE b.checkin_date <= CURDATE() AND b.checkout_date >= CURDATE()) as today_bookings "
            + "ON r.pk_room_id=today_bookings.fk_room_id;")
            .addEntity("room",Room.class)
            .addScalar("pk_account_id")
            .addScalar("last_name");

VampBoy · **Posted:** Fri Mar 14, 2014 11:20 am

What DB are you running against? I'm 99.9% sure that Oracle would kick that out on you as it doesn't allow subselects in the outer join which is what completely hosed me.

pavel.biely · **Joined:** Wed Mar 05, 2014 5:06 am **Posts:** 4

VampBoy wrote:

What DB are you running against?

MySQL