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.
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();
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.
Select + Fetch = 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
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
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.
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 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
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