1
Vote

Query with "contains any word" filter produces incorrect SQL with two or more words

description

Here is some SQL produced by a query in our Orchard 1.7 site that has three filters.
  1. Content with type HpProduct
  2. Hp Product.Available In contains 'Canada'
  3. Hp Product.Species contains any of 'Rabbit', 'Quail', 'Pheasant', 'Chukkar', 'Duck', 'Goose', 'Turkey', 'Ratite', 'Llama', 'Alpaca', 'Chinchilla', 'Deer', 'Bison', 'Game-Bird', 'Wild-Bird'
(note: the original field was HpProduct.Category, but was renamed to .Species later - that could add confusion when reading the query)

The resulting SQL query will match any HpProduct.Category which has 'Rabbit', and will match any string field that has any of the other words. This is because only the first match actually checks if the field name is HpProduct.Category.
select distinct contentite0_.Id as col_0_0_
              , titlepartr6_.Title as col_1_0_
from
  Orchard_Framework_ContentItemVersionRecord contentite0_
  inner join Orchard_Framework_ContentItemRecord contentite1_
    on contentite0_.ContentItemRecord_id = contentite1_.Id
  inner join Orchard_Framework_ContentTypeRecord contenttyp2_
    on contentite1_.ContentType_id = contenttyp2_.Id
  inner join Orchard_Projections_FieldIndexPartRecord fieldindex3_
    on contentite1_.Id = fieldindex3_.Id
  inner join Orchard_Projections_StringFieldIndexRecord stringfiel4_
    on fieldindex3_.Id = stringfiel4_.FieldIndexPartRecord_id
  inner join Orchard_Projections_StringFieldIndexRecord stringfiel5_
    on fieldindex3_.Id = stringfiel5_.FieldIndexPartRecord_id
  inner join Title_TitlePartRecord titlepartr6_
    on contentite0_.Id = titlepartr6_.Id
where
  (contenttyp2_.NAME in ('HpProduct'))
  and (stringfiel4_.PropertyName = 'HpProduct.Category.'
  and (stringfiel4_.value like '%Rabbit%')
  or stringfiel4_.value like '%Quail%'
  or stringfiel4_.value like '%Pheasant%'
  or stringfiel4_.value like '%Chukkar%'
  or stringfiel4_.value like '%Duck%'
  or stringfiel4_.value like '%Goose%'
  or stringfiel4_.value like '%Turkey%'
  or stringfiel4_.value like '%Ratite%'
  or stringfiel4_.value like '%Llama%'
  or stringfiel4_.value like '%Alpaca%'
  or stringfiel4_.value like '%Chinchilla%'
  or stringfiel4_.value like '%Deer%'
  or stringfiel4_.value like '%Bison%'
  or stringfiel4_.value like '%Game-Bird%'
  or stringfiel4_.value like '%Wild-Bird%')
  and stringfiel5_.PropertyName = 'HpProduct.AvailableIn.'
  and (stringfiel5_.value like '%Canada%')
  and contentite0_.Published = 1
order by
  titlepartr6_.Title

comments

GeoffArmstrong wrote Sep 18, 2013 at 6:17 PM

I suspect this could be an easy bugfix, but I am not an HQL expert. It seems to be in these places:

Orchard.Projections.Providers.Filters.ContentFieldsFilter.ApplyFilter():
        public void ApplyFilter(FilterContext context, IFieldTypeEditor fieldTypeEditor, string storageName, Type storageType, ContentPartDefinition part, ContentPartFieldDefinition field) {
            var propertyName = String.Join(".", part.Name, field.Name, storageName ?? "");

            // use an alias with the join so that two filters on the same Field Type wont collide
            var relationship = fieldTypeEditor.GetFilterRelationship(propertyName.ToSafeName());

            // generate the predicate based on the editor which has been used
            Action<IHqlExpressionFactory> predicate = fieldTypeEditor.GetFilterPredicate(context.State);

            // combines the predicate with a filter on the specific property name of the storage, as implemented in FieldIndexService
            Action<IHqlExpressionFactory> andPredicate = x => x.And(y => y.Eq("PropertyName", propertyName), predicate);

            // apply where clause
            context.Query = context.Query.Where(relationship, andPredicate);
        }
Orchard.Projections.FilterEditors.Forms.StringFilterForm.GetFilterPredicate():
        public static Action<IHqlExpressionFactory> GetFilterPredicate(dynamic formState, string property) {
            var op = (StringOperator)Enum.Parse(typeof(StringOperator), Convert.ToString(formState.Operator));
            object value = Convert.ToString(formState.Value);

            switch (op) {
                // ...
                case StringOperator.ContainsAny:
                    var values1 = Convert.ToString(value).Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
                    var predicates1 = values1.Skip(1).Select<string, Action<IHqlExpressionFactory>>(x => y => y.Like(property, x, HqlMatchMode.Anywhere)).ToArray();
                    return x => x.Disjunction(y => y.Like(property, values1[0], HqlMatchMode.Anywhere), predicates1);
                // ...
            }
        }
Maybe this is a problem in NHibernate itself where if you do A AND B where B is a disjunction then it doesn't automatically parenthesize B or something?