Fetch

With fetch we specify the properties that should be fetched on associated beans. Beans that are related by OneToOne, OneToMany, ManyToOne and ManyToMany.

Fetch means we want the query to eagerly load that path and that we prefer for that to happen as a SQL JOIN.

QCustomer cust = QCustomer.alias();
QContact cont = QContact.alias();

List<Customer> customers =
  new QCustomer()
  .select(cust.name, cust.version, cust.whenCreated)    // root level properties
  .contacts.fetch(cont.email)                           // contacts is a OneToMany path

  .name.istartsWith("Rob")
  .findList();

The resulting sql is:

select t0.id, t0.name, t0.version, t0.when_created,    -- customer columns
       t1.id, t1.email                                 -- contact columns
from customer t0
left join contact t1 on t1.customer_id = t0.id
where lower(t0.name) like ? escape'|' order by t0.id

The contacts path is a OneToMany so we get a left join to the contact table. We only fetch the id and email properties for the contacts.

The above query written using the query expression API is:

List<Customer> customers =
  database.find(Customer.class)
    .select("name, version, whenCreated")    // root level properties
    .fetch("contacts", "email")              // contacts is a OneToMany path

    .where()
    .istartsWith("name", "Rob")
    .findList();

Fetch variations - fetch, fetchQuery, fetchCache, fetchLazy

There are 4 variations of fetch. The summary of what they are is:

fetch - fetch eagerly preferring SQL JOIN
fetchQuery - fetch eagerly using a separate secondary SQL query
fetchCache - fetch eagerly hitting the L2 cache, cache misses fetched using a separate secondary SQL query
fetchLazy - fetch lazy

Ebean applies rules when translating ORM queries to SQL queries such that:

Ebean will not generate a SQL cartesian product
Ebean will honor maxRows in SQL

Ebean will automatically convert fetch paths into fetchQuery paths to ensure that it honors these rules. In effect there is a limit on the number of OneToMany & ManyToMany paths that can be included in a SQL query (as otherwise we get a SQL cartesian product which is not desirable).

FetchQuery

When we specify fetchQuery we are telling Ebean that we want this path fetched eagerly but that we want that to be done using a separate sql query (known as a "secondary query" or "query join").

We choose to do this on specific paths because we know the relative costs of sql joins vs query joins based on cardinality and the relative width of the results etc.

List<Order> orders =
  new QOrder()
    .customer.fetchQuery()                // fetch customer (and children) using a "secondary query"
    .customer.billingAddress.fetch()
    .customer.shippingAddress.fetch()
    .findList();

With the above query we use fetchQuery to explicitly fetch the customers with their billing and shipping addresses using a second separate sql query.

Primary query

-- "primary" query fetches the orders only
select t0.id, ...
from orders t0

Secondary query (aka "query join")

-- "secondary" query fetches customers with
-- their billing and shipping addresses

select t0.id, t0.inactive, t0.name, ...,                         -- customer
       t1.id, t1.line1, t1.line2, t1.city, ...,                  -- customers billing address
       t2.id, t2.line1, t2.line2, t2.city, ...                   -- customers shipping address
from customer t0
join address t1 on t1.id = t0.billing_address_id
join address t2 on t2.id = t0.shipping_address_id
where t0.id in (?, ?, ... )                                      -- customer ids

FetchCache

fetchCache is like fetchQuery except we hit the L2 cache first.

List<Order> orders =
  new QOrder()
    .customer.fetchCache()         // Hit L2 cache for customer, for cache miss load from database
    .findList();

In the above the query fetches orders from the database. For Customer it will hit the L2 cache for customer and for the cache misses it will load the Customers from the Database. This is performed in batches of 100 customers at a time.

FetchLazy

With fetchLazy we don't want to eagerly fetch this path BUT if it is lazy loaded we are optimising what the lazy loading query will fetch.

e.g. lazy loading Customer only fetch the name.

List<Order> orders =
  new QOrder()
    .fetchLazy("customer", "name")
    .findList();

...

// invoke lazy loading will only fetch customer name
customer.getName();

e.g. lazy loading Customer additionally fetch the billingAddress and shippingAddress.

QCustomer cust = QCustomer.alias();

List<Order> orders =
  new QOrder()
    .fetchLazy("customer")
    .fetch("customer.billingAddress")  // fetched with lazy load of customer
    .fetch("customer.shippingAddress") // fetched with lazy load of customer

    .findList();


...

// invoke lazy loading will fetch customer name + billingAddress + shippingAddress
customer.getName();

Alias Query beans

When using query beans we use alias beans to provide properties for both the select and fetch.

// "alias" beans for customer and contact which
// we use to specify the properties to select and fetch
QCustomer cust = QCustomer.alias();
QContact con = QContact.alias();

// using query beans
List<Customer> customers =
  new QCustomer()
  .select(cust.name, cust.version, cust.whenCreated)    // customer properties only
  .contacts.fetch(con.email)                            // contact properties only

  .name.istartsWith("Rob")
  .findList();

Kotlin alias query beans

For Kotlin the alias beans are a companion object of the query bean and accessed via _alias. Otherwise the queries are the same in Kotlin.

val cust = QCustomer._alias
val con = QContact._alias

Select + Fetch = projection query tuning

Using select and fetch we are effectively tuning the query controlling what part of the object graph is loaded.

Generally we prefer a query to only load what it needs to from the database. This gives us the ability to reduce the cost of the query, the amount of data fetched and pulled over the network as well as the opportunity to use covering indexes etc.

select -> "graph root level properties"

The select defines what to fetch at the root level of the object graph.

fetch -> "graph leaf properties"

The fetch defines what to fetch for the leaves of the object graph. This translates to what to fetch on the OneToOne, OneToMany, ManyToOne and ManyToMany paths of the object graph.

Note that FetchGroup provides an alternative to select + fetch.

ORM query to SQL query rules

Ebean enforces 2 rules when it converts an ORM query into SQL queries. These rules have the effect of limiting the number of sql joins that it will allow when translating an ORM query into SQL queries. These 2 rules are:

Ebean will not generate a SQL cartesian product
Ebean will honor maxRows in SQL

Rule 1: No SQL Cartesian product

This rule has the implication that Ebean will allow at most 1 join to a OneToMany path or a ManyToMany path. If the ORM query has more than 1 of those paths then the ORM query will be translated and executed as multiple SQL queries.

We avoid sql cartesian product because it has a high risk of producing a very very bad sql query.

Rule 2: Always honor maxRows in SQL

This rule has the implication that Ebean can not include any joins to a OneToMany path or a ManyToMany path if there is a maxRows. This is because SQL max rows works on rows so we can't include SQL joins to paths that have a cardinality of more than 1.

Honoring maxRows is SQL is important because the database can typically give us a much better query plan and more efficient sql queries.

Ebean detects which paths are "ToMany" paths and determines if it needs to convert any of them from fetch paths to fetchQuery paths. Ebean firstly takes into account @FetchPreference mapping and then the order in which the paths are specified in the query. That is, generally the first ToMany path stays as fetch and the other ToMany paths are converted to fetchQuery.