I have a table
cartitems where the items added to the cart are stored in. It has columns:
id (Primary Key), sku, quantity, userId, status, size and couple of other columns
I also have a
productAvailability table in which we store Inventory levels and where I have following columns:
Quote:
sku, size , quantity, BatchId (Primary Key)
Also
product table has one-to-many relationships with
productAvailability table and
cartitems table both on the
sku key
Now, for each cartitems with status = 1 and userId=x we need to check the availability of the product and if available, I need the CartItems row and the BatchId of the product:
I got till here:
Code:
Criteria criteria = session.createCriteria(CartItems.class,"cartitems");
criteria.add(Restrictions.eq("userId", userId));
criteria.add(Restrictions.eq("status", status));
criteria.createAlias("product", "product");
criteria.createAlias("product.productAvailability", "productavailability");
criteria.add(Restrictions.eqProperty("productavailability.size", "cartitems.size"));
criteria.add(Restrictions.geProperty("productavailability.quantity", "cartitems.quantity"));
List<CartItems> cartItems = criteria.list();
The above basically does following:
Code:
select * from cartItems
where productAvailability.quantity >= cartitems.quantity and
productAvailability.size = cartitems.size and
productAvailability.sku = cartitems.sku and
cartitems.userId = ? and
cartitems.status = ?
This way I cannot get the BatchId. I tried using JOIN FETCHTYPE but it still loads productAvailability table with all sizes of an sku. Is there any way for me to get both cartItems and corresponding BatchId for each cartItem?
Login
FAQ
Search