Introducing the Fluent Query API Part 3 of n: Aggregates, Functions and Projections

by Dennis 14. June 2012 06:47

Disclaimer: The API presented here is still under development, so there might be changes until the final release. If you have any suggestions or comments post them here, over at Uservoice or drop me a mail!

In this post we’ll be taking a look at how the new fluent query API handles the creation of aggregate values and the projection of documents into new types. Also, we’ll see how it translates .NET method calls to calls to functions that are supported by Sphinx. 

Projecting Results


For projecting results into a new form, the IFulltextQuery interface provides the Select method. If you’ve ever used LINQ, you’ll probably already know what to do with it Winking smile. It can be used to select only one attribute from the index e.g. the document id (for the declaration of the Product class please refer to the second post of this series):

IList<int> results = fulltextSession.Query<Product>().
                                     Match("a product").
                                     Select(p => p.Id).
                                     Results();

The generated SphinxQL query will then look like this:

SELECT id AS c1 FROM `product` WHERE MATCH('a product')

As you can see, only the id attribute is being retrieved from the index, thus avoiding unnecessary data transmission from Sphinx to the client.

We could also use it to project only the needed attributes for a given use-case into an anonymous type:

var results = fulltextSession.Query<Product>().
                              Match("a product").
                              Select(p => new
                              {
                                  p.Name,
                                  p.Price
                              }).Results();

 

Aggregates


In order to create aggregate values like the sum, the maximum of values etc., the API provides a static class named Projection which contains methods for all supported aggregation operations. For example, for a product search we could get the number of categories that contain matching products and the minimum and maximum prices in each category like this:

var results = fulltextSession.Query<Product>().
                              Match("a product").
                              GroupBy(p => p.CategoryId).
                              Select(p => new
                              {
                                  p.CategoryId,
                                  ProductCount = Projection.Count(),
                                  MinimumPrice = Projection.Min(() => p.Price),
                                  MaximumPrice = Projection.Max(() => p.Price)
                              }).Results();

 

Functions


Sphinx supports quite a few functions that can be used in a query. It ranges from numeric functions like FLOOR, CEIL over date functions like YEAR to comparison functions like IF. SphinxConnector.NET supports these functions by recognizing the corresponding .NET methods and translating them to their Sphinx equivalents. Most numeric functions can used via the Math class provided by .NET. e.g.

var results = fulltextSession.Query<Product>().
                              Select(p => new
                              {
                                  Floor = Math.Floor(p.Price),
                                  Ceiling = Math.Ceiling(p.Price)
                              }).Results();

The date functions can be used via the methods of the DateTime class, IF can be used via the ternary operator, e.g:

var results = fulltextSession.Query<Product>().
                              Select(p => new
                              {
                                  Price = p.CategoryId == 5 ? p.Price * 0.9m : p.Price
                              }).Results();

will be translated to:

SELECT IF(categoryid = 5, price * 0.9, price) AS c1 FROM `product`

For functions that have no corresponding .NET method, SphinxConnector.NET provides the Function class which contains methods for functions like Fibonacci and Geodist. Additionally, there are extension methods for the IN and INTERVAL functions. Here’s an example for getting the number of products in certain price intervals for some categories:

var results = fulltextSession.Query<Product>().
Where(p => p.CategoryId.In(4, 8, 15, 16, 23, 42)). Select(p => new { Count = Projection.Count(), PriceInterval = p.Price.Interval(10, 50, 100, 1000) }). GroupBy(p => p.PriceInterval). Results();

Tags: , , ,