Filter Query Results
One of the most basic functionalities of querying is the ability to filter out data and return records that match a given condition. There are couple of ways to do this.
The following examples demonstrate how to add simple conditions to a query using all of those methods.
Where
List<Employee> results = session
.query(Employee.class, Employees_ByFirstAndLastName.class) // query 'Employees/ByFirstAndLastName' index
.whereEquals("FirstName", "Robert") // filtering predicates
.andAlso() // by default OR is between each condition
.whereEquals("LastName", "King") // materialize query by sending it to server for processing
.toList();
public class Employees_ByFirstAndLastName extends AbstractIndexCreationTask {
public Employees_ByFirstAndLastName() {
map = "docs.Employees.Select(employee => new {" +
" FirstName = employee.FirstName," +
" LastName = employee.LastName" +
"})";
}
}
from index 'Employees/ByFirstAndLastName'
where FirstName = 'Robert' and LastName = 'King'
Where - Numeric Property
List<Product> results = session
.query(Product.class, Products_ByUnitsInStock.class) // query 'Products/ByUnitsInStock' index
.whereGreaterThan("UnitsInStock", 50) // filtering predicates
.toList(); // materialize query by sending it to server for processing
public class Products_ByUnitsInStock extends AbstractIndexCreationTask {
public Products_ByUnitsInStock() {
map = "docs.Products.Select(product => new {" +
" UnitsInStock = product.UnitsInStock" +
" })";
}
}
from index 'Products/ByUnitsInStock'
where UnitsInStock > 50
Where - Nested Property
// return all orders that were shipped to 'Albuquerque'
List<Order> results = session
.query(Order.class)
.whereEquals("ShipTo_city", "Albuquerque")
.toList();
from Orders
where ShipTo.City = 'Albuquerque'
List<Order> results = session
.query(Order.class, Order_ByOrderLinesCount.class) // query 'Order/ByOrderLinesCount' index
.whereGreaterThan("Lines_count", 50) // filtering predicates
.toList(); // materialize query by sending it to server for processing
public class Order_ByOrderLinesCount extends AbstractIndexCreationTask {
public Order_ByOrderLinesCount() {
map = "docs.Orders.Select(order => new {" +
" Lines_count = order.Lines.Count" +
"})";
}
}
from index 'Order/ByOrderLinesCount'
where Lines.Count > 50
Where + Any
Any
is useful when you have a collection of items (e.g. Order
contains OrderLines
) and you want to filter out based on values from this collection. For example, let's retrieve all orders that contain an OrderLine
with a given product.
session
.query(Order.class, Order_ByOrderLines_ProductName.class) // query 'Order/ByOrderLines/ProductName' index
.whereEquals("Lines_productName", "Teatime Chocolate Biscuits") // filtering predicates
.toList(); // materialize query by sending it to server for processing
public class Order_ByOrderLines_ProductName extends AbstractIndexCreationTask {
public Order_ByOrderLines_ProductName() {
map = "docs.Orders.Select(order => new {" +
" Lines_productName = order.Lines.Select(x => x.ProductName)" +
"})";
}
}
from index 'Order/ByOrderLinesCount'
where Lines_ProductName = 'Teatime Chocolate Biscuits'
Where + In
When you want to check a single value against multiple values, the In
operator can become handy. To retrieve all employees where FirstName
is either Robert
or Nancy
, we can issue the following query:
List<Employee> results = session
.query(Employee.class, Employees_ByFirstAndLastName.class) // query 'Employees/ByFirstAndLastName' index
.whereIn("FirstName", Arrays.asList("Robert", "Nancy")) // filtering predicates
.toList();// materialize query by sending it to server for processing
public class Employees_ByFirstAndLastName extends AbstractIndexCreationTask {
public Employees_ByFirstAndLastName() {
map = "docs.Employees.Select(employee => new {" +
" FirstName = employee.FirstName," +
" LastName = employee.LastName" +
"})";
}
}
from index 'Employees/ByFirstAndLastName'
where FirstName IN ('Robert', 'Nancy')
Where + ContainsAny
To check if enumeration contains any of the values from a specified collection, you can use the containsAny
method.
Let's assume that we want to return all BlogPosts
that contain any of the specified tags
.
List<BlogPost> results = session
.query(BlogPost.class, BlogPosts_ByTags.class) // query 'BlogPosts/ByTags' index
.containsAny("tags", Arrays.asList("Development", "Research")) // filtering predicates
.toList(); // materialize query by sending it to server for processing
public class BlogPosts_ByTags extends AbstractIndexCreationTask {
public BlogPosts_ByTags() {
map = "docs.BlogPosts.Select(post => new {" +
" tags = post.tags" +
"})";
}
}
from index 'BlogPosts/ByTags'
where Tags IN ('Development', 'Research')
Where + ContainsAll
To check if an enumeration contains all of the values from a specified collection, you can use the containsAll
method.
Let's assume that we want to return all the BlogPosts
that contain all of the specified tags
.
List<BlogPost> results = session
.query(BlogPost.class, BlogPosts_ByTags.class) // query 'BlogPosts/ByTags' index
.containsAll("tags", Arrays.asList("Development", "Research")) // filtering predicates
.toList(); // materialize query by sending it to server for processing
public class BlogPosts_ByTags extends AbstractIndexCreationTask {
public BlogPosts_ByTags() {
map = "docs.BlogPosts.Select(post => new {" +
" tags = post.tags" +
"})";
}
}
from index 'BlogPosts/ByTags'
where Tags ALL IN ('Development', 'Research')
Where - StartsWith
// return all products which name starts with 'ch'
List<Product> results = session
.query(Product.class)
.whereStartsWith("Name", "ch")
.toList();
from Products
where startsWith(Name, 'ch')
Where - EndsWith
// return all products which name ends with 'ra'
List<Product> results = session
.query(Product.class)
.whereEndsWith("Name", "ra")
.toList();
from Products
where endsWith(Name, 'ra')
Remarks
Information
Underneath, Query
is converting predicates to the IndexQuery
class so they can issue a query from a low-level operation method.