Complex joins using Play Framework and Ebean

Question

I'm using the PlayFramework and I'm really liking it. When I want to grab data from a table, for example I have a user table, I use the following syntax:

List<User> users = User.find.where().eq("email", email).findList();

My question is that when I get the user object, I have an id column. With that id value I can map to other tables and the id's of those tables can be mapped to even more tables, so basic concept of joining across several tables. Is there any example or place I can read where it describes how to implement that with the above-like syntax?

I tried to find myself and couldn't, only way I can think of it at this point is to use straight sql with prepared statements which I'd rather not do.

Answer 1

ellou' kalvish

Relationships between models are set with common JPA annotations like @OneToMany, @ManyToOne, @OneToOne, etc.

So if you have User.java model for user table and Question.java model for user's Question you can join them with @OneToMany (One User has Many Questions)

User

@Entity
public class User extends Model {
    @Id
    public Long id;

    public String email;

    @OneToMany
    public List<Question> questions;
}

Question

@Entity
public class Question extends Model {
    @Id
    public Long id;

    public String question;
}

When you'll select a User in controller, Ebean will perform 'joins' by default and will fetch all user's questions as well:

User user = User.find.where().eq("email", email).findUnique();
List<Question> usersQuestion = user.questions;

By default Ebean fetches all object's properties and relations, so you don't need to create subqueries. Of course you can or even should select/fetch only the data that is required at the moment.

At the official Ebean documentation page you'll find quite good Reference guide (pdf), general description of relationship is available in section 11.6.2 Relationships.

In section 4.1.2 Query there is example (second) which demonstrates how to get "partial" object with use of select() and fetch()