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.

Fallonfallout

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

Avow

Hi Marcus, Thanks for your reply. I was looking at the manual just now, and i'm wondering if I could ask you a question on it...section 4.1.5 is describing the OneToMany, ManyToOne, etc. But how does it know which columns map? Say I have a column myId and that maps to a column id in another table, is there a way to specify that? – Fallonfallout

Ebean is cleaver enough to create appropriate DDL on model's change. Beware this in real case, but it's really good idea, to create other testing project and just learn it directly from Ebean – Avow

@Fallonfallout I think what you are looking for is the JPA annotation @ JoinColumn where you can specify the name of the join column to use. – Vanhook