CODEWITHSUNDEEP
javajpajpql
Engineering Machine
Engineering Machine

Reputation: 642

How to return an object with the biggest collections size in JPA?

For example, I have this code:

@Entity
@Table(name = "Foo")
public class FooImpl implements Foo {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "ID", nullable = false)
private Long id;

@Column(name = "name", nullable = false)
private String fooName;

@ElementCollection
private Set<Long> fooSet = new HashSet<Long>();

// constructors + getters

}

I want to create a method in my service class which will return an object with the greatest fooSet size. How can I achieve this with JPQL? The below code is incorrect but this is what I've tried to do:

@Override
public Foo getTopFoo() {
return (Foo)entityManager.createQuery("select c from Foo where max(size(c.fooSet)) ").getSingleResult();
}

Upvotes: 0

Views: 858

Answers (3)

Chris
Chris

Reputation: 21155

You seemed to be on the right track, you just need subqueries to return the max value and compare it to the size of the current c's list. Something like:

So if "Select max(size(c.fooSet)) from Foo c" gives you the max value,

"select f from Foo f where size(f.fooSet) = (select max(size(c.fooSet)) from Foo c)"

Should give you the Foos with the size equal to the max size.

Upvotes: 0

Maarten Winkels
Maarten Winkels

Reputation: 2417

The easiest and most readable way to do this is using two queries.

select id, count(*) from Bar group by id order by count(*) desc, id asc

This query will give you the ids and the collection size (assuming Bar is the name of the collection table.

With JPA you can easily select only the first result. With that id, selecting the Foo object is easy.

Another option is to limit the result in SQL and join with the Foo table.

from Foo where id = (select top 1 id from (select id, count(*) from Bar group by id, order by count(*) desc, id asc))

This cannot be done in a db agnostic way, however.

Upvotes: 0

NoDataFound
NoDataFound

Reputation: 11969

You can try that, because I know you can do subquery like in/= in JPQL, but I have big doubt that it works or that it's a valid syntax (notice the use of Foo.class to avoid the cast!):

@Override
public Foo getTopFoo() {
  return entityManager.createQuery("select c from Foo c where size(c.fooSet) = (select max(size(c.fooSet)) from Foo c)", Foo.class).getSingleResult();
}

I don't think that your JPA implementation will correctly translate the max(size()) as it is a computed value: in pseudo SQL, it would give this:

select f.*, count(ff.*) as cu
from Foo f
left join Foo_fooSet ff on [...]
group by f.*

Of course, you need to enumerate all the column of table Foo (or f).

You would need to return a subquery based on that count, something like:

select max(cu) from (
    select f.*, count(ff.*) as cu
    from Foo f
    left join Foo_fooSet ff on [...]
    group by f.*
)

But I don't remember JPQL allowing a from that accepts a subquery.

However, you can use native query and this would work:

select f.*
from Foo f
left join Foo_fooSet ff on [...]
group by f.*
having count(ff.*) = (
  select max(cu) from (
    select f.id, count(ff.*) as cu
    from Foo f
      left join Foo_fooSet ff on [...]
    group by f.id
  )
)

In the the subquery (which count), you need only the id.

If the first select (f.*), you have to put ALL the column corresponding to the fields of your entity. Have fun!

Or: returns the id of your entity, then load them using your entityManager.

Upvotes: 3

Related Questions