JPA query with count aggregate function

Question

I have two entities A and B as below:

@Entity
public class A {
   @Id
   private int id;
   private String name;
   private boolean hasFailedChild;

   @OneToMany
   private List<B> bs;

   public A(String name, long count) {
      this.name = name;
      this.hasFailedChild = (count > 0);
   }
}

@Entity
public class B {
   @id
   private int id;
   private String name;
   private String status;
   @ManyToOne
   private A a;

   //required constructors
}

I am trying to get all As along with the its Bs count with Failed status using @Query.

I have tried with the below Query:

public interface ARepository extends CrudRepository<A, Integer> {

   @Query(value = "select new A(a.name, count(b)) " +
            "from A a left join a.bs b where b.status = 'Failed'")
   List<A> findAllA();
}

However, it is not working. Can someone help me here.

Yes, I have. If I need to create any other constructors also, I can create them. — bpa.mdl
– bpa.mdl, Commented Mar 4, 2020 at 16:59

SternK · Accepted Answer · 2020-03-04 19:51:38Z

1

You should correct your query in the following way:

select new A(a.name, count(b))
from A a
left join a.bs b
where b.status = 'Failed'
group by a.name

See the documentation for additional details.

answered Mar 4, 2020 at 19:51

SternK

13.3k34 gold badges40 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

JPA query with count aggregate function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related