如何查询与一个用户在联接表中共享n个记录数量的用户列表

Question

我有一个实体（用户），它有一个曲目列表。我想使用一个用户的 id 查询与该用户共享 N 个曲目的所有用户。我使用 Spring Data JPA 作为我的数据层。 我已经有一个本机查询正在执行我想要的操作，但它返回用户的 id 列表。我想改为获取列表。 这是我的用户实体：

@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Table(name = "user")
public class User {
    @Id
    private String id;

    private String email;

    private String country;

    @ElementCollection
    @CollectionTable(name = "user_top_tracks",
            joinColumns = @JoinColumn(name = "user_id"))
    @Column(name = "track_id")
    @JsonIgnore
    private List<String> tracks;

    @ElementCollection
    @CollectionTable(name = "user_top_artists",
            joinColumns = @JoinColumn(name = "user_id"))
    @Column(name = "artist_id")
    @JsonIgnore
    private List<String> artists;

    @ElementCollection
    @CollectionTable(name = "matches",
            joinColumns = @JoinColumn(name = "user_id"))
    @Column(name = "match")
    @JsonIgnore
    private List<String> matches;

    @JsonIgnore
    @OneToOne(fetch = FetchType.LAZY, mappedBy = "user", cascade = CascadeType.ALL)
    @PrimaryKeyJoinColumn
    private Token token;
}

我现在的查询（返回列表）：

 @Query(value = "SELECT that.user_id " +
            "FROM user_top_tracks AS this INNER JOIN user_top_tracks " +
            "AS that ON that.user_id <> this.user_id " +
            "AND that.track_id = this.track_id WHERE this.user_id = ?1 " +
            "GROUP BY that.user_id HAVING COUNT(*) >= ?2", nativeQuery = true)
    List<String> getMatches(String userId, int matchingTracks);

数据库架构

对现在我编写了一个方法，用于遍历 ids 的返回列表，获取它们，然后将它们添加到用户列表中。它工作正常，但我想让它返回用户列表。

Answer 1

您只需要再添加一个加入

@Query(value = "SELECT usr.* " +
        "FROM user_top_tracks AS this INNER JOIN user_top_tracks " +
        "AS that ON that.user_id <> this.user_id " +
        "AND that.track_id = this.track_id "+
        "JOIN user as usr on  that.user_id = usr.id"
         "WHERE this.user_id = ?1 " +
        "GROUP BY that.user_id HAVING COUNT(*) >= ?2", User.class )
List<User> getMatches(String userId, int matchingTracks);

，但我认为您也可以使用JPQL

Answer 2

我最终做到了这一点并且工作正常。
如果我找到更好的解决方案，我会更新它。

    @Query(value ="SELECT * from user where id IN ("+
            "SELECT that.user_id " +
            "FROM user_top_tracks AS this INNER JOIN user_top_tracks " +
            "AS that ON that.user_id <> this.user_id " +
            "AND that.track_id = this.track_id WHERE this.user_id = ?1 " +
            "GROUP BY that.user_id HAVING COUNT(*) >= ?2 )", nativeQuery = true)
    List<User> getMatches(String userId, int matchingTracks);