How to search for documents where a number falls between 2 values?

Question

I am using Advanced.LuceneQuery e.g.

RavenQueryStatistics stats = null;
vm.Products = DocumentSession.Advanced.LuceneQuery<Product>("Products/Index")
            .Statistics(out stats)
            .Where(searchExpression)
            .OrderBy(columnToSortBy)
            .Skip((vm.PageIndex - 1) * vm.PageSize)
            .Take(vm.PageSize)
            .ToArray()
            ;

with this index

public Products_Index()
{
    Map = products => from p in products
                      select new
                      {
                          p.ItemNum,
                          p.BrandName,
                          p.ProductName,
                          p.Catalog,
                          p.UOM,
                          p.CasePack,
                          p.AveWeight,
                          p.CatalogId,
                          p.HasPicture,
                          p.INFO2,
                          p.IsOfflineSupplierItem,
                          p.IsRebateItem,
                          p.IsSpecialOrderItem,
                          p.IsSpecialPriceItem,
                          p.Price
                      };

    Indexes.Add(x => x.INFO2, FieldIndexing.Analyzed);
    Indexes.Add(x => x.CatalogId, FieldIndexing.Analyzed);
    Indexes.Add(x => x.HasPicture, FieldIndexing.Analyzed);
    Indexes.Add(x => x.IsOfflineSupplierItem, FieldIndexing.Analyzed);
    Indexes.Add(x => x.IsRebateItem, FieldIndexing.Analyzed);
    Indexes.Add(x => x.IsSpecialOrderItem, FieldIndexing.Analyzed);
    Indexes.Add(x => x.IsSpecialPriceItem, FieldIndexing.Analyzed);
    Indexes.Add(x => x.Price, FieldIndexing.Analyzed);
}

and a typical expression to execute would look like this

"INFO2:(blue*) AND INFO2:(pen*) AND HasPicture:(True) AND IsSpecialOrderItem:(True) AND IsRebateItem:(True) AND IsOfflineSupplierItem:(True) AND CatalogId:(736275001)" 

Now I need to incorporate a range search based on the price column/index. What would the syntax be to construct that portion of my where clause?

The requirements are

Price >= FromNumber Price <= ToNumber

EDIT: The method that constructs the where clause

private void ProductSearch(ProductSearchViewModel vm)
{

    var terms = vm.SearchTerm
        .ToLower()
        .Split(new char[] { ' ' });

    // Start buildeing up the query
    var sb = new StringBuilder();

    // terms
    foreach (string term in terms)
    {
        sb.AppendFormat("INFO2:({0}*) AND ", term);
    }

    if (vm.Filters != null)
    {

        // picture 
        if (vm.Filters.IsAtrFilterPictureSelected)
        {
            sb.AppendFormat("HasPicture:({0}) AND ", vm.Filters.IsAtrFilterPictureSelected);
        }
        // special order
        if (vm.Filters.IsAtrFilterSpecialOrderSelected)
        {
            sb.AppendFormat("IsSpecialOrderItem:({0}) AND ", vm.Filters.IsAtrFilterSpecialOrderSelected);
        }
        // special price
        if (vm.Filters.IsAtrFilterSpecialPriceSelected)
        {
            sb.AppendFormat("IsSpecialPriceItem:({0}) AND ", vm.Filters.IsAtrFilterSpecialPriceSelected);
        }
        // rebate
        if (vm.Filters.IsAtrFilterRebateSelected)
        {
            sb.AppendFormat("IsRebateItem:({0}) AND ", vm.Filters.IsAtrFilterRebateSelected);
        }
        // offline supplier
        if (vm.Filters.IsAtrFilterOfflineItemSelected)
        {
            sb.AppendFormat("IsOfflineSupplierItem:({0}) AND ", vm.Filters.IsAtrFilterOfflineItemSelected);
        }
        // catalog
        if (vm.Filters.CatalogSelected > 0)
        {
            sb.AppendFormat("CatalogId:({0}) AND ", vm.Filters.CatalogSelected);
        }
        // price range
        if (vm.Filters.PriceFrom > 0 && vm.Filters.PriceTo > 0)
        {
            sb.AppendFormat("Price_Range:[{0} TO {1}]", NumberUtil.NumberToString((double)vm.Filters.PriceFrom), NumberUtil.NumberToString((double)vm.Filters.PriceTo));
        }

    }

    // remove the last 'AND' from the string 
    var searchExpression = sb.ToString();
    if (searchExpression.EndsWith("AND "))
    {
        searchExpression = searchExpression.Substring(0, searchExpression.LastIndexOf("AND "));
    }

    // trace it out
    Logger.WriteMessage(Infrastructure.Logging.LogLevel.Info, "Search Term: " + searchExpression);

    Stopwatch watch = Stopwatch.StartNew();

    string columnToSortBy = string.Empty;
    if (vm.GridParams != null)
    {
        // Luncene specific way of ordering
        columnToSortBy = vm.GridParams.sidx ?? "Price";
        columnToSortBy = vm.GridParams.sord == "asc" ? "+" + columnToSortBy : "-" + columnToSortBy;
    }

    // execution of query
    RavenQueryStatistics stats = null;
    vm.Products = DocumentSession.Advanced.LuceneQuery<Product>("Products/Index")
                .Statistics(out stats)
                .Where(searchExpression)
                .OrderBy(columnToSortBy)
                .Skip((vm.PageIndex - 1) * vm.PageSize)
                .Take(vm.PageSize)
                .ToArray()
                ;

    watch.Stop();
    vm.TotalResults = stats.TotalResults;
    Logger.WriteMessage(Infrastructure.Logging.LogLevel.Info, "Search Time: " + watch.ElapsedMilliseconds);

}

Thank you, Stephen

Answer 1

You need something like this (note the "_Range"):

Price_Range:[FromNumber TO ToNumber]

See the Lucene query syntax docs for the full info. Also you need to put the number in the correct Hex format. Make sure you use the built-in functions in the NumberUtils class in the Raven.Abstractions namespace to do this for you, rather than doing it yourself.

BUT is there a reason you're using the low-level Lucene API and building the queries by hand? There's a strongly typed API with LINQ support that will do a lot of the work for you (session.Query<T>()).

Answer 2

Reiterating Matt's comments. You REALLY want to avoid building the queries by hand. The Lucene Query API has a method, WhereBetween that does it all for you. Please note that there is a LOT of magic going on there, for doing things like parameter formatting, etc, that you really want to take into account.