Retrieving number of rows with non-empty one-to-many relation

Question

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.

Answer 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.