Problems to write criteria with subquery

rhossi · **Joined:** Fri Feb 22, 2008 9:40 am **Posts:** 7

I need to: select all customers, for each customer select last added phone
I wrote it in SQL, it works. But, how to express the following SQL query using NHibernate ?

Code:

select p.Name, ph.Number
from People p 
inner join Phones ph on ph.PersonId = p.Id
where ph.Id in (select max(Id) from Phones group by PersonId)

I've come up with the following NH query (DetachedCriteria + Projections)
and unfortunatelly it didn't works.

Code:

         DetachedCriteria lastPhones = DetachedCriteria.For(typeof(Phone))
            .SetProjection(Projections.ProjectionList()
            .Add(Projections.GroupProperty("Person.Id"))
            .Add(Projections.Max("Id")));

         DetachedCriteria criteria = DetachedCriteria.For(typeof(Person), "p");
         criteria.CreateCriteria("p.Phones", "ph", JoinType.LeftOuterJoin)
            .SetProjection(Projections.ProjectionList()
            .Add(Projections.Property("p.Id"))
            .Add(Projections.Property("p.Name"))
            .Add(Projections.Property("ph.Id"))
            .Add(Projections.Property("ph.Number")))
            .Add(Subqueries.In("ph.Id", lastPhones));

         return criteria.GetExecutableCriteria(session).List();

wolli · **Posted:** Fri Feb 22, 2008 10:21 am

I would say you have to specifiy

Add(Projections.GroupProperty("Person"))

instead of Person.Id.

rhossi · **Joined:** Fri Feb 22, 2008 9:40 am **Posts:** 7

Wolli, this works too. The difference between

A) Projections.GroupProperty("Person")
B) Projections.GroupProperty("Person.Id")

In A situation, the projection returns a list composed by an array of object[2]
where object[0] = an instance of person and object[1] is the phone id

In B situation, the projection returns a list composed by an array of object[2]
where object[0] = person id and object[1] is the phone id

Both cases, causes the subquery to fail. Subquery is expecting a list of single valued objects. Both A and B cases return a list of multiple valued objects.

All I want to retrieve is max(Phone.Id) grouped by person.

I'm thinkering with this for a couple of hours, but so far now I could'n achieve the desired result.

Thanks for your help.

Cheers,

rhossi

wolli · **Posted:** Mon Feb 25, 2008 3:59 am

You could modify your select to a correlated subquery instead of the groub by:

Code:

select p.Name, ph.Number from People p 
inner join Phones ph on ph.PersonId = p.Id 
where ph.Id in (select max(Id) from Phones where Personid  = p.Id)

which yould result in a subquery like this:

Code:

DetachedCriteria lastPhones = DetachedCriteria.For(typeof(Phone), "ph") 
            .Add( Expression.EqProperty( "ph.PersonId", "p.Id" ) )
            .Add( Projections.Max("Id") ) ); 

... rest should stay the same.

Hope that helps !

rhossi · **Joined:** Fri Feb 22, 2008 9:40 am **Posts:** 7

We're almost there... following your suggestions

I come up with the following criteria

Code:

         IList test = session.CreateCriteria(typeof(Participant), "p")
            .CreateCriteria("p.DeliveryTickets", "dt", JoinType.LeftOuterJoin)
            .CreateCriteria("p.DeliveryTickets.TemplateEmail", "te", JoinType.LeftOuterJoin)
            .CreateCriteria("p.SurveyVersion", "sv", JoinType.LeftOuterJoin)
            .CreateCriteria("p.Language", "lang", JoinType.InnerJoin)
            .CreateCriteria("p.Country", "c", JoinType.InnerJoin)
            .SetProjection(Projections.ProjectionList()
                        .Add(Projections.Property("p.Id"))
                        .Add(Projections.Property("p.Email"))
                        .Add(Projections.Property("sv.Name"))
                        .Add(Projections.Property("c.Name"))
                        .Add(Projections.Property("lang.Name"))
                        .Add(Projections.Property("dt.TicketStatus"))
                        .Add(Projections.Property("dt.TemplateEmail")))
            .Add(Expression.Conjunction()
                  .Add(Expression.Eq("p.IsOnline", true))
                  .Add(Expression.IsNotNull("p.Email"))
                  .Add(Expression.Eq("p.Survey.Id", surveyId))
                  .Add(Expression.Disjunction()
                        .Add(Expression.IsNull("dt.Id"))
                        .Add(Subqueries.PropertyEq("dt.Id", lastParticipantTicket))))
            .List();

Almost perfect! The criteria executes successfully but, not as expected.
Seems NHibernate is not respecting the JoinType I specify on my criterias.
From the criteria above, NHibernate generates the following SQL

Code:

SELECT 
   this_.Id as y0_
   , this_.Email as y1_
   , sv5_.Name as y2_
   , c4_.Name as y3_
   , lang3_.Name as y4_
   , dt1_.status as y5_
   , te2_.TemplateEmailKey as y6_ 
FROM Participant this_ 
inner join Country c4_ on this_.country_id=c4_.Id 
inner join SurveyVersion sv5_ on this_.survey_version_id=sv5_.Id 
inner join Language lang3_ on this_.language_id=lang3_.Id 
left outer join DeliveryTicket dt1_ on this_.Id=dt1_.part_id 
inner join TemplateEmail te2_ on dt1_.template_id=te2_.Id
WHERE (this_.is_online = @p0 and this_.Email is not null and this_.survey_id = @p1 and (this_.Id is null or dt1_.Id = (SELECT max(this_0_.Id) as y0_ FROM DeliveryTicket this_0_ WHERE this_0_.part_id = this_.Id))) ORDER BY dt1_.id DESC

But, the generated SQL should be something like this

Code:

SELECT 
   this_.Id as y0_
   , this_.Email as y1_
   , sv5_.Name as y2_
   , c4_.Name as y3_
   , lang3_.Name as y4_
   , dt1_.status as y5_
   , te2_.TemplateEmailKey as y6_ 
FROM Participant this_ 
inner join Country c4_ on this_.country_id=c4_.Id 
inner join Language lang3_ on this_.language_id=lang3_.Id 
left join SurveyVersion sv5_ on this_.survey_version_id=sv5_.Id 
left join DeliveryTicket dt1_ on this_.Id=dt1_.part_id 
left join TemplateEmail te2_ on dt1_.template_id=te2_.Id
WHERE (this_.is_online = @p0 
   and this_.Email is not null 
   and this_.survey_id = @p1
   and (dt1_.Id = (SELECT max(this_0_.Id) as y0_ FROM DeliveryTicket this_0_ WHERE this_0_.part_id = this_.Id)
   or dt1_.Id is null))
order by dt1_.[id]

Cheers,

Felipe Garcia

merge_s.rottem · **Joined:** Tue Aug 23, 2005 5:52 am **Posts:** 335

What version are you running? From memory there was a problem with joins not being respected in 1.2.0, but I could be wrong...

Symon.

rhossi · **Joined:** Fri Feb 22, 2008 9:40 am **Posts:** 7

merge_s.rottem wrote:

What version are you running?

Hmm... I'm running version 1.2

And you memory didn't fail. In fact it seems pretty good...
http://sourceforge.net/project/shownote ... p_id=73818

I appreciate your help and time. thanks.