postgresql - Postgres View 在执行 SQL 时返回正确的行，但使用 REST 查询多次返回一行

Question

早上好，我有以下名为 user_popular_photos 的 View 。让我对这里的查询做一些解释。 feed_item 是一个表，feed_item_counts 是另一个 View ，它结合了另一个表中针对特定 feed_item 的条目的点赞数

SELECT f.author,
    f.thumbnail_url,
    f.feed_id
   FROM feed_item f
     JOIN ( SELECT f2.feed_item_id,
            f2.like_count AS count
           FROM feed_item_counts f2
          WHERE f2.like_count > 0) t2 ON f.feed_id = t2.feed_item_id
  ORDER BY t2.count DESC;

当我对数据库运行这个查询时(PGAdmin 查看数据 - 查看所有行)我得到了想要的结果

author | thumbnail_url | feed_id   |
user1  | thumb 1       | feed_id 1 | 
user1  | thumb 2       | feed_id 2 |
user1  | thumb 3       | feed_id 3 |
user2  | thumb 1       | feed_id 1 |
user2  | thumb 2       | feed_id 2 | and so on... 

然而，当我尝试取回结果时，使用我的 REST(Spring Boot)，我得到以下信息

{
  "username" : "user1",
  "popularPhotos" : [ {
    "feedId" : feed_id 3,
    "thumbnailUrl" : "thumb 3"
  }, {
    "feedId" : feed_id 3,
    "thumbnailUrl" : "thumb 3"
  }, {
    "feedId" : feed_id 3,
    "thumbnailUrl" : "thumb 3"
  } ]
}

这是通过以下方式生成的；

用户.Java

@Entity
@Table(name = "user_details")
public class User {

    @Id
    @NotNull @NotEmpty
    @JsonView(UserView.Summary.class)
    private String username;

    @JsonView(UserView.Summary.class)
    @OneToMany(fetch = FetchType.LAZY, mappedBy = "username")
    public List<UserPopularPhotos> popularPhotos;

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }   

    public List<UserPopularPhotos> getPopularPhotos(){
        return popularPhotos;
    }

    public void setPopularPhotos(List<UserPopularPhotos> popularPhotos){
        this.popularPhotos = popularPhotos;
    }

UserPopularPhotos

@Entity
@Table(name = "user_popular_photos")
@Immutable
public class UserPopularPhotos {

    @Id
    @Column(name = "author", insertable = false, updatable = false)
    @JsonIgnore
    public String username;

    @JsonView(UserView.Summary.class)
    @Column(name = "id", insertable = false, updatable = false)
    public Integer feedId;

    @JsonView(UserView.Summary.class)
    @Column(name = "thumbnail_url", insertable = false, updatable = false)
    public String thumbnailUrl;

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Integer getFeedId(){
        return feedId;
    }
    public void setFeedId(Integer feedId){
        this.feedId = feedId;
    }

    public String getThumbnailUrl() {
        return thumbnailUrl;
    }

    public void setThumbnailUrl(String thumbnailUrl) {
        this.thumbnailUrl = thumbnailUrl;
    }
}

我在这里遗漏了什么，我无法解释为什么 PGAdmin 中的查询返回正确的结果但 API 查询多次返回相同的结果(该用户的顶部条目)？

Answer 1

@Id 注释不在 user_popular_photos View 的唯一标识符上。将其从用户名中删除并添加到 feedId。

UserPopularPhotos.java

@Entity
@Table(name = "user_popular_photos")
@Immutable

public class UserPopularPhotos {

@Column(name = "author", insertable = false, updatable = false)
@JsonIgnore
public String username;

@Id
@JsonView(UserView.Summary.class)
@Column(name = "id", insertable = false, updatable = false)
public Integer feedId;

@JsonView(UserView.Summary.class)
@Column(name = "thumbnail_url", insertable = false, updatable = false)
public String thumbnailUrl;

public String getUsername() {
    return username;
}

public void setUsername(String username) {
    this.username = username;
}

public Integer getFeedId(){
    return feedId;
}
public void setFeedId(Integer feedId){
    this.feedId = feedId;
}

public String getThumbnailUrl() {
    return thumbnailUrl;
}

public void setThumbnailUrl(String thumbnailUrl) {
    this.thumbnailUrl = thumbnailUrl;
}

进行此更改会从 API 获得所需的返回结果