6

I'm using Ebean with the Play 2 Framework and got two models: a user model and a book model. The user model is connected with the book model in a OneToMany Relationship. So every user can have many books or no book at all. The book model itself has properties too. Now I want to create a query in the user model, which returns only users, who have books with certain properties. For example: One property might be condition, like new or used. Now give me all users which have books in new condition. Is it possible to create such a query with the Ebean methods? Or do I have to use raw SQL?

marcospereira
  • 12,045
  • 3
  • 46
  • 52
fabian.kirstein
  • 708
  • 8
  • 26

2 Answers2

11

Say you have the following models:

@Entity
public class User extends Model {
  @Id
  @Column(name = "user_index")
  private int id;

  @Column(name = "user_first_name")
  private String firstName;

  [...]

  @OneToMany(mappedBy = "book_owner_index")
  private List<Book> books;

  public static Finder<Integer, User> find = new Finder<Integer, User>(Integer.class, User.class);

  [...]
}

and

@Entity
public class Book extends Model {
  @Id
  @Column(name = "book_index")
  private int id;

  @Column(name = "book_name")
  private String name;

  @Column(name = "book_condition")
  private String condition;

  [...]

  @ManyToOne
  @JoinColumn(name = "book_owner_index", referencedColumnName = "user_index")
  private User owner;

  [...]
}

Then you can do a search like:

List<User> users = User.find.select("*")
                            .fetch("books")
                            .where()
                            .eq("books.condition", "new")
                            .findList();
t0mppa
  • 3,983
  • 5
  • 37
  • 48
  • Thank you so much! Works perfectly. One follow up question: Is it as easily possible to order it by the number of books a user has? – fabian.kirstein Jan 18 '14 at 11:40
  • I believe you can do that with the formula annotation. Like adding an int field to your `User` model with `@Formula(select = "(SELECT COUNT(*) FROM books WHERE book_owner_index = ${ta}.index)")` and then you can order by that field. But this is untested code. See [here](http://www.avaje.org/static/javadoc/pub/com/avaje/ebean/annotation/Formula.html) for more details. – t0mppa Jan 18 '14 at 20:06
  • It's exactly like you said. The Formula Annotation does the trick. Thanks again! – fabian.kirstein Jan 19 '14 at 12:15
  • Old answer but how does the SQL of these models look like? With or without a 'user_book' table (containing foreign keys user & book tabels) – Jim Vercoelen Nov 24 '16 at 18:45
  • Column names of the two tables are in given in the JPA annotations. There's no need to have an extra table for mapping many-to-one relationship, because each book can have only one owner and thus book_owner_index is a column of the book table. – t0mppa Nov 28 '16 at 02:33
0
List<User> users = User.find.select("*")
                        .fetch("books")
                        .where()
                        .eq("t1.condition", "new")
                        .findList();

For me, it works only when I use "t1.", I am using Postgres DB. The generated query makes sense with t1.

Karthik Premnath
  • 11
  • 1