Query joins

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).

example

// a normal 'fetch join' results in a single SQL query 
List<Order> orders = 
	Ebean.find(Order.class)
		.fetch("customer")
		.findList();

Example: A "Query Join" results in 2 SQL queries used to build the object graph

// 2 SQL statements are used to build this object graph
// The first SQL query fetches the Orders and the second
// SQL query fetches customers 
List<Order> orders = 
	Ebean.find(Order.class)
		.fetch("customer", new FetchConfig().query())
		.findList();

// Sql query 1) fetches the orders
// Sql query 2) fetches the customers for those orders

Example: More realistic when we additionally want a join to a OneToMany (customer contacts) from our ManyToOne (customer)

// A more realistic example where we want to also join
// customer contacts (A OneToMany from customer)
List<Order> orders = 
	Ebean.find(Order.class)
		.fetch("customer", new FetchConfig().query())
		.fetch("customer.contacts")
		.findList();

// Sql query 1) fetches the orders
// Sql query 2) fetches the customers and their contacts

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.

List<Order> orders = 
	Ebean.find(Order.class)
		.select("status, shipDate")

		.fetch("details", "orderQty, unitPrice", new FetchConfig().query())
		.fetch("details.product", "sku, name")

		.fetch("customer", "name", new FetchConfig().queryFirst(10).lazy(10))
		.fetch("customer.contacts","firstName, lastName, mobile")
		.fetch("customer.shippingAddress","line1, city")
		.findList();

Next: Partial Objects