NHibernate - 使用 Junction/Joiner Table 的多对多查询 [英] NHibernate - Many to Many Query using Junction/Joiner Table

问题描述

我在这里发现了非常相似的问题，但没有一个与我正在寻找的完全匹配.我发现的两个最接近的线程是(是的，它们是不同的线程):

NHibernate 多对多标准(1)

NHibernate 多对多标准(2)

但是，我认为这两者都使用直接的多对多关系.我实际上是通过使用连接表建立两个一对多关系来模拟多对多关系，这是非常标准的做法.这是我的 NHibernate 映射:

文件:

<id name="id"><generator class="identity"/></id><属性名称=名称"/><包名称="files_attrs" table="files_attrs" lazy="true"><key column="file_id"/><一对多类="Files_Attrs"/></包></class>

属性:

<id name="id"><generator class="identity"/></id><属性名称=名称"/><属性名称=值"/><包名称="files_attrs" table="files_attrs" lazy="true"><key column="attr_id"/><一对多类="Files_Attrs"/></包></class>

加入者:

<id 名称 =id"><generator class="identity"/></id><多对一名称=文件"级联=所有"列=file_id"/><多对一名称="attr"cascade="all" column="attr_id"/></class>

所以我的问题与上面的第二个链接完全一样，但使用了一个连接表.所以:

给定一组属性 ID，我希望运行一个查询，为我提供具有所有匹配属性的文件.我可以轻松地对集合中的每个属性 ID 运行n"个查询，并比较每个列表中出现在每个列表中的文件 ID，但我觉得应该有一种更简单的方法可以通过一个查询一次完成所有操作.

示例:

文件 |属性----------+-------------------------------------------------------foo.txt |(模式 = 只读，视图 = 可见)bar.txt |(模式 = 读写，安全性 = 全部，视图 = 可见)鸭子.txt |(模式 = 只读，查看 = 隐藏)鹅.txt |(更多 = 只读，安全性 = 所有者，查看 = 可见)

鉴于这些属性:mode = read-only 和 view =visible，我只想返回 foo.txt 和 >goose.txt.

谁能帮我解决这个问题?谢谢.

解决方案

如何实现这一点的一种方法是创建尽可能多的由 AND 连接的子查询，尽可能多的属性必须找到/与搜索文件相关

我正在寻找名称/值

第一个解决方案适用于上层的名称/值对.即用户选择的模式为只读...(第二个会更容易一些，希望我们已经有了搜索到的属性的 ID)

//下面我使用的是 C# 属性，我猜是正确的//基于映射.命名约定更多的是Java(骆驼)//但这应该适用于上面的映射//(还有 - 类名联系人，而不是文件)文件文件=空；//这是下面使用的别名//这里的属性集合代表搜索过滤器//... 用户正在寻找的设置var attributes = new List{新属性{ name = "mode", value = "read-only" },新属性{ name = "view", value = "visible" }};//让我们从外部/顶部查询的定义开始//这将返回所有满足所有过滤要求的文件var query = session.QueryOver(() => file);

下一步，我们将遍历属性，即filters集合

//这里我们将获取每个属性并创建一个子查询//所有这些子查询，将与 AND 连接//所以只有这些具有所有属性的文件才会被选中foreach(属性中的var attr){//创建子查询，返回 FileIdAttrs 属性 = null;var subQueryForAttribute = QueryOver.Of().JoinQueryOver(fa => fa.attr, () => 属性).Select(x => x.file.id);//现在，获取名称和值var name = attr.name;var 值 = attr.value;//并将它们转换为 where 条件subQueryForAttribute.Where(() => attribute.name == name);subQueryForAttribute.Where(() => attribute.value == value);//最后，将此子查询添加为对顶级查询的限制query.WithSubquery.WhereProperty(() => file.id).In(subQueryForAttribute);}

现在我们有一个查询，它已准备好支持分页 - 因为我们正在处理文件的平面结构.所以我们可以在需要时使用 Take 和 skip ，然后获取搜索文件的列表

//query.Take(25);//query.Skip(100);var list = query.List<文件>();

这是一个查询，将导致像这样的 SELECT

SELECT ...来自文件WHERE id IN (SELECT file_Id FROM files_attrsINNER JOIN attrs ON attrs.id = file_attrs.attr_idWHERE name = 'mode' AND value = 'read-only' )AND id IN (SELECT file_Id FROM files_attrsINNER JOIN attrs ON attrs.id = file_attrs.attr_idWHERE name = 'view' AND value = 'visible' )

二按属性ID搜索

第二个解决方案，有更简单的启动条件，而不是属性(名称和值)，我们已经有了它们的 Id(从一个问题中引用:)

<块引用>

给定一组属性 ID，我希望运行一个查询，为我提供具有所有匹配属性的文件.

//下面我使用的是 C# 属性，我猜是正确的//基于映射.命名约定更多的是Java(骆驼)//但这应该适用于上面的映射//(也 - 类名文件，而不是文件)文件文件=空；//这是下面使用的别名//这里的attributeIds 集合代表要找到的属性var attributeIds = new List{ 1, 4, 5 };//让我们再次从外部/顶部查询的定义开始//这将返回所有满足所有过滤要求的文件var query = session.QueryOver(() => file);

接下来是对已知 ID 集合的迭代，这些 ID 必须作为关系存在(所有这些)

//这里我们将获取每个属性并创建一个子查询//所有这些子查询，将与 AND 连接//所以只有这些具有所有属性的文件才会被选中foreach (var attrId in attributeIds){//创建子查询，返回 Files.idvar subQueryForAttribute = QueryOver.Of()//不需要加入，所有的东西都在配对表中.Select(x => x.file.id);var id = attrId;//局部变量//并将它们转换为 where 条件subQueryForAttribute.Where(pair => pair.attr.id == id);//最后，将此子查询添加为对顶级查询的限制query.WithSubquery.WhereProperty(() => file.id).In(subQueryForAttribute);}var list = query.List<文件>();

已知 IDS 的解决方案更容易(SQL 语句中需要的表更少)

注意:不得不说:很高兴看到您引入了 many-to-one 和 one-to-many的多对多.我个人认为，这个例子正好说明了它可以带来多大的利润......即使使用复杂的过滤器也能进行搜索

一些链接，展示了 QueryOver 的强大功能:查询 HasMany 参考，以及为什么不使用 many-to-many 映射的一些很好的理由:多对多与额外的列 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-only and view = visible, I want to be returned only foo.txt and goose.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-one and one-to-many instead 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 filters

Some links, to show the power of the QueryOver: Query on HasMany reference , and some good reason why not to use many-to-many mapping: many-to-many with extra columns nhibernate

这篇关于NHibernate - 使用 Junction/Joiner Table 的多对多查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！