Query Filters

YesSql.Filters.Query

YesSql.Filters.Query provides support for a dynamic search query syntax, enabling user input to safely drive a YesSql Query.

It consists of

• a configurable search parser, translating input to a universal search syntax,
• a query executor, to execute a set of search terms against a YesSql Query.

The search syntax consists of preconfigured search terms, and supports a syntax of

• termname
• seperator of :
• value

e.g. sort:created

or, in the case of a default term

• value

e.g. lake

A term supports either single conditions, or multiple conditions.

A OneCondition condition is used to describe a term with a single set of predefined values that can filter the query. Examples of this would include sort terms, or status terms.

e.g. status:published

A ManyCondition condition is used to describe a term which can support multiple Boolean operations against the filter.

When using a ManyCondition term the following Boolean operations are supported

• OR or || (also the default when no operation is specified)
• AND or &&
• NOT or !
• () for grouping

Examples of this would include a title filter.

• title:lake
• title:swimming lake -> title:swimming or lake
• title:"swimming lake"
• title:swimming AND lake
• title:(swimming AND lake) NOT beach

Configuring a Query Filter.

Filters are configured per Document type with a builder pattern.

• Register an IQueryParser<T> where T is a Document type that will be filter against.
• Add terms to the builder.
• Build

Terms

Two term types are available.

• NamedTerm
• DefaultTerm

WithNamedTerm is the most common term used, and it registers a term name, and a condition that will be applied when that term name is specified.

e.g. sort:oldest

WithDefaultTerm can only be used once in a Query Filter, and while it also specifies a term name, in this case the term name is optional, and it will be applied even when the term name is not specified.

e.g. lake or title:lake

Each specified Term must supply the conditions that will be applied to that term.

The following example configures an IQueryParser<BlogPost>

This example is available in the YesSql.Samples.Web project.

services.AddSingleton<IQueryParser<BlogPost>>(sp =>
    new QueryEngineBuilder<BlogPost>()
        .WithNamedTerm("sort", b => b
            .OneCondition((val, query) =>
            {
                if (Enum.TryParse<BlogPostSort>(val, true, out var e))
                {
                    switch (e)
                    {
                        case BlogPostSort.Newest:
                            query.With<BlogPostIndex>().OrderByDescending(x => x.PublishedUtc);
                            break;
                        case BlogPostSort.Oldest:
                            query.With<BlogPostIndex>().OrderBy(x => x.PublishedUtc);
                            break;
                        default:
                            query.With<BlogPostIndex>().OrderByDescending(x => x.PublishedUtc);
                            break;
                    }
                }
                else
                {
                    query.With<BlogPostIndex>().OrderByDescending(x => x.PublishedUtc);
                }

                return query;          
            })
            .AlwaysRun()
        )
        .WithDefaultTerm("title", b => b
            .ManyCondition(
                ((val, query) => query.With<BlogPostIndex>(x => x.Title.Contains(val))),
                ((val, query) => query.With<BlogPostIndex>(x => x.Title.IsNotIn<BlogPostIndex>(s => s.Title, w => w.Title.Contains(val))))
            )
        )
        .Build()
);

We add two Term Conditions

• A OneCondition sort term for sorting the query.
• A ManyCondition default term and a default term for filtering against the title of a BlogPost.

The sort Term

The sort term is added using WithNamedTerm, meaning that the name sort must be provided to activate this filter.

It has OneCondition where the values will be parsed using the syntax sort:value. No Boolean operations are supported on this term.

The OneCondition method takes a MatchQuery method, either synchronous or asynchronous, which is applied when the filter matches.

It also specifies .AlwaysRun() which is used to provide a default sort, when the user has not supplied any filter criteria.

The title Term

The title term is added using WithDefaultTerm, in this case the user may supply no value, e.g. lake or the named term title:lake

It has ManyCondition where the value will be parse using a Boolean criteria.

It takes both a MatchQuery and a NotMatchQuery.

The MatchQuery is applied when using an OR or AND expression and the NotMatchQuery is applied when using a NOT expression.

OneCondition Mappings

Two mapping methods are available for the OneCondition term.

MapFrom

This allows the filter engine to map from a model.

It can be used when a form post is used by the UI to generate filter expressions.

For example the sort term maybe provided via a SelectList which posts a form.

A MapFrom method will then map the result of the form binding to the FilterResult enabling the controller to correctly generate the search syntax.

Example

.MapFrom<Filter>((model) =>
{
    if (model.SelectedStatus != BlogPostStatus.Default)
    {
        return (true, model.SelectedStatus.ToString());
    }

    return (false, String.Empty);

})

In this mapping when the BlogPostStatus is not the default value, the filter value will be mapped to the FilterResult when MapFrom is called on the FilterResult.

e.g. filter.FilterResult.MapFrom(filter); where filter is the CLR type that has been bound from a form post.

The resulting filter expression can be generated with a call to filter.FilterResult.ToString()

MapTo

This allows a filter result to MapTo a model.

It can be used when a user has inputted their own search expression to map this expression to a model.

Example

.MapTo<Filter>((val, model) =>
{
    if (Enum.TryParse<BlogPostStatus>(val, true, out var e))
    {
        model.SelectedStatus = e;
    }
})

For example when the user has inputted a sort:oldest expression applying the mapping to a model allows the model to know the value of the search expression, so it is able to display the selected sort value.

e.g. filterResult.MapTo(filter); where filter is the CLR type the view will bind to.

AlwaysRun

On a OneCondition AlwaysRun can be specified to enforce that even when the term has not been specified it's MatchQuery will AlwaysRun. This can be used to provide a default value to a query.

For the sort term we check whether a sort matches the BlogPostSort enum, and if not, i.e. if no value has been provided we are still able to apply a default sort.

Example:

if (Enum.TryParse<BlogPostSort>(val, true, out var e))
{
    ...
else
{
    // Apply a default sort expression.
    query.With<BlogPostIndex>().OrderByDescending(x => x.PublishedUtc);
}

Executing a result

Once a FilterResult has been parsed it can be executed against a query.

Example

var query = session.Query<BlogPost>();

await filterResult.ExecuteAsync(new WebQueryExecutionContext<BlogPost>(HttpContext.RequestServices, query));

posts = await query.ListAsync();

The examples used for this tutorial can be found in the YesSql.Samples.Web project, along with examples of custom model binders which can be used to model bind a Query Engine to a Query String, or form post.