Group By and Having with ICriteria

[Woil]

I have a VERY simple query that I want to write with Criteria.

Select Max(ID), Name from People Group By People Having COUNT(Name) >
1

This query provides a list of the newest rows that have duplicate
names.

I haven't found any way to create the HAVING part of the query with
the criteria interface. I've looked in both the docs and the test
files of the source. Are these queries even supported?

[Ayende Rahien]

Oh the top of my head, I don't think that the criteria has HAVING support.
But I would be very interested to see a patch for it.

[Woil]

Seems like it will be necessary for the implementation of Linq to
NHibernate.

I spent some time digging in the source for this... and am a bit
overwhelmed by how huge the Criteria code is. Any clues on where to
start?

I'm assuming this is also not supported in the Java version... (I
searched online a bunch for this as well.)

> > files of the source. Are these queries even supported?- Hide quoted text -
>
> - Show quoted text -

[Ayende Rahien]

Yeah, it is probably needed for Linq.
I haven't tried that, and I can't recall ever seeing that, so I am not sure.
Check the CriteriaJoinWalker, which generates the SQL from ICriteria.

[Woil]

This looks to be a MASSIVE undertaking as classes all the way down to
the SqlSelectBuilder don't support the Having clause. I could work on
this, but only know MSSQL well, and the other dialects.

> > > - Show quoted text -- Hide quoted text -

[Ayende Rahien]

That is why I asked for a patch :-)
HAVING is standard across most DB, IIRC, so you wouldn't have to worry about that.

[Woil]

WOW, there are some pretty major bugs in this part of the framework.
Here's my favorite so far:

The query parameters get way out of order when specifying a
combination of standard criteria and projection criteria. For example
take this query:

string formalname = s.CreateCriteria(typeof(Student))
.SetProjection(new SqlFunctionProjection("concat",
NHibernateUtil.String,
new ConstantProjection("Mr. "), Projections.Property("Name")))
.Add(Restrictions.Gt("id", 2L)).UniqueResult<string>();

Seems simple enough. We're adding 'Mr. ' to all names with an ID
greater than 2. Here's what the unmodified source returns.

NHibernate: INSERT INTO Student (Name, preferredCourseCode, studentId)
VALUES (@p0, @p1, @p2); @p0 = 'ayende', @p1 = '', @p2 = '27'
NHibernate: SELECT (@p0+this_.Name) as y0_ FROM Student this_ WHERE
this_.studentId > @p1; @p0 = '2', @p1 = 'Mr. '

Ouch. Looks like the parameter order is specified in
CriteriaQueryTranslator.GetQueryParameters() as standard criteria
first then projection criteria. I'm going to try and fix this along
with my group by stuff. Sure hope that it ends up getting used, I'd
hate to go to all this trouble just to have it dumped because it
doesn't match the Java version.... which this particular function
doesn't match already, I checked.

-Will

[Ayende Rahien]

Woil,
A) If you can, make it separate patches.
B) This is very likely to be included.
C) We aren't bound to the Java version

[Ayende Rahien]

D) Good catch

[Dario Quintana]

http://opensource.atlassian.com/projects/hibernate/browse/HHH-1043

--
Dario Quintana
http://darioquintana.com.ar

[Woil]

Thanks Dario. I'm still working on this patch for NHibernate. Making
progress, but there are deeper problems with the order of parameters
which get worse when adding in Having clauses. Hopefully I'll be done
soon.

On Apr 2, 8:04 am, "Dario Quintana" <conta...@darioquintana.com.ar>
wrote:

> Dario Quintanahttp://darioquintana.com.ar- Hide quoted text -

[Dario Quintana]

Hi,

Maybe make a checkout of Hibernate, apply the patch and then *port* the feature could be more easier. And is better (not always) make a port instead make a feature from zero, because then is more easier for the commiters make anothers ports around of that code. By the way, the patch was not applied yet at Hibernate trunk.

Best regards and thanks for contribute

On Wed, Apr 2, 2008 at 12:39 PM, Woil <will....@gmail.com> wrote:

Thanks Dario. I'm still working on this patch for NHibernate. Making
progress, but there are deeper problems with the order of parameters
which get worse when adding in Having clauses. Hopefully I'll be done
soon.

[Woil]

It looks like from reviewing the patch that it wasn't accepted because
it didn't work. I think I have mine working and am spending some time
testing before I submit it.

On Apr 2, 9:23 am, "Dario Quintana" <conta...@darioquintana.com.ar>
wrote:

> Hi,
>
> Maybe make a checkout of Hibernate, apply the patch and then *port* the
> feature could be more easier. And is better (not always) make a port instead
> make a feature from zero, because then is more easier for the commiters make
> anothers ports around of that code. By the way, the patch was not applied
> yet at Hibernate trunk.
>
> Best regards and thanks for contribute
>

[Dario Quintana]

Ok, great ;)

On Wed, Apr 2, 2008 at 5:55 PM, Woil <will....@gmail.com> wrote:

I think I have mine working and am spending some time
testing before I submit it.

[Woil]

The patch for this has been posted.
http://jira.nhibernate.org:8080/jira/browse/NH-1280

[Dario Quintana]

Let us to make a review.
Thanks!

On Wed, Apr 2, 2008 at 7:44 PM, Woil <will....@gmail.com> wrote:

The patch for this has been posted.
http://jira.nhibernate.org:8080/jira/browse/NH-1280

[Ayende Rahien]

Woil,
That was a pleasure to read, applied.
Many thanks.

[Fabio Maulo]

2008/4/2, Ayende Rahien <aye...@ayende.com>:

Woil,
That was a pleasure to read, applied.
Many thanks.

The issue NH-1280 ?
--
Fabio Maulo

[Ayende Rahien]

Yes

[Fabio Maulo]

2008/4/3, Ayende Rahien <aye...@ayende.com>:

Yes

:D .... I mean : the state of JIRA issue NH-1280 ?
--
Fabio Maulo

[Ayende Rahien]

Oh, that.
Yes, it needs to be closed, but I can't log it at the moment, and the JIRA forgot your pass doesn't work.

[Fabio Maulo]

2008/4/3, Ayende Rahien <aye...@ayende.com>:

Oh, that.
Yes, it needs to be closed, but I can't log it at the moment, and the JIRA forgot your pass doesn't work.

BTW we need NH2.1.0Alpha1 version to fix something in the trunk not present in NH2.0.x branch.

Fabio Maulo

[Woil]

I wasn't sure if people would want to include this in the 2.1 version
as it includes what some may feel is a new "feature". (I feel it is
actually a bug fix.) I can work on porting it over.

-Will

[Fabio Maulo]

2008/4/3, Woil <will....@gmail.com>:

I wasn't sure if people would want to include this in the 2.1 version
as it includes what some may feel is a new "feature". (I feel it is
actually a bug fix.) I can work on porting it over.

 

Not only a new feature.... Hibernate (our big brother) don't support it so... is more than a new feature.

--
Fabio Maulo