1

I am using Play Framework 2 (Java) together with Ebean. Among my model classes, I have classA with a one-to-many relation to classB. The associated table for classB therefore has a field which either contains nullor an id for a classA entity.

I would like to retrieve the number of classA rows for which there are one or more associated classB entities. In SQL terms, what I want to do is this:

select count(id) from classA where id in (select classA_id from classB);

How do I do this with Play Framework / Ebean?

I am thinking that there should be a built-in method to perform this simple task instead of having to use raw SQL. Note that it is not viable to fetch a long list from the database and then count the number of entries in Java: I want to let the database do the work.

Neil Stockton
  • 11,383
  • 3
  • 34
  • 29
plade
  • 541
  • 4
  • 15

1 Answers1

1

Have a look at the documentation, they explain pretty well how to build the queries.

int count = 
   Ebean.find(classB.class)
     .fetch("id")
     .where("classA_id IS NOT NULL")
     .findRowCount();

In case you want the "distinct" result, add .setDistinct(true) to the query.

Peanut
  • 3,753
  • 3
  • 31
  • 45
  • That's not a bad solution, and I can even reduce the amount of raw SQL by using the built-in `isNotNull()` to write `.where().isNotNull("classA")`. Only problem left now is that `.setDistinct()` (which I certainly need) doesn't work in Ebean (at least not in Play Framework 2.3.9): http://stackoverflow.com/questions/19472511/ebean-query-using-setdistinct-does-not-work – plade Jun 16 '15 at 14:23
  • Is updating to Play Framework 2.4 an option? They state 2.4 would support newer versions of EBean. – Peanut Jun 16 '15 at 14:42
  • Anyway, since this problem has now been reduced to another problem, namely that of `setDistinct()` not working, I will accept the answer. – plade Jun 17 '15 at 12:16