Having clause in Criteria

[cws]

Hello!

Trying to create a having cluse with the criteria interface after
reading this http://nhjira.koah.net/browse/NH-1280.

I want to extend the having cluse using ISqlFunctions:
ISQLFunction arithmaticAddition = new VarArgsSQLFunction("(",
"+", ")");
ISQLFunction arithmaticSubstract = new VarArgsSQLFunction("(",
"-", ")");

to generate a having clause like this:

having dbo.Hour((max(Time + dbo.Minute(Length)) - min(Time))) > 7) as
lectureInner

Maybe I am doing wrong but seems to be a problem when nesting
projections.

I have created a relevant testcase, according to the NhForge HowTo.

See code and mappings below.

Regards
Cws

Code:
PropertyProjection length = Projections.GroupProperty("Length");

IProjection dateOnly = Projections.SqlFunction
("DateOnly", NHibernateUtil.DateTime, time);
IProjection inner1 = Projections.Max
(Projections.SqlFunction(arithmaticAddition, NHibernateUtil.DateTime,
time, length));
IProjection inner2 = Projections.Min(time);
IProjection inner = Projections.SqlFunction
(arithmaticSubstract, NHibernateUtil.DateTime, inner1, inner2);
IProjection hour = Projections.SqlFunction("Hour",
NHibernateUtil.Int32, inner);

ICriteria criteria = session.CreateCriteria(typeof
(Lecture))
.SetProjection(dateOnly)
.Add(Restrictions.Gt(hour, 7));
IList list2 = criteria.List();
Mappings:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="NHibernate.Test"
namespace="NHibernate.Test.NHSpecificTest.NHCriteriaHaving">
<class name="Lecture" table="Lecture" dynamic-insert="true"
dynamic-update="true" >
<id name="ID" column="id" type="Int32" >
<generator class="hilo"/>
</id>
<property name="Time" column="time" type="DateTime" not-
null="false" />
<property name="Length" column="length" type="Int32" not-
null="false" />
</class>

<database-object>
<create>
create function [dbo].[DateOnly](@DateTime DateTime)
-- Returns @DateTime at midnight; i.e., it removes the time
portion of a DateTime value.
returns datetime
as
begin
return dateadd(dd,0, datediff(dd,0,@DateTime))
end
</create>
<drop>
DROP FUNCTION [dbo].[DateOnly]
</drop>
<dialect-scope name="NHibernate.Dialect.MsSql2000Dialect"/>
<dialect-scope name="NHibernate.Dialect.MsSql2005Dialect"/>
<dialect-scope name="NHibernate.Dialect.MsSql2008Dialect"/>
</database-object>

<database-object>

<create>
create function [dbo].[Date](@Year int, @Month int, @Day int)
-- returns a datetime value for the specified year, month and
day
-- Thank you to Michael Valentine Jones for this formula (see
comments).
returns datetime
as
begin
return dateadd(month,((@Year-1900)*12)+@Month-1,@Day-1)
end
</create>
<drop>
DROP FUNCTION [dbo].[Date]
</drop>
<dialect-scope name="NHibernate.Dialect.MsSql2000Dialect"/>
<dialect-scope name="NHibernate.Dialect.MsSql2005Dialect"/>
<dialect-scope name="NHibernate.Dialect.MsSql2008Dialect"/>
</database-object>

<database-object>

<create>
create function [dbo].[Time](@Hour int, @Minute int, @Second
int)
-- Returns a datetime value for the specified time at the "base"
date (1/1/1900)
-- Many thanks to MVJ for providing this formula (see comments).
returns datetime
as
begin
return dateadd(ss,(@Hour*3600)+(@Minute*60)+@Second,0)
end
</create>
<drop>
DROP FUNCTION [dbo].[Time]
</drop>
<dialect-scope name="NHibernate.Dialect.MsSql2000Dialect"/>
<dialect-scope name="NHibernate.Dialect.MsSql2005Dialect"/>
<dialect-scope name="NHibernate.Dialect.MsSql2008Dialect"/>
</database-object>

<database-object>

<create>
create function [dbo].[NextDay](@DateTime DateTime)
-- Returns a datetime + 1 day from
returns datetime
as
begin
return dateadd(dd, 1, @DateTime)
end
</create>
<drop>
DROP FUNCTION [dbo].[NextDay]
</drop>
<dialect-scope name="NHibernate.Dialect.MsSql2000Dialect"/>
<dialect-scope name="NHibernate.Dialect.MsSql2005Dialect"/>
<dialect-scope name="NHibernate.Dialect.MsSql2008Dialect"/>
</database-object>
<database-object>

<create>
create function [dbo].[TimeOnly](@DateTime DateTime)
-- returns only the time portion of a DateTime, at the "base"
date (1/1/1900)
-- Thanks, Peso!
returns datetime
as
begin
return dateadd(day, -datediff(day, 0, @datetime), @datetime)
end
</create>
<drop>
DROP FUNCTION [dbo].[TimeOnly]
</drop>
<dialect-scope name="NHibernate.Dialect.MsSql2000Dialect"/>
<dialect-scope name="NHibernate.Dialect.MsSql2005Dialect"/>
<dialect-scope name="NHibernate.Dialect.MsSql2008Dialect"/>
</database-object>

<database-object>

<create>

create function [dbo].[DateTime](@Year int, @Month int, @Day
int, @Hour int, @Minute int, @Second int)
-- returns a dateTime value for the date and time specified.
returns datetime
as
begin
return dbo.Date(@Year,@Month,@Day) + dbo.Time(@Hour,
@Minute,@Second)
end
</create>
<drop>
DROP FUNCTION [dbo].[DateTime]
</drop>
<dialect-scope name="NHibernate.Dialect.MsSql2000Dialect"/>
<dialect-scope name="NHibernate.Dialect.MsSql2005Dialect"/>
<dialect-scope name="NHibernate.Dialect.MsSql2008Dialect"/>
</database-object>

