I have two tables:
Item table representing item with some values
table Item(
id,owner,name....
)
and "view table" representing view of the item by user
table ItemView(
id, item, user_id...
)
Now i have @Entities:
@Entity
@Table(name = "item", schema = "public")
public class ItemEntity {
@ManyToOne
@JoinColumn(name = "owner", nullable = false)
private UserEntity user;
}
and:
@Entity
@Table(name = "item_view", schema = "public")
public class ItemViewEntity {
@ManyToOne
@JoinColumn(name = "user_id", nullable = false)
private UserEntity user;
}
I am using JPARepositorires:
public interface ItemViewRepository extends JpaRepository<ItemViewEntity, Long> {
....
}
And i want to retreive top 4 viewed items of user. If i was using pure sql i could write something like:
`Select * from Item u where u.id in (
Select item from item_view group by item order by count(*) desc limit 4
) and u.owner = ...`
How can i do this nested query using JPA repository?