Orchard Queries generate Incorrect SQL when using "contains any word" filter

Topics: Core, Troubleshooting
Sep 16, 2013 at 7:11 PM
A little while ago I posted a issue about this bug: http://orchard.codeplex.com/workitem/20082

The example query had the following 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'
The SQL where clause looks like this:
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
and the correct SQL would look like
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
Has anyone else run into this issue? It was was producing strange behaviors, and the root cause wasn't clear until I traced the SQL, and then the problem was obvious. I'm wondering if other people out there are using the "contains any word" filter in their queries with two or more words.
Coordinator
Sep 17, 2013 at 5:50 PM
Do not use that feature. It's going to kill performance, guaranteed. Anything that generates LIKE constraints should be avoided. Multiple LIKE constraints with ors are even worse.
There is full-text search through Lucene integrated in Orchard for that sort of thing.
Sep 17, 2013 at 9:47 PM
Sure, but it's still a bug. :)

How do I add a Lucene filter to a projection query?
Coordinator
Sep 17, 2013 at 11:12 PM
Maybe, but not one that will rank very high in priority, seeing that it's bad practice ;) A Lucene filter for projections would be great. There isn't one that I know of.
Sep 18, 2013 at 5:12 PM
I sympathize with your time management problems, I really do. But why have the "Contains"/"Contains any word"/"Contains all words" filters at all then? And maybe they're not the best thing performance-wise, but if the database isn't a large one, isn't that hit a lot better than doing a call to Lucene, getting a list of IDs to build an in-list (which could be large, so you'd need to use a table-valued parameter, and can you even use those in Orchard?), and then passing that along to SQL Server to continue the query?

Also, 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);
                // ...
            }
        }
Is there 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?

Here's what I need to do. Products have a multi-select enumeration relating to what type they are. I need a projection that covers a subset of those types. If I can't use the "Contains any word" filter, what can I use?
Coordinator
Sep 18, 2013 at 5:20 PM
That's a good question. I don't think we should have that filter.

I can't comment on nHibernate itself, I just don't know, sorry. I would however like to ask, however, why "Contains any word" would have to be used to filter on an enumeration. That sound extremely wasteful. It seems like the "has terms" filter would be more appropriate.
Sep 18, 2013 at 6:45 PM
Ah, so don't use multi-select enumerations. Use taxonomies instead. Good to know.

Basically I didn't have the Taxonomies module installed. I generally try to work with the core features in unfamiliar products, and if they aren't enough, I try to find a module that will work. And multi-select enumerations seemed to be working perfectly (I didn't know they were implemented using LIKEs) until I ran into the bug. And that only showed up once we'd gotten to the point of mass data entry, had 300 content items in the database, and started getting some slightly fishy results. :)
Coordinator
Sep 18, 2013 at 6:52 PM
Aha! I see. Now it makes sense. Yes, Taxonomies are the way to go. And now they are core (from 1.7)
Developer
Sep 25, 2013 at 9:00 PM
FYI SearchFilter capable of filtering items on a search query is now implemented as part of Helpful Extensions.
Oct 2, 2013 at 11:11 PM
Based on the advice here, I'm taking the Taxonomy module for a spin. But it seems I've run into a issue.

I setup two taxonomies. One is for "species" and another for "region". I created a content item with the species "Equine" and "Rabbit", and in the regions "Canada" and "USA".

I made a query with the following two "Has Terms" filters:

Categorized with one of Beef, Dairy, Equine
Categorized with one of Canada

The test query doesn't find the content item, but it should.

However, if I change the terms to:
Categorized with one of Beef, Dairy, Equine
Categorized with all of Canada

The test query does find the example content item. I'm at a loss to why this is.
Coordinator
Oct 5, 2013 at 2:32 AM
Please file a bug.