How to join the table to itself a few times in hibernate criteria?

174 Views Asked by At

I have some entity essence. It has a lot of attributes (over one hundred). I need make entities filtering. Filters'es count is Unlimited, one attribute can have some filters (such as "age > 20 AND age < 50"). The problem is that, in hibernate criteria I can not attach attribute table arbitrary number of times. The first time I join on the entity field, the second time I can not use this field.

Criteria creating:

var attributeType = typeof(Attribute);
var criteria = tr.CreateCriteria(attributeType, "ATTRIBUTE");
criteria
    .CreateAlias("Essence", "ESSENCE", NHibernate.SqlCommand.JoinType.RightOuterJoin)
    .CreateAlias("SomeModel", "SOME_MODEL", NHibernate.SqlCommand.JoinType.LeftOuterJoin)
    .Add(Restrictions.And(
        Restrictions.Eq("SOME_MODEL.Code", someCode),
        Restrictions.IsNull("ESSENCE.SomeParameter")));
...

Here attribute is needed to sort through it.

for (int i = 0; i < filters.Count; i++)
{
    string attributeColumnName = "ATTRIBUTE_" + i.ToString();
    criteria
        .CreateAlias("Essence.EssenceAttribute", 
            attributeColumnName,
            NHibernate.SqlCommand.JoinType.LeftOuterJoin,
            Restrictions.EqProperty("ESSENCE.Id", attributeColumnName + ".EssenceId"))
        .Add(Restrictions.Eq(
            attributeColumnName + ".AttributeId",
            (long)filters[i].AttributeId))
        .Add(Restrictions.Eq(
            attributeColumnName + ".Value",
            filters[i].Value));
}

When filters has only one filter, then everything works fine. If filters has two - I will catch error (something like "duplicate using Essence.EssenceAttribute"). How I can do it?

0

There are 0 best solutions below