Hello.

I have a 3 object mapping Patient-->PatientCompany-->Company

Patient is bi-directional one-to-many on PatientCompany
PatientCompany is uni-directional many-to-one on Company.

Giving a patient can be hired on many companies which can hold employeement information.

The problem is then i make queries on these. I use a normally large base whith about 1500 Companies and 150 000 Patients.

I use a Paginator. My application uses QBE as query technique.

1. making queries on Patients is ok, i make like querys on firstname, alst name and so on. here i use normal QBE (Example queries).

2. making queris on Company and getting back the patient is not as easy but can be done, i have i bi-directional on the PatientCompany so i can get the patient if i use Company as the query base. then i get the patient attached to PatientCompany and insert them into a List and return them.

hql for 2:
from se.asynja.model.PatientCompany as pc
join fetch pc.company as c
where c.title like :companyTitle

Things i have tried but does not seam to work good or at all is.
2.1. QBE, not supported, since PatientCompany is a collection it a Featurerequest for 3.2, but i dont know if it will be there though.
2.2 making a HQL that fetches all patients and there companies.

hql for 2.2 (very slow, waited 60seconds then terminated tomcat)
from Patient as patient
join fetch patient.patientCompanies as pc
where pc.company.title = :companyTitle

3. If i combine Patient and Company queries then it becomes even more complex and bad.

I hav enot yet made a solution to this one but i think i will go for Company first and then filter the patient hits with the other queries.


Actual question is can i make all 3 cases use the same logic?
And a pretty simple one as well, i realise it cant be QBE but a simple and dynamic hql perhaps.

help appreciated.
/robert