Where

Query bean properties provide a nice type safe way to add expressions to the where clause. We get compile time validation of the query paths, expressions and bind value types.

// using query bean
Customer rob =
  new QCustomer()
    .name.equalTo("Rob") // type safe expression with IDE auto-completion
    .findOne();

The same query can be written without using query beans as:

// using standard query
Customer rob = DB.find(Customer.class)
    .where().eq("name", "Rob")
    .findOne();

Both queries above produce the same sql:

select ...
from customer t0
where t0.name = ?

With the above the name property is of type String/varchar and so only valid String expressions such as like, startsWith, contains etc are allowed and the bind value for the expression must be a String.

Developers get IDE assistance/auto-completion when creating queries using query beans as the properties and expressions available based on their types are suggested by the IDE with auto-complete.

Additionally with query beans, if the property is renamed (e.g. "name" became "fullName") or if the property changed it's type then the query would no longer compile.

Paths

With properties that are associated to beans (OneToOne, OneToMany, ManyToOne, ManyToMany) we can 'navigate' these paths adding valid expressions.

e.g. path "billingAddress.city" for Customer

// using query beans
List<Customer> customers =
  new QCustomer()
    .billingAddress.city.equalTo("Auckland")
    .findList();

// using standard query
List<Customer> customers = DB.find(Customer.class)
  .where()
     .eq("billingAddress.city", "Auckland")
  .findList();

Ebean will automatically add appropriate SQL JOINS to support the expressions in the query.

select ...
from customer t0
join address t1 on t1.id = t0.billing_address_id
where t1.city = ?

These property paths can be any depth. In the example below the path takes us from the Order bean to the Customer bean to the Address bean via customer.billingAddress.

e.g. path "customer.billingAddress.city" for Order

List<Order> orders =
  new QOrder()
    .customer.billingAddress.city.equalTo("Auckland")
    .findList();

AND

By default multiple expressions are added via AND.

List<Customer> customers =
  new QCustomer()
    .status.equalTo(Status.NEW)
    .whenCreated.greaterThan(lastWeek)
    .findList();

select ...
from customer t0
where t0.status = ? and t0.when_created > ?

// using standard query
List<Customer> customers = DB.find(Customer.class)
  .where()
    .eq("status", Status.NEW)
    .gt("whenCreated", lastWeek)
    .findList();

e.g. multiple paths for Order

List<Order> orders =
  new QOrder()
    .orderDate.greaterThan(lastWeek)
    .customer.status.equalTo(Status.NEW)
    .customer.billingAddress.city.equalTo("Auckland")
    .findList();

With the above query we have the additional paths of customer and customer.billingAddress. Ebean will determine the joins that are needed to support the expressions.

select ...
from orders t0
join customer t1 on t1.id = t0.order_id
join address t2 on t2.id = t1.billing_address_id
where t0.order_date >= ? and t1.status = ? and t2.city = ?

Notes on join type

The type of join used (left join or join) is based on the cardinality and optionality of the relationship. For example, if the customer foreign key on order was nullable then that is an optional relationship and a left join is used.

For any path, if a "higher level" join is a left join then all "child joins" (for that path) must also be a left join (i.e. "left joins cascade"). For example, in the example above - if the join from order to customers is a left join then the join from customer to address must also be a left join.

The type of join to use for expression on a ToMany path depends on whether the expression is contained in a OR. If the expression is contained inside an OR then a left join is used.

OR

When we want to add multiple expressions via OR we use or() and endOr() and all the expressions between them are joined by OR.

For example: (name is null OR name = 'Rob')

Customer customer
  = new QCustomer()
     .or()
       .name.isNull()
       .name.equalTo("Rob")
     .endOr()
     .findOne()

select ...
from customer t0
where (t0.name is null or t0.name = ? )

Customer customer = DB.find(Customer.class)
   .where()
     .or()
       .isNull("name")
       .eq("name", "Rob")
     .endOr()
     .findOne()

OR using raw expression

Rather than using the fluid style with or() and endOr() we can alternatively use raw() expressions. For example:

Customer customer
  = new QCustomer()
     .raw("(name is null or name = ?)", "Rob")
     .findOne()

Raw expressions

Sometimes we want to add raw expressions to the where clause. We use raw() to include arbitrary SQL, functions and stored procedures into the query where clause.

e.g. simple raw expression

List<Order> orders =
  new QOrder()
  .raw("orderDate > shipDate ")
  .findList()

e.g. use sql function

List<Order> orders =
  new QOrder()
  .raw("add_days(orderDate, 10) < ?", someDate)
  .findList();

e.g. sql subquery

List<Order> orders =
  new QOrder()
   .status.equalTo(Status.NEW)
   .raw("t0.customer_id in (select customer_id from customer_group where group_id = any(?::uuid[]))", groupIds)
   .findList()

Property paths to sql joins

If we use property paths in raw() then Ebean will automatically add appropriate joins to support the expression. For example:

List<Order> orders =
    new QOrder()
      .raw("(customer.name = ? or customer.billingAddress.city = ?)", "Rob", "Auckland")
      .findList();

... produces the following SQL with joins added to the customer and address tables.

select t0.id, t0.status, t0.order_date, t0.ship_date, ...
from orders t0
left join customer t1 on t1.id = t0.customer_id            -- supports customer.name
left join address t2 on t2.id = t1.billing_address_id      -- supports customer.billingAddress.city
where (t1.name = ? or t2.city = ?); --bind(Rob,Auckland)

Combing expressions

We can combine raw() expressions with other expressions. For example:

List<Order> orders =
  new QOrder()
    .status.eq(Order.Status.NEW)       // before raw()
    .raw("(customer.name = ? or customer.billingAddress.city = ?)", "Rob", "Auckland")
    .orderDate.greaterThan(lastWeek)   // after raw()
    .findList();

select ...
where t0.status = ?  and (t1.name = ? or t2.city = ?) and t0.order_date > ?
 --bind(NEW,Rob,Auckland,2019-01-17)

Ode to Raw expressions

If developer life was simple we would not need raw expressions. Many real world projects often have a few queries where our nice ORM query gets us 90% there but there is that predicate needed that is best expressed in SQL or uses a database specific function or feature.

Raw expressions is this nice little feature that allows us to put that arbitrary SQL expression into the where clause. We could instead go to findNative etc and provide all the SQL for the query but that is a bigger jump and raw expressions gives us that flexibility.

I'm not sure if I'm allowed a favourite feature but if I was I'd vote for raw expressions.