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 tables 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));
[/code]
In this case if i try to use
[code]return dc.GetExecutableCriteria(session
).List<Order>();[/
code]
it throw exception. If add before this
[code]dc.SetResultTransformer(Transformers.AliasToBean(typeof(Order)));
[/code]
i receive List of empty Order's. I was try to add properties of Order
using [code].Add(Projections.Property(""))[/code] but still receive
the same IList.
How can i order(sort) my list of Orders by PayedStatus received by
subquery?
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
