LEFT JOIN in Hibernate Query Language

Question

Hi, I am trying to do a LEFT JOIN in Hibernate Query Language, in MySQL I can do this as follows:

select * from day_timetable_timeslots t LEFT JOIN golfnine_date_time_entity d ON d.start_time = t.start_time

In my table day_timetable_timeslots I have many time intervals for the whole day with 15 minute increments. eg. 00:00:00, 00:15:00, 00:30:00, ... to end of day. So it shows me all the time intervals with any matching golfnine_date_time_entity, but I cannot seem to work out how to do this in Hibernate Query Language.

Well I can do the LEFT JOIN by the following HQL.

select o.id, book.id from DayTimetableTimeslots o left outer join o.bookings book where o.id > 0

I don't know why I must put the o.id > 0 there, but it works.

However, I want to only select book.id's where the booking has a where condition. I tried:

select o.id, book.id from DayTimetableTimeslots o left outer join o.bookings book where o.id > 0 and book.dateTime.startDate > '2010-01-01'

But this doesn't work correctly, it shows only some of the DayTimetableTimeslots, but not all of them, as it is supposed to do so.

I basically want to do this mysql query in HQL.

select t.id as start_time_sequence, t.start_time as all_start_time, d.* from day_timetable_timeslots t LEFT JOIN golfnine_date_time_entity d ON d.start_time = t.start_time AND t.customer_id = d.customer_id AND d.start_date = '2010-01-24' WHERE t.customer_id = 11

Thanks, Philip

---

In mysql I can do the following and it shows me all bookings against their start time. All start times are stored in day_timetable_timeslots.

select t.start_time, d.id from day_timetable_timeslots t LEFT JOIN golfnine_date_time_entity d ON d.start_time = t.start_time

'00:00:00', NULL
'00:15:00', NULL
'00:30:00', NULL
'00:45:00', NULL
'01:00:00', '443'
'01:15:00', NULL
'01:30:00', NULL
'01:45:00', NULL
'02:00:00', '444'

... for the whole day, it matches up the ids of the golfnine_date_time_entity with the times in day_timetable_timeslots.

The good thing about this mysql query is I can set some criteria on the booking, eg.

select t.id, t.start_time, d.id from day_timetable_timeslots t LEFT JOIN golfnine_date_time_entity d ON d.start_time = t.start_time AND t.customer_id = d.customer_id AND d.start_date = '2010-01-24' WHERE t.customer_id = 11

I get

... lots of data then
'31', '07:15:00', NULL
'32', '07:30:00', NULL
'33', '07:45:00', NULL
'34', '08:00:00', '501'
'35', '08:15:00', NULL
'36', '08:30:00', NULL
'37', '08:45:00', NULL
'38', '09:00:00', NULL
'39', '09:15:00', NULL
... lots more data

So its only showing the booking on that date specified and the customer id.

Its so hard to do this in HQL...

---

This is the join I want in HQL.

select o.id, b.id from DayTimetableTimeslots o, LegacyDateTimeEntity b where b.customer.id = 11 and b.startDate = '2010-02-07' and o.startTime = b.startTime

Gives this SQL.

select
    daytimetab0_.id as col_0_0_,
    legacydate1_.id as col_1_0_ 
from
    day_timetable_timeslots daytimetab0_,
    golfnine_date_time_entity legacydate1_ 
where
    legacydate1_.customer_id=11 
    and legacydate1_.start_date='2010-02-07' 
    and daytimetab0_.start_time=legacydate1_.start_time

But - it only returns 1 row because only one golfnine_date_time_entity matches, I want all of the day_timetable_timeslots rows returned.

So I tried in HQL.

select o.id, o.bookings.size from DayTimetableTimeslots o left join o.bookings book left join book.dateTime dt with dt.customer.id = 11 and dt.startDate = '2010-02-29' where 1 = 1

Which sadly seems to ignore the with expression.

It returns this SQL.

select
    daytimetab0_.id as col_0_0_,
    (select
        count(bookings3_.timeslot_id) 
    from
        golfnine_booking bookings3_ 
    where
        daytimetab0_.id=bookings3_.timeslot_id) as col_1_0_ 
from
    day_timetable_timeslots daytimetab0_ 
left outer join
    golfnine_booking bookings1_ 
        on daytimetab0_.id=bookings1_.timeslot_id 
left outer join
    golfnine_date_time_entity legacydate2_ 
        on bookings1_.date_time_id=legacydate2_.id 
        and (
            legacydate2_.customer_id=11 
            and legacydate2_.start_date='2010-02-29'
        ) 
where
    1=1

Which just joins all matching relationships between tables and ignores legacydate2_.customer_id=11 and legacydate2_.start_date='2010-02-29'

---

I found that this seems to work. Note I am referencing dt2 which is in the with clause.

select distinct o.startTime, dt2.id, book.uniqueDateTimeResource from DayTimetableTimeslots o left join o.bookings book with book.deleted = 0 left join book.dateTime dt2 with dt2.startDate = '2010-01-19' and dt2.deleted = 0

Answer 1

I can think of two potential problems that you are running into:

1. You are having issues with date/time precision between your java and SQL code. You can try adjusting the date by a small increment, and see if the correct values show up.

2. Your hibernate mapping is not correct. It looks like you always join on multiple columns? Your schema is a little bit confusing to me. Do you use the @JoinColumns annotation to specify multiple columns in your association (if you're using annotations), or the equivalent in the XML mapping file?