NHibernate – Many to Many Query using Junction/Joiner Table

by

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

Leave a Comment

tech