SQL for humans ?Depending on your domain-model you may have some surprise using NH...
This is an HQL from Animal a inner join a.mother as m with m.bodyWeight < :someLimit Now try to read de corresponding SQL: http://fabiomaulo.blogspot.com/2009/05/nhibernate-210-hql-with-clause.html 2009/7/17 Darius Damalakas <[email protected]> > > I have tested the new feature, the sql produces the expected results. > Thanks!! > > > There is one non a non-blocker issue, however. Please see the SQL that > i've got > > SELECT > count(*) as y0_, > extract(month from this_.OBJECT_CREATE_DATE) as y1_, > extract(year from this_.OBJECT_CREATE_DATE) as y2_ > FROM CRM_ORDER this_ > WHERE > extract(month from this_.OBJECT_CREATE_DATE) = 7 and > extract(year from this_.OBJECT_CREATE_DATE) = 2009 > GROUP BY > extract(month from this_.OBJECT_CREATE_DATE), > extract(year from this_.OBJECT_CREATE_DATE) > ORDER BY > extract(year from this_.OBJECT_CREATE_DATE) asc, > extract(month from this_.OBJECT_CREATE_DATE) asc; > > > > What is noticeable here is that extract parts are repeated four times, > which is still valid SQL. However, the main issue is the readability > of this SQL (as i firmly believe that the code and in turn SQL should > be written for humans to understand, not for computers). > > This query could be rewritten into this: > SELECT > count(*) as y0_, > extract(month from this_.OBJECT_CREATE_DATE) as y1_, > extract(year from this_.OBJECT_CREATE_DATE) as y2_ > FROM CRM_ORDER this_ > WHERE > extract(month from this_.OBJECT_CREATE_DATE) = 7 and > extract(year from this_.OBJECT_CREATE_DATE) = 2009 > GROUP BY > y1_, > y2_ > ORDER BY > y1_ asc, > y2_ asc; > > I actually was a little surprised that i could not use aliases in the > WHERE clause, however (i am using FireBird 2.1). > > Is this the kind of feature that is worth registering as enhancement > in Jira, or is it nor worth/ possible implementing this? > > > On 10 Lie, 09:31, Darius Damalakas <[email protected]> wrote: > > FYI, the issue is fixed!http://nhjira.koah.net/browse/NH-1877 > > > > Thanks > > > > On Jul 9, 9:51 am, Darius Damalakas <[email protected]> > > wrote: > > > > > Thank you. I filed a new ticket here: > http://nhjira.koah.net/browse/NH-1877 > > > > > If this could be done in two days (compared to two months), it would > > > be great. > > > We are adding chart support for our software, and we want them to be > > > as flexible as possible. So for us it is necessary to use ICriteria > > > API, and not HQL. > > > > > Also conserning the issue, thegroupby query could be writtent in two > > > ways. One is Fabio suggested way: > > > select month(ro.Something) from RisingOrder rogroupby month > > > (ro.Something) > > > > > Another could be with aliases, that is: > > > select month(ro.Something) as myMonth from RisingOrder rogroupby > > > myMonth > > > > > I am interested in either way, as they both solve my problem. > > > > > On Jul 8, 8:25 pm, Fabio Maulo <[email protected]> wrote: > > > > > > Darius if you need > > > > Projections.GroupBy(IProjectionprojection) > > > > add the JIRA as Improvement, Tuna will work on it. > > > > Thanks. > > > > > > 2009/7/8 Tuna Toksoz <[email protected]> > > > > > > > As you said before, one of the targets of NH is to provide DB > ignorance. > > > > > This was the reason why we wanted to have more functions in > dialects,I > > > > > believe, so that we don't enforce the user to use an MSSQL specific > > > > > function, for example. Having an overload that takes IProjection > would serve > > > > > this purpose. > > > > > > > My 2 cents. > > > > > > > Tuna Toksöz > > > > > Eternal sunshine of the open source mind. > > > > > > >http://devlicio.us/blogs/tuna_toksoz > > > > >http://tunatoksoz.com > > > > >http://twitter.com/tehlike > > > > > > > On Wed, Jul 8, 2009 at 8:13 PM, Fabio Maulo <[email protected]> > wrote: > > > > > > >> .SetProjection(Projections.SqlGroupProjection("min({alias}.Name) > as > > > > >> FirstName", "datepart(mm, {alias}.BirthDate)", > > > > >> new[] { "FirstName" }, new[] { NHibernateUtil.String }) ) > > > > > > >> 2009/7/8 Tuna Toksoz <[email protected]> > > > > > > >>> This is not impossible to implement, is it? > > > > > > >>> select month(ro.Something) from RisingOrder rogroupby month( > > > > >>> ro.Something) > > > > > > >>> It is both ingroupby and in select, so it is a matter of creating > an > > > > >>> overload for both the constructor and Projections.GroupBy thing. > > > > > > >>> I still don't understand why you said this. > > > > > > >>> Tuna Toksöz > > > > >>> Eternal sunshine of the open source mind. > > > > > > >>>http://devlicio.us/blogs/tuna_toksoz > > > > >>>http://tunatoksoz.com > > > > >>>http://twitter.com/tehlike > > > > > > >>> On Wed, Jul 8, 2009 at 7:28 PM, Fabio Maulo < > [email protected]>wrote: > > > > > > >>>> 2009/7/8 Tuna Toksoz <[email protected]> > > > > > > >>>>> If you file a jira report, I will implement this in 2 days. > > > > > > >>>> That is because you have studied it and you know that it > > > > >>>> is impossible with the actual implementation, right ? > > > > >>>> Or is because create a JIRA ticket is similar to a sport ? > > > > > > >>>> -- > > > > >>>> Fabio Maulo > > > > > > >> -- > > > > >> Fabio Maulo > > > > > > -- > > > > Fabio Maulo > > > -- Fabio Maulo --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
