I have a Department entity with two optional @ManyToOne fields "faxLinkedTo"
and "phoneNumberLinkedTo" that optionally refer to another Department whose
phone number of fax can be used.
I am writing a query to find out the names of departments that refer to either
the fax or phone number of a given department using the following JPQL:
SELECT d.name, d.faxLinkedTo.id, d.phoneNumberLinkedTo.id
FROM Department d
WHERE d.faxLinkedTo.id IN (:departments) OR d.faxLinkedTo IS NULL OR
d.phoneNumberLinkedTo.id IN (:departments) OR
d.phoneNumberLinkedTo IS NULL
ORDER BY d.name
As a test case I let department A link it's fax to department B by setting
A.faxLinkedTo = B, while having A.phoneNumberLinkedTo = null.
When I run the query to find departments linking to department B, I expect it
to return a row for A.
However I get an empty result because OpenJPA generates query with two inner
joins and the phoneNumberLinkedTo field is null.
I would expect the native query to use no joins at all and just do a simple
select on the Department table.
Is this a bug or am I doing something wrong? Is there a way to let OpenJPA
generate the correct query?
Or should I simply use two separate queries, one for faxLinkedTo and
phoneNumberLinkedTo?
SELECT t0.NAME,
t1.id,
t2.id
FROM dbo.crm_department t0
INNER JOIN dbo.crm_department t1
ON t0.faxlinkedto_id = t1.id
INNER JOIN dbo.crm_department t2
ON t0.phonenumberlinkedto_id = t2.id
WHERE ( t0.faxlinkedto_id IN ( ? )
OR t0.faxlinkedto_id IS NULL
OR t0.phonenumberlinkedto_id IN ( ? )
OR t0.phonenumberlinkedto_id IS NULL )
ORDER BY t0.NAME ASC
Regards,
Henno Vermeulen
