In my Grails application I had defined a domain object called Order (mapped to a work_order table since order is a reserved word in MySQL) which has a one-to-many relationship with another domain object called OrderProductItem in a property called products and also a one-to-many relationship with another domain object called OrderServiceItem in a property called services. In turn, OrderProductItem holds a StoreProduct domain object in a property called item and OrderServiceItem holds a StoreSvc domain object in a property called item.
In a query for Order LIST, I would like to support keywords search on the OrderProductItem's item's name property or OrderServiceItem's item's name property, in an Order, among others. Naturally, in my HQL, I make use of LEFT OUTER JOIN for these like below:
SELECT DISTINCT o
FROM Order o
LEFT OUTER JOIN o.store.managers AS m
LEFT OUTER JOIN o.products AS p
LEFT OUTER JOIN o.services AS s
WHERE o.store = :store
AND (o.store IS NULL OR m.id = :userId)
AND (TRIM(LOWER(o.identifier)) LIKE LOWER(:keywords) OR
TRIM(LOWER(o.description)) LIKE LOWER(:keywords) OR
TRIM(LOWER(o.customer.name)) LIKE LOWER(:keywords) OR
TRIM(LOWER(o.customer.description)) LIKE LOWER(:keywords) OR
TRIM(LOWER(o.customer.phone)) LIKE LOWER(:keywords) OR
TRIM(LOWER(p.item.name)) LIKE LOWER(:keywords) OR
TRIM(LOWER(p.item.product.name)) LIKE LOWER(:keywords) OR
TRIM(LOWER(p.item.product.specifications)) LIKE LOWER(:keywords) OR
TRIM(LOWER(p.item.product.brand)) LIKE LOWER(:keywords) OR
TRIM(LOWER(s.item.name)) LIKE LOWER(:keywords) OR
TRIM(LOWER(s.item.specifications)) LIKE LOWER(:keywords))
ORDER BY o.orderTime desc
After running this query a few times, I noticed that it only returns Order objects which has both products AND services. In other words, Order objects which has ONLY products and whose item's name property matches the keywords, won't be returned. Or, Order objects which has ONLY services and whose item's name property matches the keywords, won't be returned.
Undoubtedly, I know this must have something to do with the LEFT OUTER JOIN I have for both products and services. But I had thought LEFT OUTER JOIN means it can return rows for which products is NULL or services is NULL too. And given that my WHERE clause involving the keywords is entirely made up of OR sub-clauses, I cannot understand why this suddenly turns into an AND condition for having both products and services be "present" (or non-null) in the Order objects that get returned. I even tried adding p IS NULL and s IS NULL in my OR sub-clauses to no avail.
Can anyone please take a look at my query and see if there's anything I have missed? Thank you!
Have you checked the generated query? I think it happens, because you are accessing p.item and s.item which are loaded with an inner join.