Hi there!

You probably won't remember, but about a week ago i posted here about a problem i had with createSQLQuery() and joined subclasses.

since i couldn't figure out how to do it at all i wrote an sql-statement that does the job and tried to read all objects with session.load().

now it turned out, that that's terribly slow and i'm trying to figure out if there's another way to do it.

Here's the SQL-Statement:

select T.technicianActorID as currentTechnician,
TAS.tbTechnicianAppointmentSlotsID as currentSlot,
TAS2.tbTechnicianAppointmentSlotsID as previousSlot, TAS3.tbTechnicianAppointmentSlotsID as nextSlot
from tbTechnician as T
left outer join tbTechnicianAppointmentSlots as TAS on TAS.technicianActorID = T.technicianActorID
left outer join tbTechnicianAppointmentSlots as TAS2 on TAS2.technicianActorID = TAS.technicianActorID and TAS2.timeTo <= TAS.timeFrom and TAS2.tbCalendarID = TAS.tbCalendarID and TAS2.technicianAppointmentStatus = 1
left outer join tbTechnicianAppointmentSlots as TAS3 on TAS3.technicianActorID = TAS.technicianActorID and TAS3.timeFrom >= TAS.timeTo and TAS3.tbCalendarID = TAS.tbCalendarID and TAS3.technicianAppointmentStatus = 1
where T.technicianNo in ( 1890, 0774, 9977 )
and TAS.tbCalendarID in ( 18, 19, 20, 21, 22 )
and TAS.technicianAppointmentStatus = 0
order by T.technicianActorID asc, currentDate asc, TAS.timeFrom asc, TAS2.timeFrom desc, TAS3.timeFrom asc

The reason why we couldn't do it with a hibernate query is because of the bold part. Hibernate doesn't seem to support the on-conditionals in joins. We checked and there's no way to express it in the where clause either.

Now, the query above works and is suuuper fast!!! The problem is to load the objects, primary keys of which are returned.

So the question is, Gavin, is there a way to do that with a hibernate query and if not, Max, is there a way to do that with a SQLQuery?

If you need more information please let me know.

kind regards,

Torsten

maybe I did not read your first post...

Why can't you use createSQLQuery for this one ?

so it should be possible? the original problem was that we use inheritance in our data model. technician is a child of user and user of actor. these entities are stored in different tables, and i couldn't find any information on how to cover that with createSQLQuery().

i started off with a very simple query like select * from technician, but since i couldn't get that to work i tried to find another solution.

i even found one, it's very fast, but kind of ugly in my eyes. i get the jdbc connection from hibernate, run a standart sql-qery which returns all the ids and before i load() the associated objects i go ahead and run a hibernate query on the db with all the ids i received before. the result of this query is just discarded, because it can't use it, but sql-server then seems to have the rows in its cache and the load()-ing is incredibly fast.

but i'm conviced there must a better way to do that. if i could just get the createSQLQuery to work.

eh - createSQLQuery just need a result that returns the same rows as you have specified is necessary in the hbm.xml file.

So, without knowing anything ...........

select {technician.*} from technician

Does that not work ?

select {technician.*} from technician did not work because of inheritance. but if i only have to provide all neccessary columns, i'll just try again!