Queries in Ebean

Joins

You can use fetch() to explicitly state which additional paths you want to fetch. You would do this to reduce "lazy loading" of those beans later.

example
// fetch orders and also fetch the order details
List<Order> orders = 
        Ebean.find(Order.class)
                .fetch("details")
                .findList();

... results in the "left outer join or_order_detail" and related d.* columns being included in the query (so they won't be lazy loaded later).

<sql summary='[app.data.test.Order] +many[details]'>
select o.id, o.order_date, o.ship_date, o.cretime, o.updtime, o.status_code, o.customer_id
        , d.id, d.order_qty, d.ship_qty, d.cretime, d.updtime, d.order_id, d.product_id 
from or_order o
left outer join or_order_detail d on o.id = d.order_id  
order by o.id
<sql>
example

An example with a few more paths fetched. Note that Ebean determines the type of SQL join for you based on the cardinality and optionality of the relationship.

List<Order> orders = 
        Ebean.find(Order.class)
                .fetch("details")
                .fetch("details.product")
                .fetch("customer")
                .fetch("customer.billingAddress")
                .fetch("customer.shippingAddress")
                .findList();
<sql summary='[app.data.test.Order, customer, -
--                  customer.billingAddress, customer.shippingAddress] -
--                              +many[details, details.product]'>

select o.id, o.order_date, o.ship_date, o.cretime, o.updtime, o.status_code
        , c.id, c.name, c.cretime, c.updtime, c.status_code
        , cb.id, cb.line_1, cb.line_2, cb.city, cb.region, cb.cretime, cb.updtime, cb.country_code
        , ca.id, ca.line_1, ca.line_2, ca.city, ca.region, ca.cretime, ca.updtime, ca.country_code
        , d.id, d.order_qty, d.ship_qty, d.cretime, d.updtime, d.order_id
        , dp.id, dp.sku, dp.name, dp.cretime, dp.updtime 
from or_order o
join or_customer c on o.customer_id = c.id 
left outer join or_address cb on c.billing_address_id = cb.id 
left outer join or_address ca on c.shipping_address_id = ca.id 
left outer join or_order_detail d on o.id = d.order_id 
left outer join or_product dp on d.product_id = dp.id  
order by o.id
<sql>
example

Ebean will automatically add SQL joins if they are required for the where clause or order by clause (and the matching joins are not explicitly included).

List<Order> orders = 
        Ebean.find(Order.class)
                .where().ilike("customer.name", "rob%")
                .findList();

... in the sql below, the join to or_customer is automatically added to support the where clause.

<sql summary='[app.data.test.Order]'>
select o.id, o.order_date, o.ship_date, o.cretime, o.updtime, o.status_code, o.customer_id 
from or_order o
join or_customer c on o.customer_id = c.id  
where lower(c.name) like ? 
<sql>
Introduction User Guide (pdf) Install/Configure Public JavaDoc Whitepapers
General Database Specific Byte Code Deployment Annotations Features
Top Bugs Top Enhancements
woResponse