I finally got around to returning to the subject. things are moving in
the right direction, with one snag. say I create an empty interface
for querying
interface ISearchable
{
}
then I declare my criteria
session
.CreateCritiera(typeof(ISearchable))
.Add(Restrictions.Ge("ReivewDate", DateTime.Today)
.List()
this works right up until I apply the searchable interface to
DisposeStatus, which does not contain review date. I think I'm
approaching the problem wrong. i'm looking for one dynamic query to
get all records in one go. I know I can do it, because I can write the
sql.
select [fields]
from status this_
left outer join prepare_status prepare on this_.id=prepare.id
left outer join in_service_status in_service on
this_.id=in_service.id
left outer join repair_status repair on this_.id=repair.id
left outer join storage_status storage on this_.id=storage.id
left outer join dispose_status dispose on this_.id=dispose.id
left outer join in_service_status_employee employee on in_service.id
= employee.id
where this_.id in (select max(x.id) from status x where
this_.equipment_id = x.equipment_id)
and (
prepare.review_date between @p0 and @p1
or in_service.review_date between @p0 and @p1
or repair.review_date between @p0 and @p1
or storage.review_date between @p0 and @p1
)
and (
prepare.deploy_location_id in (@p2, @p3)
or in_service.deploy_location_id in (@p2, @p3)
)
and (
prepare.employee_id in (@p4, @p5)
or employee_employee_id in (@p4, @p5)
)
and (
case
when prepare.id is not null then 1
when in_service.id is not null then 2
when repair.id is not null then 3
when storage.id is not null then 4
when dispose.id is not null then 5
else 0 end in (@p6, @p7)
)
where each "and" predicate is optional. However I don't think NH can
create this.
On Oct 18, 2:33 pm, "Fabio Maulo" <[EMAIL PROTECTED]> wrote:
> 8.1.3http://nhforge.org/doc/nh/en/index.html#inheritance-tablepersubclass-...
>
> 2008/10/18 Jason Meckley <[EMAIL PROTECTED]>
>
>
>
>
>
> > Digging more into this. I have examined union-class and began looking
> > to simply join. Union-doesn't work as it messes with the current
> > identity and the queries don't look that effecient (no predicates on
> > the union-ized sub-queries) Just started looking into the join type.
> > I found this mentioned on ayende's blog. Is there any documentation on
> > this feature?
>
> > On Oct 16, 9:51 am, Jason Meckley <[EMAIL PROTECTED]> wrote:
> > > mappings:
> > > Status is abstract with 1:1 relations to other tables.
> > > Some of the tables have a property ReviewDate, others do not.
> > > in this snippet PrepareStatus and InServiceStatus have the ReviewDate
> > > property. The DisposeStatus does not.
> > > <hibernate-mapping>
> > > <class name="Status" abstract="true">
> > > <id name="Id">
> > > <generator class="identity" />
> > > </id>
> > > ...
> > > </class>
>
> > > <joined-subclass extends="Status" name="PrepareStatus">
> > > <key column="id" on-delete="cascade" />
> > > <property name="ReviewDate" />
> > > ...
> > > </joined-subclass>
>
> > > <joined-subclass extends="Status" name="InServiceStatus">
> > > <key column="id" on-delete="cascade" />
> > > <property name="ReviewDate" />
> > > ...
> > > </joined-subclass>
>
> > > <joined-subclass extends="Status" name="DisposeStatus">
> > > <key column="id" on-delete="cascade" />
> > > ...
> > > </joined-subclass>
> > > </hibernate-mapping>
>
> > > code:
> > > I want to return all statuses where ReviewDate is between 2 dates.
> > > DetachedCriteria criteria = Repository<AnInterfaceImplementedByStatus>
> > > .CreateDetachedCriteria()
> > > .Add(Restrictions.Between("ReviewDate", since, before));
>
> > > return Repository<AnInterfaceImplementedByStatus>.FindAll(criteria);
>
> > > Sql:
> > > however the sql produced looks like this
> > > select ...
> > > from Status s
> > > left join PrepareStatus p on s.Id = p.Id
> > > left join InServiceStatus i on s.Id = i.Id
> > > left join DisposeStatus d on s.Id = d.Id
> > > where p.ReviewDate between @p0 and @p1
>
> > > So only PreparedStatus records are returned.
> > > How would I alter my criteria to generate sql similar to?
> > > select ...
> > > from Status s
> > > left join PrepareStatus p on s.Id = p.Id
> > > left join InServiceStatus i on s.Id = i.Id
> > > left join DisposeStatus d on s.Id = d.Id
> > > where p.ReviewDate between @p0 and @p1
> > > or i.ReviewDate between @p0 and @p1
>
> > > I tried this:
> > > DetachedCriteria criteria = Repository<AnInterfaceImplementedByStatus>
> > > .CreateDetachedCriteria()
> > > .CreateAlias("PrepareStatus", "p")
> > > .CreateAlisa("InServiceStatus", "i")
> > > .Add(Restrictions.Between("p.ReviewDate", since, before) |
> > > Restrictions.Between("i.ReviewDate", since, before));
>
> > > return Repository<AnInterfaceImplementedByStatus>.FindAll(criteria);
>
> > > But NH throws an excpetion: Status does not have a property named
> > > PrepareStatus. Which makes sense since PrepareStatus is an
> > > inheritance, not a property.
>
> > > thank you in advance.
> > > Jason
>
> --
> 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
-~----------~----~----~----~------~----~------~--~---
