Select where..in using Spring jpa repository

Viewed 42

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?

0 Answers
Related