我有一个问题清单,每个挂标签的列表。
和以下数据:
问题1:Tag1中
问题2:Tag1中,与Tag2
问题3:Tag1中,与Tag2,TAG3
Question4:Tag1中,TAG3
以下标准:
VAR tagsIds =新INT [] {TAG1,TAG2};
VAR解析度= session.CreateCriteria<问题>()
.CreateCriteria(标签)
。新增(Restrictions.In(ID,tagsIds))
.LIST<问题>();
收益(我明白为什么,在中像一个或行为)
问题1,问题2,问题3,Question4
或者,我想只拿到
问题2,问题3
因为它们都具有标签1和标签2。 我有没有办法做到这一点?
在SQL中,我会做这样的事情:
SELECT *
从问题问
在存在(
选择 *
从QuestionsToTags QTT
WHERE qtt.Question_id = q.Id
与qtt.Tag_id IN(1,2)
GROUP BY qtt.Question_id
HAVING COUNT(qtt.Question_id)> = 2
)
解决方案
使用HQL:
变种Q = NHibernateSession.CreateQuery(
@的问题的问题
其中,存在(
从问题问选择q.id
加入q.Tags吨
哪里
t.id在(:IDS)
和q.id = question.id
按q.id
具有计数(t.id)=:c_count));
q.SetParameterList(IDS,tagIds);
q.SetInt32(c_count,tagIds.Length);
和使用的ICriteria:
//这里是存在的部分
VAR dCriteria = DetachedCriteria.For<问题>(Q)
.SetProjection(Projections.GroupProperty(Projections.Id()))
。新增(Restrictions.Eq(Projections.Count(Projections.Id()),tagIds.Length))
//这里我们对父母的标准筛选
。新增(Restrictions.EqProperty(q.id,question.Id))
.CreateCriteria(标签)
。新增(Restrictions.In(ID,tagIds));
VAR暴击= NHibernateSession
.CreateCriteria<问题>(问题)
。新增(Subqueries.Exists(dCriteria));
I have a list of questions, each linked to a list of tag.
And the following data :
Question1 : Tag1
Question2 : Tag1, Tag2
Question3 : Tag1, Tag2, Tag3
Question4 : Tag1, Tag3
The following criteria :
var tagsIds = new int[] { tag1, tag2 };
var res = session.CreateCriteria<Question>()
.CreateCriteria( "Tags" )
.Add( Restrictions.In( "id", tagsIds ) )
.List<Question>();
returns (I understand why, the "in" acts like an OR)
Question1, Question2, Question3, Question4
Or I would like to get only
Question2, Question3
as they both have tag1 AND tag2. I there a way to do it ?
In SQL, I would do something like :
SELECT *
FROM Question q
WHERE EXISTS (
SELECT *
FROM QuestionsToTags qtt
WHERE qtt.Question_id = q.Id
AND qtt.Tag_id IN ( 1, 2 )
GROUP BY qtt.Question_id
HAVING COUNT( qtt.Question_id ) >= 2
)
解决方案
Using hql :
var q = NHibernateSession.CreateQuery(
@"from Question question
where exists(
select q.id from Question q
join q.Tags t
where
t.id in (:ids)
and q.id = question.id
group by q.id
having count(t.id)=:c_count )");
q.SetParameterList("ids", tagIds);
q.SetInt32("c_count", tagIds.Length);
And using an ICriteria :
// here is the exists part
var dCriteria = DetachedCriteria.For<Question>("q")
.SetProjection(Projections.GroupProperty(Projections.Id()))
.Add(Restrictions.Eq(Projections.Count(Projections.Id()), tagIds.Length))
// here we filter on the "parent" criteria
.Add(Restrictions.EqProperty("q.id", "question.Id"))
.CreateCriteria("Tags")
.Add(Restrictions.In("id", tagIds));
var crit = NHibernateSession
.CreateCriteria<Question>("question")
.Add(Subqueries.Exists(dCriteria));
上一篇:为什么XslCompiledTransform添加META标记HTML输出?标记、XslCompiledTransform、META、HTML
下一篇:如何与QUOT;清理&QUOT; Microsoft.Office.Interop.Excel.WorkbookOffice、Microsoft、QUOT、Workbook