Queries in Ebean

Query Joins

You use FetchConfig to define that you want to use a separate SQL query to fetch that part of the object graph (to use a "query join" rather than a "fetch join").
What this means is that Ebean will use 2 SQL queries rather than 1 to build the object graph.

Note that you do not need to expicitly use FetchConfig if you don't want to. In that case Ebean will automatically convert any fetch paths over to use
FetchConfig if it needs to (multiple *ToMany relationships etc).

There are a number of cases where using multiple SQL queries is
more efficient than a single query.

Any time you want to load multiple OneToMany associations it will likely be more
performant as multiple SQL queries. If a single SQL query was used that would result in a
Cartesian product.

// We want to fetch 2 or more OneToMany associations
// ... fetch one of the OneToMany (bug attachments) as a 'fetch join'
// ... and the other (bug details) as a 'query join'
List<Bug> bugs =
Ebean.find(Bug.class)
.fetch("attachments")
.fetch("details", new FetchConfig().query())
.findList();
// Sql query 1) fetches the bugs and bug attachments
// Sql query 2) fetches the bug details
// ... Two SQL queries avoiding a cartesian product
// NOTE: If you did not specify FetchConfig in this case
// ebean would automatically do so as we are fetching
// multiple *ToMany relationships (to avoid Cartesian product).

There can also be cases loading across a single OneToMany where 2 SQL queries (using
Ebean "query join") can be more efficient than one SQL query (using Ebean "fetch join").
When the "One" side is wide (lots of columns) and the cardinality difference is high (a lot
of "Many" beans per "One" bean) then this can be more efficient loaded as 2 SQL queries.

Additionally you can make use of query joins when you want to use maxRows to limit a result and also join a OneToMany association. You use maxRows with the query (resulting in SQL limit/offset or rownum etc) and then with the query join you can fetch all or some of the associated OneToMany beans.

// We want to use SQL limit/offset (or rownum etc) to
// limit the number of orders returned to 100, we also
// want the order details for the first 20 orders
// (after that lazy load 20 at a time)
List<Order> orders =
Ebean.find(Order.class)
.fetch("details", new FetchConfig().queryFirst(20).lazy(20))
.setMaxRows(100)
.where().eq("status",Order.Status.NEW)
.order().desc("id")
.findList();
// Note: You can alternatively make use of a subquery rather than
// use a query join in some cases.

example

Example: Using 2 Query Joins - results in 3 SQL queries used to build this object graph. Also note the use of partial objects.