Error trying to convert postgreSQL query on JPA NamedQuery

Question

I am trying to build this PostgreSQL in a JPA sentence:

SELECT p.id
FROM testing.producto p
INNER JOIN testing.fact_prod f ON p.id = f.producto_id
GROUP BY f.producto_id, p.id
ORDER BY COUNT(f) DESC LIMIT 5;

In simple words im just trying to show the 5 most frequent products from the table fact_prod...

Here is my model Producto with the namedQuery which is the postgreSQL query converted to JPA:

@Entity
@Table(name = "PRODUCTO")
@XmlRootElement
@NamedQueries({
    @NamedQuery(name = "Producto.findTop5ByFrecuencia", 
          query = "SELECT p FROM Producto p INNER JOIN 
          p.factProdCollection f WHERE p.id = f.productoId.id 
          GROUP BY f.productoId, p.id ORDER BY COUNT(f) DESC")})

    public class Producto implements Serializable {
    
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        @Basic(optional = false)
        @Column(name = "ID")
        private Integer id;

       @OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.REMOVE, mappedBy = "productoId")
    private Collection<FactProd> factProdCollection;

And my FactProd model which is a relation with Producto table:

public class FactProd implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "ID")
    private Integer id;

    @JoinColumn(name = "PRODUCTO_ID", referencedColumnName = "ID")
    @ManyToOne(optional = false)
    private Producto productoId;

Here is where i call the namedQuery findTop5ByFrecuencia from Producto model where i supose to get the query:

public List<Producto> findTop5ByFrecuenciaInFactura() {
    Query query = em.createNamedQuery("Producto.findTop5ByFrecuencia").setHint(QueryHints.REFRESH, HintValues.TRUE);
    query.setMaxResults(5);
    List<Producto> productos = query.getResultList();
    return productos;
}

The problem is my namedQuery findTop5ByFrecuencia is not giving me any results even when my postgreSQL is giving the correct results.

Any ideas on how can i fix this?

Thanks!

Answer 1

Maybe try using JPQL's size function:

"SELECT p, SIZE(p.factProdCollection) testingCount FROM Producto p ORDER BY testingCount DESC"

Your JPA provider will turn that into a query likely using a subquery, but one that will not require grouping. The query will return both the Producto instances and the collection size as a List<Object[]>.