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.

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