Nhibernate: left outer join on subquery

Question

Update take 2

here is the two queries i'm working with (paging is omitted in both queries)

i'd like to get the following query

SELECT * 
FROM product
LEFT OUTER JOIN 
(
    SELECT * 
    FROM Cart 
    LEFT OUTER JOIN 
    cartproducts 
    ON Cart.Id = cartproducts.Cart_id 
    WHERE Cart.username = 'user'
)
AS CartFiltered 
ON product.Id = CartFiltered.product_id

but i always seem to get

SELECT * 
FROM product  
LEFT OUTER JOIN 
cartproducts 
ON product.Id = cartproducts.Product_id 
LEFT OUTER JOIN
Cart
ON
cartproducts.cart_id = cart.id
WHERE Cart.username = 'user'

How can i manage to create the first type of query? I hope my question is clearer :) lack of clarity is sometimes a great enemy of mine :p

---

Update:

FWIW, i still haven't found the answer, and am currently loading the paged product data and the whole cart to display the correct object. Crude solution but it works and it beats the combinatorials i've been through trying to make the Criteria API recognize me as its master. I would be very interested if somebody could happen to point me in the right direction though ;)

---

Hello,

i'm having a hard time writing the following query in the Criteria API, and i don't really see how to do it: i hope some people can help.

On the database, i have products. Theses products can be in many carts (one cart per user), and each cart can contain many products, so we have a manytomany relationship. I would like to display a list of every product, along with a small icon next to it to inform the user that this particular product is already in the cart. What i did is i asked NHibernate for my products, and to do a left outer join on carts filtered by the cart's owner.

Dim critPage As ICriteria = Session.CreateCriteria(GetType(Product)) _
.SetFirstResult(pageNumber * itemsPerPage).SetMaxResults(itemsPerPage) _
.CreateCriteria("Carts", "c", SqlCommand.JoinType.LeftOuterJoin) _
.SetProjection(plist) _ 
.SetResultTransformer(New TypedResultTransformer(Of ProductWithCartInfo)) _
.Add(Expression.Eq("c.User", username))

the projection list is here to reduce the number of columns to what's interesting for the ProductWithCartInfo class. It contains only property projections.

The problem is that with this query, the cart filtering is applied to the whole result set and i don't see every product with its presence in the user's cart, but rather every product in the user's cart.

Is it possible to do a left outer join on a subquery with the Criteria API in Nhibernate? For information, i would like to keep it in the Criteria API if possible. Thanks

Answer 1

I'm not sure I entirly follow your issue but it sounds very similar to an issue I had. I seems that when you have the nested (sub) criteira with NHibernate you loose some of the control. I was able to get around my issue but aliasing my tables instead of using the nested criteira.

Possibly try...

criteria.CreateAlias("Cart", "Cart", JoinType.LeftOuterJoin);

and then your filter on the cart will have to be an OR condition

ICriterion cartCriterion = Restrictions.Eq("Cart.User", username);
customerCriterion = Restrictions.Or(customerCriterion, Restrictions.IsNull("Cart.User"));
criteria.Add(customerCriterion);

Let me know if that helps you out... if not... maybe post the SQL that your criteria above is generating and where it needs to change.

Good Luck