How to query a list of users that share N number of records in a join table with one user-CodePudding

I have an entity (user) that has a List of tracks. I'd like to query all users that share N number of tracks with one user using his id. I'm using Spring Data JPA for my data layer. I already have a native query that is doing what I want but it's returning a List of ids for the users. I would like to get List instead. This is my user entity:

@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;
}

The query I have right now (returning List):

 @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);

Database schema

Right now I wrote a method that goes over the return list of ids, fetches them and then adds them to a List of users. It's working fine but I'd like to just make it return a List of users instead.

CodePudding user response：

You just need to add one more join

@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);

But I think you can also use JPQL

CodePudding user response：

I ended up doing this and it's working fine. If i find any better solutions i'll update it.

    @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);