Colleges, I need your advice on the following problem
I need to order results of my query by result of complex subquery of related table.
I have table ORDERS & related table INVOICES (by INVOICES.ORDER_ID = ORDERS.ID).
Table INVOICES has the flag IS_OPEN. Order can be (in compliance with flags of his invoices):
not payed (all INVOICES.IS_OPEN = true), partically payed (part of INVOICES.IS_OPEN = true),
fully payed (all of INVOICES.IS_OPEN != true).
I tried to use SqlProjection & succesfully received only the result of subquery
Code:
DetachedCriteria dc = DetachedCriteria.For<Order>();
IProjection proj = Projections.ProjectionList().Add(Projections.Alias(Projections.SqlProjection(@"(SELECT payedStatus =
case
when (SELECT COUNT(*) FROM INVOICE invoice WHERE invoice.IS_OPEN = 1 AND invoice.ORDER_ID = {alias}.ID) > 0
AND (SELECT COUNT(*) FROM INVOICE invoice WHERE invoice.IS_OPEN = 1 AND invoice.ORDER_ID = {alias}.ID) = (SELECT COUNT(*) FROM INVOICE invoice WHERE AND invoice.ORDER_ID = {alias}.ID
) then 1
when (SELECT COUNT(*) FROM INVOICE invoice
WHERE (SELECT COUNT(*) FROM INVOICE invoice WHERE invoice.IS_OPEN = 1 AND invoice.ORDER_ID = {alias}.ID) > 0 AND (SELECT COUNT(*) FROM INVOICE invoice WHERE invoice.IS_OPEN = 1 AND invoice.ORDER_ID = {alias}.ID)
<> (SELECT COUNT(*) FROM INVOICE invoice WHERE invoice.ORDER_ID = {alias}.ID) then 0
else 2
end) as payedStatus ", new[] { "payedStatus " }, new IType[] { NHibernateUtil.Int32 }), "PayedStatus"));
dc.SetProjection(proj)
.AddOrder(new Order("PayedStatus", Asc));
In this case if i try to use
Code:
return dc.GetExecutableCriteria(session ).List<Order>();
it throw exception. If add before this
Code:
dc.SetResultTransformer(Transformers.AliasToBean(typeof(Order)));
i receive List of empty Order's. I was try to add properties of Order using
Code:
.Add(Projections.Property(""))
but still receive the same IList.
How can i order(sort) my list of Orders by PayedStatus received by subquery?
Login
FAQ
Search