c# - NHibernate - 使用 Junction/Joiner 表进行多对多查询

Question

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

NHibernate many-to-many criteria (1)

NHibernate many-to-many criteria (2)

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

文件:

<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>

属性:

<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>

加入者:

<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>

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

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

例子:

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)

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

谁能帮我解决这个问题？谢谢。

Answer 1

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

我搜索名称/值

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

// 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);

在下一步中，我们将遍历属性，即过滤器集合

// 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);
}

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

// query.Take(25);
// query.Skip(100);

var list = query.List<Files>();

这是一个会产生这样的 SELECT 的查询

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 通过属性ID搜索

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

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);

接下来是通过一组已知 ID 进行迭代，这些 ID 必须作为关系(所有这些)

存在

// 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>();

已知 IDS 的解决方案更简单一些(SQL 语句中需要的表较少)

注意:不得不说:很高兴看到您引入了多对一和一对多多对多的。就我个人而言，我会说这个例子恰好表明，它可以带来多大的利润......即使使用复杂的过滤器也能进行搜索

一些链接，以显示QueryOver 的强大功能:Query on HasMany reference ，以及为什么不使用多对多映射的一些很好的理由:many-to-many with extra columns nhibernate