<database-object>

<create>

create function [dbo].[Hour](@DateTime DateTime)
-- returns a dateTime value for the date and time specified.
returns int
as
begin
return DatePart(Hour, @DateTime)
end
</create>
<drop>
DROP FUNCTION [dbo].[Hour]
</drop>
<dialect-scope name="NHibernate.Dialect.MsSql2000Dialect"/>
<dialect-scope name="NHibernate.Dialect.MsSql2005Dialect"/>
<dialect-scope name="NHibernate.Dialect.MsSql2008Dialect"/>
</database-object>

<database-object>

<create>

create function [dbo].[Minute](@DateTime DateTime)
-- returns a dateTime value for the date and time specified.
returns int
as
begin
return DatePart(Minute, @DateTime)
end
</create>
<drop>
DROP FUNCTION [dbo].[Minute]
</drop>
<dialect-scope name="NHibernate.Dialect.MsSql2000Dialect"/>
<dialect-scope name="NHibernate.Dialect.MsSql2005Dialect"/>
<dialect-scope name="NHibernate.Dialect.MsSql2008Dialect"/>
</database-object>




<!-- Oracle need this stats only -->
<database-object>
<create>
CREATE TABLE [dbo].[hibernate_unique_key](
[next_hi] [int] NULL
) ON [PRIMARY]
</create>
<!--<drop>
DROP STATISTICS PeopleByCityAndLastName
</drop>-->
<drop>

</drop>
<dialect-scope name="NHibernate.Dialect.SQLiteDialect"/>
</database-object>

</hibernate-mapping>

[Fabio Maulo]

for Date only the right type, to use in the mapping, is "Date".

For datetime management you have some builtin HQL functions.

2009/6/15 cws <cw.st...@gmail.com>

--
Fabio Maulo

[cws]

Fabio, thank you for answering. Sorry about those mappings. It really
took the fun away :).

As I mentioned above, I have also prepared a testcase for this.
http://nhforge.org/cfs-file.ashx/__key/CommunityServer.Components.UserFiles/00.00.00.26.00/NHibernate.Test.zip

The question is anyway not about mappings, but how I could get a
criteria to generate a having clause like this:

select dbo.DateOnly(Time) as date
From lecture
where Time between :StartDate and :EndDate
group by dbo.DateOnly(Time)

having dbo.Hour((max(Time + dbo.Minute(Length)) - min(Time))) > 7

According to http://nhjira.koah.net/browse/NH-1280 it seems like it
should be possible.

Using this code, the having clause is not generated as it should be
accordnig to the issue-link above.

PropertyProjection length = Projections.GroupProperty("Length");

IProjection dateOnly = Projections.SqlFunction("DateOnly",

NHibernateUtil.DateTime, time);
IProjection inner1 = Projections.Max(Projections.SqlFunction

(arithmaticAddition, NHibernateUtil.DateTime,time, length));

IProjection inner2 = Projections.Min(time);
IProjection inner = Projections.SqlFunction(arithmaticSubstract,
NHibernateUtil.DateTime, inner1, inner2);
IProjection hour = Projections.SqlFunction
("Hour",NHibernateUtil.Int32, inner);

ICriteria criteria = session.CreateCriteria(typeof(Lecture))
.SetProjection(dateOnly)
.Add(Restrictions.Gt(hour, 7));
IList list2 = criteria.List();

On 15 Juni, 17:02, Fabio Maulo <fabioma...@gmail.com> wrote:
> for Date only the right type, to use in the mapping, is "Date".For datetime

> management you have some builtin HQL functions.
>

> 2009/6/15 cws <cw.stenb...@gmail.com>

>
>
>
>
>
> > Hello!
>
> > Trying to create a having cluse with the criteria interface after

> > reading thishttp://nhjira.koah.net/browse/NH-1280.

[Fabio Maulo]

2009/6/15 cws <cw.st...@gmail.com>

select dbo.DateOnly(Time) as date
From lecture
where Time between :StartDate and :EndDate
group by dbo.DateOnly(Time)

having dbo.Hour((max(Time + dbo.Minute(Length)) - min(Time))) > 7

well.. write that query with Criteria is like a PITA basically because Criteria API are more Entity oriented than projection oriented.

Registering all your own function in your own Dialect (hinetited from MsSQL2005Dialect) the HQL should look like

select  DateOnly(l.Time) as date
from lecture l
where l.Time between :StartDate and :EndDate
group by DateOnly(l.Time)

having hour((max(l.Time + minute(l.Length)) - min(l.Time))) > 7

note "hour" and "minute" are buildin function (builtin mean multi-RDBMS).

--
Fabio Maulo

[cws]

Ok, I now see the benefit of extending the dialect, thank you for
that..

Btw, my attention was to use this special query as a subquery to make
a outer criteria to fetch all lectures satisfying the subquery. But I
guess its hard do that with criteria and I should use hql instead.

On 15 Juni, 22:38, Fabio Maulo <fabioma...@gmail.com> wrote:
> 2009/6/15 cws <cw.stenb...@gmail.com>

[Fabio Maulo]

Not so hard to write... only long to write and perhaps hard to understand after write it.

2009/6/15 cws <cw.st...@gmail.com>

--
Fabio Maulo

[cws]

Hello!

Fabio,
I am really curious how this could be written with criteria api.
Could you perhaps point me in the right direction from where I am now
(the provided code)?

On 15 Juni, 23:41, Fabio Maulo <fabioma...@gmail.com> wrote:
> Not so hard to write... only long to write and perhaps hard to understand
> after write it.
>

> 2009/6/15 cws <cw.stenb...@gmail.com>