use GROUP BY and ORDER BY with JPA repository on object

Question

I have two entities:

Item:

@Entity
@Table(name = "item", schema = "public")
public class ItemEntity {
    @ManyToOne
    @JoinColumn(name = "owner", nullable = false)
    private UserEntity user;
    ....
}

ItemView:

@Entity
@Table(name = "item_view", schema = "public")
public class ItemViewEntity {
    @ManyToOne
    @JoinColumn(name = "user_id", nullable = false)
    private UserEntity user;

    @ManyToOne
    @JoinColumn(name = "item_id", nullable = false)
    private ItemEntity item;
}

I want to return Items of user in order of the amount time they were viewed of

public interface ItemViewRepository extends JpaRepository<ItemViewEntity, Long> {
   @Query("Select u.item from ItemViewEntity u WHERE u.item.owner= ?! group by u.item order by count(u.item") desc")
   List<ItemEntity> getItemsByViews(UserEntity user);
}

Using this query i get error"

ERROR: column "itementi1_.id" must appear in the GROUP BY clause or be used in an aggregate function

I have all equals method defined on each entity

I have tried using group by and so on on primitive types and it all works. It seems i has problem with relations.

How can i fix this and make the query work?

Answer 1

You can do this by explicitly specifying a join in your query

    @Query("SELECT i, COUNT(v) AS viewcount " +
            "FROM ItemViewEntity v " +
            "JOIN v.item i " +
            "WHERE i.user = ?1 " +
            "GROUP BY i " +
            "ORDER BY viewcount DESC")
    List<ItemEntity> getItemsByViews(UserEntity user);