Complex Criteria (UNION ALL)

jeusdi · **Joined:** Mon Oct 02, 2006 12:03 pm **Posts:** 62

I need create a query or criteria that represents this sentence -->

Code:

 SELECT ARTICLE_ID, SITE, QUANTITY FROM (
                 (SELECT ARTICLE_ID, TO_PLACE_ID SITE, AMOUNT QUANTITY    FROM STOCK_MOVEMENTS WHERE TO_PLACE_ID IS NOT NULL)
             UNION ALL 
                 (SELECT ARTICLE_ID, FROM_PLACE_ID SITE, -AMOUNT QUANTITY FROM STOCK_MOVEMENTS WHERE FROM_PLACE_ID IS NOT NULL)) 
   AS RESULT;

The result is as -->

+------------+------+----------+
| ARTICLE_ID | SITE | QUANTITY |
+------------+------+----------+
| 3 | 0 | 2 |
| 3 | 0 | 2 |
| 3 | 0 | 2 |
| 3 | 0 | 58 |
| 3 | 0 | 58 |
| 3 | 0 | 0 |
| 3 | 0 | 0 |
| 3 | 0 | 0 |
| 3 | 0 | 0 |
+------------+------+----------+

Mmm, I need a Stock class that has a Article, Site and int properties, and I need that the criteria returns me a list of Stock objects.

Code:

public class Stock {
   private Model.Entities.Article article;
   private Model.Entities.Site site;
   private int amount;
   
   ....
}

Can you help me, please?
Thanks in advanced.

Tomaz · **Joined:** Tue Oct 11, 2005 8:02 am **Posts:** 6

AFAIK there is no union support in HQL. But it's quite easy to implement custom union feature - you have to read two separate lists of your Stock objects and append one list to another. If you would have difficulties with constructing HQL drop a line here.

jeusdi · **Joined:** Mon Oct 02, 2006 12:03 pm **Posts:** 62

Can you help me how Can I "transform" the ARTICLE_ID to a Article Entity, so

Stock class has this members -->

Model.Entites.Article Article
Model.Entities.Site Site
int amount

But, In my select sentence I obtain ARTICLE_ID, and SITE_ID. How Can I transform the ids to Entities?

Thanks for all.

Tomaz · **Joined:** Tue Oct 11, 2005 8:02 am **Posts:** 6

If you want to read the list of entities (which is not always the best practice) you can do it with the CreateCriteria method:

Code:

ISession session = NHibernateSessionFactory.OpenSession();
ICriteria criteria = session.CreateCriteria(typeof(Stock));
criteria.CreateAlias("Site", "s");
SimpleExpression expression = Expression.IsNotNull("s.Id");
criteria.Add(expression);
return criteria.List();

Change the property name "Id" to your real id property name of your Site class.
I don't know exactly if TO_PLACE_ID and FROM_PLACE_ID represents different PK's from two different tables but I hope the example above gives you a hint how to do it.

You can find more examples and explanation about ICriteria usage in the pdf file which is included in the zip:
http://www.hibernate.org/379.html