Geek Answers Handbook

LEFT JOIN in Hibernate Query Language

I am trying to make a LEFT JOIN in the Hibernate Query Language, in MySQL I can do it like this:

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

In my day_timetable_timeslots table, I have many time slots for the whole day with a 15-minute step. eg. 00:00:00, 00:15:00, 00:30:00, ... until the end of the day. This way it shows me all the time slots with any golfnine_date_time_entity mapping, but I can't figure out how to do this in the Hibernate Query Language.

Well, I can make LEFT JOIN the next HQL.

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

I do not know why I should put o.id> 0 there, but it works.

However, I only want to choose book.id, where the reservation has conditions. I tried:

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

But this does not work correctly, it only displays some of the DayTimetableTimeslots, but not all of them, as this should be done.

Basically I want to make 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 the orders against their start time. All starting points 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'

... all day long, it matches golfnine_date_time_entity ids with time at day_timetable_timeslots.

It is good that in this mysql query I can set some criteria when booking, for example.

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

Thus, it shows only the reservation on the specified date and customer ID.

Its so hard to do in HQL ...

This is the connection 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 returns only 1 row, because only one golfnine_date_time_entity name matches, I want all day_timetable_timeslots returned rows to be 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, unfortunately, ignores the expression with.

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 only joins all matches between tables and ignores legacydate2_.customer_id = 11 and legacydate2_.start_date = '2010-02-29'

I found this to work. Note. I refer to dt2, which is in the with clause.

select a separate 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

+6
left-join hibernate
source share
1 answer

I can think of two potential problems that you face:

  • You are having problems with date / time accuracy between your Java and SQL code. You can try adjusting the date with a small increment and see if the correct values ​​are displayed.

  • Your hibernation mapping is incorrect. Looks like you always join multiple columns? Your circuit is a little confusing to me. Are you using the @JoinColumns annotation to indicate multiple columns in your association (if you are using annotations) or the equivalent in an XML mapping file?

+1
source share

More articles:


All Articles