OK, this is probably an easy one for most of you but I'm stumped. I'm using 2.0 adapter and I'm having trouble getting LLBL to obey the join hints I'm telling it to use in order to create the query I need. Instead of using left joins for all, it is switching some to right joins, which is causing the query to return no results.
Here are the tables used in the query and how they are related:
Order -> Cart = m:1
OrderItem -> Order = m:1
Product -> OrderItem = 1:n
Supplier -> Product = 1:n
Person -> Cart = 1:n
Here is the query I am trying to create; note it uses all left joins and selects from the Order table:
SELECT DISTINCT o.ID, s.Name
FROM Order o
LEFT JOIN Cart c on c.ID = o.CartID
LEFT JOIN OrderItem i on o.ID = i.OrderID
LEFT JOIN Product p on i.ProductID = p.ID
LEFT JOIN Supplier s on p.SupplierID = s.ID
LEFT JOIN Person n on n.ID = c.PersonID
WHERE o.ResponseCode = 1 AND n.ID = 12343
So I run the following code:
Dim fields As New ResultsetFields(2)
fields.DefineField(OrderFields.Id, 0)
fields.DefineField(SupplierFields.Name, 1)
Dim filter As New RelationPredicateBucket
filter.Relations.ObeyWeakRelations = True
filter.Relations.Add(CartEntity.Relations.OrderEntityUsingCartId, JoinHint.Left)
filter.Relations.Add(OrderItemEntity.Relations.OrderEntityUsingOrderId, JoinHint.Left)
filter.Relations.Add(ProductEntity.Relations.OrderItemEntityUsingProductId, JoinHint.Left)
Filter.Relations.Add(SupplierEntity.Relations.ProductEntityUsingSupplierId, JoinHint.Left)
Filter.Relations.Add(PersonEntity.Relations.CartEntityUsingPersonId, JoinHint.Left)
Filter.PredicateExpression.Add(OrderFields.ResponseCode = 1)
Filter.PredicateExpression.Add(PersonFields.Id = myUserID)
Dim sort As New SortExpression
sort.Add(New SortClause(OrderFields.DateFinished, Nothing, SortOperator.Descending))
Dim dt As New DataTable
Using adapter As New DataAccessAdapter(myConnStr, True)
adapter.FetchTypedList(fields, dt, filter, 0, sort, False)
End Using
This is giving me the following query; note LLBL has changed some of the joins to right joins and the select is now from the Cart table:
SELECT DISTINCT o.ID, s.Name
FROM Cart c
RIGHT JOIN Order o on c.ID = o.CartID
LEFT JOIN OrderItem i on o.ID = i.OrderID
LEFT JOIN Product p on i.ProductID = p.ID
RIGHT JOIN Supplier s on p.SupplierID = s.ID
RIGHT JOIN Person n on n.ID = c.PersonID
WHERE o.ResponseCode = 1 AND n.ID = 12343
I tried it with and without the ObeyWeakRelations property. I also tried rearranging the order of the join statements. It always seems to come up the same way. What am I doing wrong?
