NHibernate的 - 许多使用结/木表中的许多查询 [英] NHibernate - Many to Many Query using Junction/Joiner Table
问题描述
我发现在这里非常类似的问题，但没有匹配的正是我要找的那个。我发现的最接近的两个线程都（是的，他们是不同的线程）：
不过，我觉得这两个都是直接使用许多一对多的关系。我实际上有两个带接线表，这是pretty标准的做法一对多关系模拟多对一一对多的关系。这里是我的NHibernate映射：
文件：
＆LT;类名=文件表=文件＆GT;
＆LT; ID名称=ID＆GT;
＆LT;生成器类=身份/＆GT;
＆LT; / ID＆GT;
＆LT;属性名=名/＆GT; ＆LT;包名称=files_attrs表=files_attrs懒=真＆GT;
＆LT;键列=FILE_ID/＆GT;
＆下;一对许多类=Files_Attrs/＆GT;
＆LT; /袋＆GT;
＆LT; /班＆GT;
属性：
＆LT;类名=ATTRS表=ATTRS＆GT;
＆LT; ID名称=ID＆GT;
＆LT;生成器类=身份/＆GT;
＆LT; / ID＆GT;
＆LT;属性名=名/＆GT;
＆LT;属性名=值/＆GT; ＆LT;包名称=files_attrs表=files_attrs懒=真＆GT;
＆LT;键列=attr_id/＆GT;
＆下;一对许多类=Files_Attrs/＆GT;
＆LT; /袋＆GT;
＆LT; /班＆GT;
木：
＆LT;类名=Files_Attrs表=files_attrs＆GT;
＆LT; ID名称=ID＆GT;
＆LT;生成器类=身份/＆GT;
＆LT; / ID＆GT;
＆LT;多到一个名称=文件级联=所有栏=FILE_ID/＆GT;
＆LT;多到一个名称=ATTR级联=所有栏=attr_id/＆GT;
＆LT; /班＆GT;
所以我的问题是，酷似上面的第二个链接，但结合表完成。所以：
给定一组属性的ID，我希望运行一个查询，让我拥有所有这些属性相匹配的文件。我可以轻松地运行N查询的集合中的每个属性ID，并比较各列表出现在每一个列表中的文件的ID，但我觉得应该有一个更简单的方法来使用一个查询一次全部做到这一点。
例如：
文件|属性
---------- + --------------------------------------- --------------
foo.txt的| （模式=只读视图=可见）
跳回到bar.txt | （模式=读写，安全= ALL，查看=可见）
duck.txt | （模式=只读，查看=隐藏）
goose.txt | （更多=只读，安全=所有者，查看=可见）
由于这些属性：模式=只读和查看=可见，我想只返回 foo.txt的和 goose.txt 。
谁能帮助我？谢谢你。
的一种方法如何实现这一点，可以创建由和加入尽可能多的子查询，多属性必须找到/相关搜索文件
我寻找的名称/值
的第一溶液可与名称/值对，从上层。以只读即用户选择的模式... （第二将是一个更容易一点，希望我们已经拥有了搜索Atttributes的ID的）的
//下面我使用C＃的属性，我的猜测是正确的
//基于所述映射。命名规则是多个Java（骆驼）
//但这应该与上面的映射工作
//（也 - 类名联系人，而不是文件）文件的文件= NULL; //这是以下使用的别名//此处的属性集合再presents搜索过滤器
// ...对于这些设置是用户寻找
VAR属性=新的List＆LT;＆ATTRS GT;
{
新ATTRS {NAME =模式，值=只读}，
新ATTRS {名=查看，值=看得见}
};//让我们开始与外部/前查询的定义
//将返回所有文件，里面做符合所有过滤器的要求
VAR的查询= session.QueryOver＆LT;文件＆GT;（（）=＆GT;文件）;
在下一步，我们将通过迭代的属性，即过滤器的集合
//这里，我们将采取每个属性，并创建一个子查询
//所有这些子查询，将与加盟和
//所以只有这些文件，它确实有所有属性，将被选中
的foreach（在属性VAR ATTR）
{
//创建子查询，返回FILEID
ATTRS属性= NULL;
VAR subQueryForAttribute = QueryOver.Of＆LT; Files_Attrs＆GT;（）
.JoinQueryOver（FA =＆GT; fa.attr，（）=＆GT;属性）
。选择（X =＆GT; x.file.id）
; //现在，只要名称和值
变量名称= attr.name;
VAR值= attr.value; //并转换他们到哪里条件
subQueryForAttribute.Where（（）=＆GT; attribute.name ==名）;
subQueryForAttribute.Where（（）=＆GT; attribute.value ==值）; //最后，作为限制添加此子查询到顶级查询
query.WithSubquery
.WhereProperty（（）=＆GT; file.id）
。在（subQueryForAttribute）;
}
现在我们有一个查询，这是准备支持分页 - 因为我们对文件的扁平结构的工作。因此，我们可以使用带，如果需要跳过，然后得到搜索到的文件列表
// query.Take（25）;
// query.Skip（100）;VAR列表= query.List＆LT;文件＆GT;（）;
这是一个查询，这将导致在SELECT这样
SELECT ...
从文件中
WHERE ID IN（SELECT FILE_ID FROM files_attrs
INNER JOIN ATTRS ON attrs.id = file_attrs.attr_id
其中name ='模式'和价值='只读'）
和ID IN（SELECT FILE_ID FROM files_attrs
INNER JOIN ATTRS ON attrs.id = file_attrs.attr_id
其中name ='查看'和Value =可见的）
II搜索按属性ID
第二个解决方案，具有更容易初始条件，而不是属性（名称和值），我们已经有了自己的ID（从问题引用：）
给定一组属性的ID，我希望运行一个查询，让我拥有所有这些属性相匹配的文件。
//下面我使用C＃的属性，我的猜测是正确的
//基于所述映射。命名规则是多个Java（骆驼）
//但这应该与上面的映射工作
//（也 - 类名的文件，而不是文件）文件的文件= NULL; //这是以下使用的别名//这里的attributeIds集合再presents属性被发现
VAR attributeIds =新的List＆LT; INT＆GT; {1，4,5};//让我们再次与外/顶查询定义开始
//将返回所有文件，里面做符合所有过滤器的要求
VAR的查询= session.QueryOver＆LT;文件＆GT;（（）=＆GT;文件）;接下来就是通过它必须存在的关系组已知ID的迭代的（全部）的
//这里，我们将采取每个属性，并创建一个子查询
//所有这些子查询，将与加盟和
//所以只有这些文件，它确实有所有属性，将被选中
的foreach（在attributeIds VAR attrID）利用
{
//创建子查询，返回Files.id
VAR subQueryForAttribute = QueryOver.Of＆LT; Files_Attrs＆GT;（）
//无需加入，所有的东西是配对表
。选择（X =＆GT; x.file.id）
;
VAR ID = attrId; //局部变量
//并转换他们到哪里条件
subQueryForAttribute.Where（双=＆GT; pair.attr.id == ID）; //最后，作为限制添加此子查询到顶级查询
query.WithSubquery
.WhereProperty（（）=＆GT; file.id）
。在（subQueryForAttribute）;
}VAR列表= query.List＆LT;文件＆GT;（）;与已知的IDS的解决方案是一个更容易一点的（需要在SQL statemenets少表）的
的注：不得不说：这是伟大的看，你已经引入了
多到一和单一对多而不是多到很多。我个人而言，说，正是这个例子说明，它的利润有多大可能带来...的能力甚至与复杂的过滤器搜索的一些链接，显示的功率
QueryOver：质疑的hasMany参考，以及一些很好的理由，为什么不使用多到许多映射：多到许多额外的列NHibernate的I've found very similar questions here but none that match exactly what I'm looking for. The two closest threads I've found are (yes, they are different threads):
NHibernate many-to-many criteria (1)
NHibernate many-to-many criteria (2)
However, I think both of those are using direct Many-to-Many relationships. I am actually simulating the Many-to-Many relationship by having two One-to-Many relationships with a junction table, which is pretty standard practice. Here are my NHibernate mappings:
Files:
<class name="Files" table="files"> <id name="id"> <generator class="identity" /> </id> <property name="name" /> <bag name="files_attrs" table="files_attrs" lazy="true"> <key column="file_id" /> <one-to-many class="Files_Attrs" /> </bag> </class>Attributes:
<class name="Attrs" table="attrs"> <id name="id"> <generator class="identity" /> </id> <property name="name" /> <property name="value" /> <bag name="files_attrs" table="files_attrs" lazy="true"> <key column="attr_id" /> <one-to-many class="Files_Attrs" /> </bag> </class>Joiner:
<class name="Files_Attrs" table="files_attrs"> <id name ="id"> <generator class="identity" /> </id> <many-to-one name="file" cascade="all" column="file_id" /> <many-to-one name="attr" cascade="all" column="attr_id" /> </class>So my problem is exactly like the second link above, but done with a Junction Table. So:
Given a set of Attribute IDs, I'm hoping to run a query that gives me the files that have ALL of those matching Attributes. I can easily run "n" queries for each Attribute ID in the set and compare each list for File IDs that appear in every list, but I feel like there should be an easier way to do this all at once with one query.
Example:
File | Attributes ----------+----------------------------------------------------- foo.txt | (mode = read-only, view = visible) bar.txt | (mode = read-write, security = all, view = visible) duck.txt | (mode = read-only, view = hidden) goose.txt | (more = read-only, security = owner, view = visible)Given these attributes:
mode = read-onlyandview = visible, I want to be returned onlyfoo.txtandgoose.txt.Can anyone help me with this? Thanks.
解决方案One way how to achieve this, could be to create as many subqueries joined by AND, as many attributes must be found/related to searched files
I searching for name / value
The first solution works with the name/value pairs, from upper layer. I.e user selected mode to be read-only... (the second will be a bit easier, expecting that we already have ID's of the searched Atttributes)
// Below I am using C# properties, which I guess are correct // based on the mapping. Naming convention is more Java (camel) // but this should work with above mapping // (also - class name Contact, not File) Files file = null; // this is an alias used below // here the attributes collection represents search filter // ... settings for which is user looking for var attributes = new List<Attrs> { new Attrs{ name = "mode", value = "read-only" }, new Attrs{ name = "view", value = "visible" } }; // Let's start with definition of the outer/top query // which will return all files, which do meet all filter requirements var query = session.QueryOver<Files>(() => file);In the next step, we will iterate through attributes, i.e. filters collection
// here we will take each attribute and create a subquery // all these subqueries, will be joined with AND // so only these files, which do have all attributes, will be selected foreach (var attr in attributes) { // create the subquery, returning the FileId Attrs attribute = null; var subQueryForAttribute = QueryOver.Of<Files_Attrs>() .JoinQueryOver(fa => fa.attr, () => attribute) .Select(x => x.file.id) ; // now, take name and value var name = attr.name; var value = attr.value; // and convert them into where condition subQueryForAttribute.Where(() => attribute.name == name); subQueryForAttribute.Where(() => attribute.value == value); // finally, add this subquery as a restriction to the top level query query.WithSubquery .WhereProperty(() => file.id) .In(subQueryForAttribute); }Now we have a query, which is ready to support paging - because we are working on a flat structure of files. So we can use Take and skip if needed and then get the list of searched files
// query.Take(25); // query.Skip(100); var list = query.List<Files>();This is a query which will result in a SELECT like this
SELECT ... FROM files WHERE id IN (SELECT file_Id FROM files_attrs INNER JOIN attrs ON attrs.id = file_attrs.attr_id WHERE name = 'mode' AND value = 'read-only' ) AND id IN (SELECT file_Id FROM files_attrs INNER JOIN attrs ON attrs.id = file_attrs.attr_id WHERE name = 'view' AND value = 'visible' )
II searching by attributes ID
The second solution, has easier starting conditions, instead of attributes (name and value) we already have their Ids (cite from a question:)
Given a set of Attribute IDs, I'm hoping to run a query that gives me the files that have ALL of those matching Attributes.
// Below I am using C# properties, which I guess are correct // based on the mapping. Naming convention is more Java (camel) // but this should work with above mapping // (also - class name Files, not File) Files file = null; // this is an alias used below // here the attributeIds collection represents attributes to be found var attributeIds = new List<int> { 1, 4, 5 }; // Let's again start with definition of the outer/top query // which will return all files, which do meet all filter requirements var query = session.QueryOver<Files>(() => file);Next is the iteration through the set of known IDs which must exist as relation (all of them)
// here we will take each attribute and create a subquery // all these subqueries, will be joined with AND // so only these files, which do have all attributes, will be selected foreach (var attrId in attributeIds) { // create the subquery, returning the Files.id var subQueryForAttribute = QueryOver.Of<Files_Attrs>() // no need to join, all the stuff is in the pairing table .Select(x => x.file.id) ; var id = attrId; // local variable // and convert them into where condition subQueryForAttribute.Where(pair => pair.attr.id == id); // finally, add this subquery as a restriction to the top level query query.WithSubquery .WhereProperty(() => file.id) .In(subQueryForAttribute); } var list = query.List<Files>();The solution with known IDS is a bit easier (less tables are needed in SQL statemenets)
NOTE: have to say: it is great to see, that you've introduced the
many-to-oneandone-to-manyinstead of many-to-many. I would, personally, say that exactly this example shows, how big profit it could bring... ability to search even with complex filtersSome links, to show the power of the
QueryOver: Query on HasMany reference , and some good reason why not to usemany-to-manymapping: many-to-many with extra columns nhibernate这篇关于NHibernate的 - 许多使用结/木表中的许多查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
