You are currently browsing legacy 3.0 version of documentation. Click here to switch to the newest 6.2 version.
    We can help you with migration to the latest RavenDB
    Contact Us Now
    See on GitHub

    Filtering

    One of the most basic functionalities when it comes to querying is the ability to filter out data and only return records that match given condition. There are couple of ways to do this, and they all depend on querying approach you want to use (Query from basic session operations, DocumentQuery from advanced session operations or low-level Command). Following example demonstrates how to add a simple conditions to query using all those methods.

    Where

    QEmployee e = QEmployee.employee;
List<Employee> results = session
  .query(Employee.class, Employees_ByFirstAndLastName.class)   //query 'Employees/ByFirstAndLastName' index
  .where(e.firstName.eq("Robert").and(e.lastName.eq("King")))  // filtering predicates
  .toList();     // materialize query by sending it to server for processing
    QEmployee e = QEmployee.employee;
List<Employee> results = session
  .advanced()
  .documentQuery(Employee.class, Employees_ByFirstAndLastName.class) // query 'Employees/ByFirstAndLastName' index
  .whereEquals(e.firstName, "Robert") // filtering predicates
  .andAlso()                       // by default OR is between each condition
  .whereEquals(e.lastName, "King") // filtering predicates
  .toList();                     // materialize query by sending it to server for processing
    QueryResult result = store
  .getDatabaseCommands()
  .query("Employees/ByFirstAndLastName",
    new IndexQuery("FirstName:Robert AND LastName:King"));
    public Employees_ByFirstAndLastName() {
  map =
   " from employee in docs.Employees " +
   " select new                              " +
   " {                                       " +
   "     FirstName = employee.FirstName,     " +
   "     LastName = employee.LastName        " +
   " };                                      ";
}

    Where - numeric property

    QProduct p = QProduct.product;
List<Product> results = session
  .query(Product.class, Products_ByUnitsInStock.class) // query 'Products/ByUnitsInStock' index
  .where(p.unitsInStock.gt(10)) // filtering predicates
  .toList();  // materialize query by sending it to server for processing
    QProduct p = QProduct.product;
List<Product> results = session
  .advanced()
  .documentQuery(Product.class, Products_ByUnitsInStock.class) // query 'Products/ByUnitsInStock' index
  .whereGreaterThan(p.unitsInStock, 50) // filtering predicates
  .toList(); // materialize query by sending it to server for processing
    store
  .getDatabaseCommands()
  .query("Products/ByUnitsInStock",
    new IndexQuery("UnitsInStock_Range:{Ix50 TO NULL}"));
    public static class Products_ByUnitsInStock extends AbstractIndexCreationTask {
  public Products_ByUnitsInStock() {
    map =
     " from product in docs.Products " +
     " select new                           " +
     "   {                                  " +
     "       product.UnitsInStock           " +
     "   };                                 ";
  }
}

    The importance of types in queries

    Let's consider the following index and queries:

    public static class Orders_ByTotalPrice extends AbstractIndexCreationTask {
  @QueryEntity
  public static class Result {
    public double totalPrice;
  }

  public Orders_ByTotalPrice() {
    map = "from order in docs.orders select new { TotalPrice = order.Lines.Sum(x => (x.Quantity * x.PricePerUnit) * (1 - x.Discount)) }";
  }
}
    QFiltering_Orders_ByTotalPrice_Result x = QFiltering_Orders_ByTotalPrice_Result.result;
List<Orders_ByTotalPrice.Result> results = session.query(Orders_ByTotalPrice.Result.class, Orders_ByTotalPrice.class)
        .where(x.totalPrice.gt(50))
        .toList();
    QFiltering_Orders_ByTotalPrice_Result x = QFiltering_Orders_ByTotalPrice_Result.result;
List<Orders_ByTotalPrice.Result> results = session.advanced().documentQuery(Orders_ByTotalPrice.Result.class, Orders_ByTotalPrice.class)
        .whereGreaterThan(x.totalPrice, 50.0)
        .toList();
    store.getDatabaseCommands().query("Orders/ByTotalPrice", new IndexQuery("TotalPrice_Range:{Dx50 TO NULL}"));

    Note that PricePerUnit is of type double in Order entity, so indexed TotalPrice value will be double too. In oder to properly query such index, we need to preserve types in queries. That is why Orders_ByTotalPrice.Result.TotalPrice is double and IndexQuery.Query has Dx prefix specified before the actual value. Types of properties in predicates have to match types of indexed fields.

    Where - nested property

    QOrder o = QOrder.order;
List<Order> results = session
  .query(Order.class, Order_ByOrderLinesCount.class) // query 'Order/ByOrderLinesCount' index
  .where(o.lines.size().lt(50))  // filtering predicates
  .toList(); // materialize query by sending it to server for processing
    QOrder o = QOrder.order;
session
  .advanced()
  .documentQuery(Order.class, Order_ByOrderLinesCount.class) // query 'Order/ByOrderLinesCount' index
  .whereGreaterThan(o.lines.size(), 50)   // filtering predicates
  .toList();  // materialize query by sending it to server for processing
    QueryResult result = store
  .getDatabaseCommands()
  .query("Order/ByOrderLinesCount",
    new IndexQuery("Lines.Count_Range:{Ix50 TO NULL}"));
    public static class Order_ByOrderLinesCount extends AbstractIndexCreationTask {
  public Order_ByOrderLinesCount() {
    map =
      " from order in docs.Orders     " +
      " select new                         " +
      " {                                  " +
      "   Lines_Count = order.Lines.Count  " +
      " };";
  }
}

    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 a values from this collection. For example, let's retrieve all orders that contain a OrderLine with a given product.

    QOrder o = QOrder.order;
