休眠和“禁止进入"状态在汇总子查询中 [英] nhibernate and "NOT IN" in a aggregate subquery
问题描述
我有一个实体提醒,其中包含 ReminderSchedule 的集合. 这是我的映射:
I've got an entity Reminder which contains a collection of ReminderSchedule. This is my mapping:
<class xmlns="urn:nhibernate-mapping-2.2" mutable="true" name="Reminder" table="Reminders">
<id name="Code" type="System.Guid">
<column name="ReminderCode" />
<generator class="guid.comb" />
</id>
...
<set access="field.pascalcase-underscore" cascade="all-delete-orphan" inverse="true" lazy="false" name="Schedules" mutable="true">
<key foreign-key="FK_Schedules_Reminders">
<column name="ReminderCode" />
</key>
<one-to-many class="ReminderSchedule" />
</set>
</class>
这是实体 ReminderSchedule 的映射:
<class xmlns="urn:nhibernate-mapping-2.2" mutable="true" name="ReminderSchedule" table="ReminderSchedules">
<id name="Code" type="System.Guid">
<column name="ReminderScheduleCode" />
<generator class="guid.comb" />
</id>
<property name="NextSchedule" type="System.DateTime">
<column name="NextSchedule" index="ReminderScheduleK01" not-null="true" />
</property>
<many-to-one class="Reminder" foreign-key="FK_ReminderScheduleToReminder" name="Reminder">
<column name="ReminderCode" index="ReminderScheduleK02" not-null="true" />
</many-to-one>
</class>
ReminderSchedules表包含与提醒关联的每个新计划的记录.
我可以关闭一个计划(已关闭= 1)并重新计划一个新计划.
在这种情况下,我将拥有Closed = 0的新记录,并且字段NextSchedule将包含下一个计划的日期/时间.
ReminderSchedules table contains a record for each new scedule associated with a reminder.
I can close a schedule (Closed = 1) and reschedule a new one.
In this situation I would have a new records with Closed = 0 and I the field NextSchedule would contain the date/time of the next schedule.
我已经在使用 CreateCriteria 来过滤提醒,并且效果很好. 现在,我想获取未打开任何ReminderSchedule的提醒.
I am already using CreateCriteria to filter the reminders and it works pretty very well. Now I would like to fetch the reminders which do not have any ReminderSchedule open.
我想出了如何通过查询来做到这一点:
I've figured out how to do it with a query:
SELECT * FROM Reminders
WHERE ReminderCode
NOT IN (
SELECT LastReschedule.ReminderCode FROM (
SELECT ReminderCode, MAX(NextSchedule) MaxSchedule
FROM ReminderSchedules
WHERE Closed = 1
GROUP BY ReminderCode) LastReschedule
)
ORDER BY Reminders.ReminderCode
但是我不知道如何按照对nhibernate有效的标准来翻译它.
but I don't know how to translate it in a criteria valid for nhibernate.
有没有人可以帮助我?会非常感激的.
Is there anybody out there who can help me? It would be really appreciated.
推荐答案
由于无法使用2个子查询(嵌套),因此我尝试仅使用一个子查询来简化该查询.
我已获取关闭时间表的提醒的ID(Schedules.Reminder.Code).对我来说幸运的是,如果之前的时间表没有关闭,我就不需要另外的时间表.如果发生这种情况,那就太糟糕了,我将无法进行管理.
Since I haven't been able to use 2 subqueries (nested) I've tried to simplify using just one subquery.
I've fetched the id of the reminder (Schedules.Reminder.Code) for the schedules closed. Luckily for me I shouldn't have another schedule if the previous one hasn't been closed. If it happens, too bad, I won't be able to manage it.
这是我能做的最好的事情
This is the best I could do:
ICriteria FiltersCriteria = Session.CreateCriteria<Domain.Reminder>("Reminders");
DetachedCriteria dCriteria = DetachedCriteria.For<Domain.ReminderSchedule>("Schedules")
.SetProjection(Projections.ProjectionList()
.Add(Projections.GroupProperty("Schedules.Reminder.Code")))
// .Add(Projections.Max("Schedules.NextSchedule").As("MaxSchedule")))
.Add(Restrictions.Eq("Schedules.Closed", true));
FiltersCriteria.Add(Subqueries.PropertyIn("Reminders.Code", dCriteria));
我想这个示例代码是不言自明的.
我为子查询使用了 DetachedCriteria ,并将其用作我的 Criteria 的参数.
I guess this sample code is self explanatory.
I've used a DetachedCriteria for the subquery and I use it as a parameter for my Criteria.
这篇关于休眠和“禁止进入"状态在汇总子查询中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!