How to query reference table in Spring JPA

Question

I'm having two tables Item and Review Item class

@Entity
public class Item {

  @Id
  @GeneratedValue
  private Long id;

  @Column(length = 100)
  @NotEmpty
  private String title;

  @Column(length = 200)
  private String description;

  @OneToMany(mappedBy = "item", cascade = CascadeType.ALL, orphanRemoval = true)
  Set<Review> reviews = new HashSet<>();

  public Item() {
  }

  public Item(String title, String description) {
    this.title = title;
    this.description = description;
  }

  public Long getId() {
    return id;
  }

  public String getTitle() {
    return title;
  }

  public String getDescription() {
    return description;
  }

  public Set<Review> getReviews() {
    return reviews;
  }

  public void addReview(Review review) {
    reviews.add(review);
    review.setItem(this);
  }

  @Override
  public String toString() {
    return "\nItem{" +
        "id=" + id +
        ", title='" + title + '\'' +
        ", description='" + description + '\'' +
        ", reviews=" + reviews +
        '}';
  }
}

Review Table

@Entity
public class Review {

  @Id
  @GeneratedValue
  private Long id;

  private Double rating;

  @Length(max=200)
  private String comment;

  @ManyToOne(optional = false)
  private Item item;

  @ManyToOne(optional = false)
  private User author;

  public Review() {
  }

  public Review(Double rating, String comment, User author) {
    this.rating = rating;
    this.comment = comment;
    this.author = author;
  }

  public Long getId() {
    return id;
  }

  public Double getRating() {
    return rating;
  }

  public String getComment() {
    return comment;
  }

  public Item getItem() {
    return item;
  }

  public User getAuthor() {
    return author;
  }

  void setItem(Item item) {
    this.item = item;
  }

  @Override
  public String toString() {
    return "\nReview{" +
        "id=" + id +
        ", rating=" + rating +
        ", comment='" + comment + '\'' +
        '}';
  }
}

I want to write query using Spring JPA to find Items with average of ratings less than e.g (6). Single item will be having multiple ratings so average rating of particular item should be less than 6. I tried many ways but not able to calculate.

public interface ItemRepository extends CrudRepository<Item, Long> {

    @Query(
              value = "SELECT i FROM Item i where (select AVG(rating) from Review where rating < :rating) > :rating", 
              nativeQuery = true)
    List<Item> findItemsWithAverageRatingLowerThan(@Param("rating") Double rating);
    
}

Please correct where I'm getting wrong.

Answer 1

1. You need to use JPQL query language. So remove nativeQuery = true
2. Perform join of Item and Review tables in the subquery

Query example:

@Repository
public interface ItemRepository extends CrudRepository<Item, Long> {
    @Query(value = "SELECT i FROM Item i where (select AVG(r.rating) FROM Review r where r.item = i) < :rating")
    List<Item> findItemsWithAverageRatingLowerThan(@Param("rating") Double rating);
}

Testing data:

insert into item(id, description, title) values(1, 'description', 'title1');
insert into item(id, description, title) values(2, 'description', 'title2');

insert into review(id, rating, comment, item_id) values(1, 3, 'comment', 1);
insert into review(id, rating, comment, item_id) values(2, 3, 'comment', 1);
insert into review(id, rating, comment, item_id) values(3, 3, 'comment', 1);

insert into review(id, rating, comment, item_id) values(4, 30, 'comment', 2);
insert into review(id, rating, comment, item_id) values(5, 3, 'comment', 2);
insert into review(id, rating, comment, item_id) values(6, 15, 'comment', 2);

Input value: 6
Output: Item entity with id = 1
Query returns items which have AVG Review rating less than 6

Hibernate generate native query:

    select
        item0_.id as id1_1_,
        item0_.description as descript2_1_,
        item0_.title as title3_1_ 
    from
        item item0_ 
    where
        (
            select
                avg(review1_.rating) 
            from
                review review1_ 
            where
                review1_.item_id=item0_.id
        )<?

Answer 2

I don't get the part

average rating of particular item should be less than 6

But simply, you are mixing JPQL syntax with native SQL syntax. just go for the native version when you use functions or particular SQL operators:

SELECT * FROM ITEM i JOIN REVIEW r ON i.id = r.item_id where AVG(r.rating) < :rating