QOrderLine l = QOrderLine.orderLine;
List<Order> results = session
  .query(Order.class, Order_ByOrderLines_ProductName.class) // query 'Order/ByOrderLines/ProductName' index
  .where(o.lines.any(l.productName.eq("Teatime Chocolate Biscuits"))) // filtering predicates
  .toList(); // materialize query by sending it to server for processing
    List<Order> results = session
  .advanced()
  .documentQuery(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
    QueryResult result = store
  .getDatabaseCommands()
  .query("Order/ByOrderLinesCount",
    new IndexQuery("Lines,ProductName:\"Teatime Chocolate Biscuits\""));
    public static class Order_ByOrderLines_ProductName extends AbstractIndexCreationTask {
  public Order_ByOrderLines_ProductName() {
    map =
     " from order in docs.Orders                                  " +
     " select new                                                      " +
     " {                                                               " +
     "     Lines_ProductName = order.Lines.Select(x => x.ProductName)  " +
     " }; ";
  }
}

    Where + In

    When you want to check single value against multiple values in operator can become handy. E.g. to retrieve all employees that FirstName is either Robert or Nancy we can issue following query:

    QEmployee e = QEmployee.employee;
List<Employee> results = session
  .query(Employee.class, Employees_ByFirstAndLastName.class) // query 'Employees/ByFirstAndLastName' index
  .where(e.firstName.in("Robert", "Nancy")) // filtering predicates
  .toList(); // materialize query by sending it to server for processing
    QEmployee e = QEmployee.employee;
List<Employee> results = session
  .advanced()
  .documentQuery(Employee.class, Employees_ByFirstAndLastName.class) // query 'Employees/ByFirstAndLastName' index
  .whereIn(e.firstName, Arrays.asList("Robert", "Nancy")) // filtering predicates
  .toList(); // materialize query by sending it to server for processing
    QueryResult result = store
  .getDatabaseCommands()
  .query("Employees/ByFirstAndLastName",
       new IndexQuery("@in<FirstName>:(Robert, Nancy)"));
    public Employees_ByFirstAndLastName() {
  map =
   " from employee in docs.Employees " +
   " select new                              " +
   " {                                       " +
   "     FirstName = employee.FirstName,     " +
   "     LastName = employee.LastName        " +
   " };                                      ";
}

    Where + ContainsAny

    To check if enumeration contains any of the values from a specified collection you can use containsAny method.

    Let's assume that we want to return all BlogPosts that contain any of the specified Tags.

    QBlogPost b = QBlogPost.blogPost;
List<BlogPost> results = session
  .query(BlogPost.class, BlogPosts_ByTags.class) // query 'BlogPosts/ByTags' index
  .where(b.tags.containsAny(Arrays.asList("Development", "Research"))) // filtering predicates
  .toList(); // materialize query by sending it to server for processing
    List<BlogPost> results = session
  .advanced()
  .documentQuery(BlogPost.class, BlogPosts_ByTags.class) // query 'BlogPosts/ByTags' index
  .containsAny("Tags", Arrays.<Object>asList("Development", "Research")) // filtering predicates
  .toList(); // materialize query by sending it to server for processing
    QueryResult result = store
  .getDatabaseCommands()
  .query("BlogPosts/ByTags",
    new IndexQuery("(Tags:Development OR Tags:Research)"));
    public static class BlogPosts_ByTags extends AbstractIndexCreationTask {
  public BlogPosts_ByTags() {
    map =
     " from post in docs.Posts " +
     " select new                  " +
     "  {                          " +
     "      post.Tags              " +
     "  };";
  }
}

    Where + ContainsAll

    To check if enumeration contains all of the values from a specified collection you can use containsAll method.

    Let's assume that we want to return all BlogPosts that contain all of the specified Tags.

    QFiltering_Orders_ByTotalPrice_Result r = QFiltering_Orders_ByTotalPrice_Result.result;
List<Orders_ByTotalPrice.Result> results = session
    .query(Orders_ByTotalPrice.Result.class, Orders_ByTotalPrice.class).where(r.totalPrice.gt(50)).toList();
    QFiltering_Orders_ByTotalPrice_Result r = QFiltering_Orders_ByTotalPrice_Result.result;
List<Orders_ByTotalPrice.Result> results = session.advanced()
    .documentQuery(Orders_ByTotalPrice.Result.class, Orders_ByTotalPrice.class)
    .whereGreaterThan(r.totalPrice, 50.0).toList();
    store.getDatabaseCommands().query("Orders/ByTotalPrice", new IndexQuery("TotalPrice_Range:{Dx50 TO NULL}"));
    public static class BlogPosts_ByTags extends AbstractIndexCreationTask {
  public BlogPosts_ByTags() {
    map =
     " from post in docs.Posts " +
     " select new                  " +
     "  {                          " +
     "      post.Tags              " +
     "  };";
  }
}

    Remarks

    Information

    Underneath, Query and DocumentQuery are converting predicates to IndexQuery class so they can issue a query from low-level command method.

    Safe By Default

    By default page size is set to 128 if not specified, so above queries will not return more than 128 results. You can read more about paging here.

    related articlesRelated Articles

    Home / Docs / Indexes / Querying / Filtering