ICriteria not quite emitting what I want, help please :D

RF · **Joined:** Fri May 30, 2008 3:57 pm **Posts:** 26

NHibernate 1.2
.Net 3.5
SQL Server 2005

Note: I've replaced the explicit property selectors in the following samples with "*" to make it easier to read.

This represents roughly what I'd like to see emitted by NHibernate:

Code:

SELECT *
FROM Tasks this_
WHERE this_.id = (SELECT max(this_0_.id) as y0_
                           FROM Tasks this_0_
                           WHERE (this_0_.scheduled_by = '1' and this_0_.procedure_id in ('1'))
                      GROUP BY this_0_.procedure_id)

Unfortunately this is what's being generated. Note the additional property in the subquery select statement.

Code:

SELECT *
FROM Tasks this_
WHERE this_.id = (SELECT max(this_0_.id) as y0_, this_0_.procedure_id as y1_
                      FROM Tasks this_0_
                      WHERE (this_0_.scheduled_by = '1' and this_0_.procedure_id in ('1'))
                      GROUP BY this_0_.procedure_id)

Here is the criteria code that I'm using to generate said SQL:

Code:

DetachedCriteria dc = DetachedCriteria.For(typeof(TaskModel), "dc1")
            .SetProjection(Projections.ProjectionList()
              .Add(Projections.Max("id"))
              .Add(Projections.GroupProperty("procedureID"),""))
            .Add(
              Expression.And(
                 Expression.Eq("scheduledBy",userID),
                 Expression.InG<Int32>("procedureID",procIDs.ToArray())));

         ICriteria criteria = this.session.CreateCriteria(typeof(TaskModel), "c1")
            .Add( Subqueries.PropertyEq("id",dc) );

         return criteria.List<TaskModel>();

I'm still trying to get my ICriteria legs under me fully, so any help/advice would be greatly appreciated.

If there's any other information you need please let me know.

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

When you use an aggregate function, (in your case, MAX()) whatever other field you use in the GROUP BY has to be in the SELECT list.

Hibernate is just (accurately) following this rule; your initial query lacks that additional property.

RF · **Joined:** Fri May 30, 2008 3:57 pm **Posts:** 26

ok.

The first query executes under SQL Server 2005, the second throws an error. What do I need to do in order to accomplish what I'm attempting?

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

Find a way to remove the GROUP BY altogether.
Maybe removing

Code:

.Add(Projections.GroupProperty("procedureID"),""))

friom your query?

You don't need the GROUP BY, since you should only return a single row with a single field: the id.

RF · **Joined:** Fri May 30, 2008 3:57 pm **Posts:** 26

Unfortunately I'm wanting 1 record for each distinct procedureID, which I believe requires a group by clause.

Unless you know of a better way to do it?

At this point I"m just about to break down and use raw SQL :)

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

But you can't!
The expression inside the =(...) has to return 1 record only.

RF · **Joined:** Fri May 30, 2008 3:57 pm **Posts:** 26

I just tested with more than 1 procedureID and you're absolutely right. I revised the "working" query to look like so:

Code:

SELECT *
FROM Tasks this_
WHERE this_.id in (SELECT max(this_0_.id) as y0_
                      FROM Tasks this_0_
                      WHERE (this_0_.scheduled_by = '1' and this_0_.procedure_id in ('1', '2'))
                      GROUP BY this_0_.procedure_id)

This appears to be working, but let me know if there are any problems with it.

Is there a way to accurately describe this using ICriteria or will I need to resort to raw SQL?

RF · **Joined:** Fri May 30, 2008 3:57 pm **Posts:** 26

ok, I've come up with this revised query.

Code:

SELECT *
FROM Tasks this_
INNER JOIN 
(SELECT max(this_0_.id) as y0_, this_0_.procedure_id as y1_ 
                      FROM Tasks this_0_
                      WHERE (this_0_.scheduled_by = '1' and this_0_.procedure_id in ('1', '2'))
                      GROUP BY this_0_.procedure_id) as chunk
ON this_.id = chunk.y0_

I'm sure it's obvious, but my mad SQL skills don't exist, so I thank you for your patience.

Now I just need to turn this bad boy into an ICriteria query :)

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

This is in HQL, and does produce the SQL you want.
If you absolutely need it to be a Criteria, I can think about it, too.

Code:

      

  StringBuffer sb=new StringBuffer();
  sb.append("select t from Task t   \n");
  sb.append("where t.id in(         \n");
  sb.append("  select max(t2.id)    \n");
  sb.append("  from Task t2         \n");
  sb.append("  where t2.scheduledBy=1  \n");
  sb.append("  and t2.procedureId in (1, 2)  \n");
  sb.append("  group by t2.procedureId  \n");
  sb.append(")                        \n");

  Query query=s1.createQuery(sb.toString());
  List<Task> result=query.list();

RF · **Joined:** Fri May 30, 2008 3:57 pm **Posts:** 26

I was able to adapt that and it works correctly, thanks :)

I'm perfectly happying using hql, I'm just trying to learn ICriteria more deeply since it's programmatic and there's something looming in my future where the dynamic generation of queries via ICriteria is going to be extremely helpful.

If you don't mind, I do have a few general questions about NHibernate.

Can you do an arbitrary join or does NHibernate limit the joins to associated collections?

Is there a way to pluck out the sql being generated by NHibernate programmatically at runtime? I have something in mind and it'd be helpful to be able to grab the generated SQL without it actually querying the DB.

Do you know of an article with a more in-depth look at ICriteria/HQL than the standard NHibernate manual?

and thanks for the help gonzao, it's helped me out a lot.

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

Quote:

Can you do an arbitrary join or does NHibernate limit the joins to associated collections?

Yes. You are not limited to the join specified in your mapping.
However, you will have to use the theta-join style (i.e., comparing ids in the WHERE clause), like it is done in the example subquery. You cannot use the "join" word in such queries.

Quote:

Is there a way to pluck out the sql being generated by NHibernate programmatically at runtime? I have something in mind and it'd be helpful to be able to grab the generated SQL without it actually querying the DB.

No, there isn't.
You can easily log the generated SQL to a special file, for example (which I explain in detail here
http://hibernar.org/articulos_en/logging.php ), but that is only SQL that has been actually executed against the DB.

Quote:

Do you know of an article with a more in-depth look at ICriteria/HQL than the standard NHibernate manual?

Not that I know. And I agree with you that the projections/transformations part is very poorly documented. But there is not that much to it, either. Just keep experimenting stuff until you get the result you want.

For example, at this point I am sure you know more about projections than I do. Nevertheless, playing a little with the example you gave me, I could create the Criteria query you need:
(forgive me the Java notation, I am not much into Microsoft)

Code:

        DetachedCriteria dc = DetachedCriteria.forClass( Task.class)
                    .setProjection(Projections.projectionList()
                      .add(Projections.max("id"))
                      .add(Projections.groupProperty("procedureId"),""))
                    .add(
                      Expression.and(
                         Expression.eq("scheduledBy",1),
                         Expression.in("procedureId",new Integer[]{1,2})));

                 Criteria criteria = s1.createCriteria(Task.class).add( Subqueries.eq("id",dc) );

                 List<Task> result=criteria.list(); 

notice that my only change was that "in(...)", it's very intuitive.