Spring Boot Data JPA Querying an Entity with a Collection which has @ManyToMany

Question

I have an entity named Club which has a @ManytoMany relationship with an entity named Type. This type has various tags for clubs, such as whether it's a football club or else a swimming club likewise. So a club could have multiple types also a certain type could be shared among clubs. When I want to select a club I also want to display the tags associated with the club accessing the type collection. How I am suppose to achieve this?

@Data
@Entity
@EntityListeners(AuditingEntityListener.class)
public class Club {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    @Column(length = 20)
    private String name;

    @Column(length = 20)
    private String shortName;

    @Column(length = 80)
    private String description;

    @Column(length = 50)
    private String email;

    private boolean status;

    @Lob
    private Blob logo;

    @Lob
    private Blob cover;

    @Column(length = 50)
    private String website;

    private double longitude;

    private double latitude;

    @Column(length = 20)
    private String registrationId;

    @CreatedDate
    @Temporal(TemporalType.DATE)
    @Column(nullable = false, updatable = false)
    private Date registrationDate;

    @Temporal(TemporalType.DATE)
    private Date lastActiveDate;

    @OneToMany(mappedBy = "club")
    private List<Favourite> favourites;

    @ManyToMany
    private List<Type> type;

    @OneToOne(fetch = FetchType.LAZY)
    private Address address;

}
@Entity
@Data
@EntityListeners(AuditingEntityListener.class)
public class Type {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    @Column(length = 10)
    private String tag;

    @ManyToMany(mappedBy = "type")
    private List<User> user;

    @ManyToMany(mappedBy = "type")
    private List<Club> club;

    @CreatedDate
    @Temporal(TemporalType.DATE)
    @Column(nullable = false, updatable = false)
    private Date createdDate;

}

in the ClubRespository.java I am trying to access the type collection's tag variable like this.

@Repository
public interface ClubRepository extends JpaRepository<Club, Integer> {

    @Query("SELECT new com.service.payload.response.ClubSend(c.id, c.name, c.type.tag, c.shorName, c.description, c.email, c.logo, c.cover, c.longitude, c.latitude, c.address) FROM Club c JOIN c.type WHERE c.id=c.type.club.id")
    List<Club> findAllClubs();

}

ClubSend is a DTO class I created to map the fields I want to present, however when I am doing so, I am receiving this error.

Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryException: illegal attempt to dereference collection [club0_.id.type] with element property reference [tag] [SELECT new com.vayemo.mysponsor.service.payload.response.ClubSend(c.id, c.name, c.type.tag, c.shorName, c.description, c.email, c.logo, c.cover, c.longitude, c.latitude, c.address) FROM com.vayemo.mysponsor.service.model.Club c JOIN c.type WHERE c.id=c.type.club.id]

Caused by: org.hibernate.QueryException: illegal attempt to dereference collection [club0_.id.type] with element property reference [tag] [SELECT new com.vayemo.mysponsor.service.payload.response.ClubSend(c.id, c.name, c.type.tag, c.shorName, c.description, c.email, c.logo, c.cover, c.longitude, c.latitude, c.address) FROM com.vayemo.mysponsor.service.model.Club c JOIN c.type WHERE c.id=c.type.club.id]

Caused by: org.hibernate.QueryException: illegal attempt to dereference collection [club0_.id.type] with element property reference [tag]

Answer 1

Your problem is that c.type in the Query is a List and therefore c.type.club cannot be dereferenced. If you want to access the type object, you need to provide an alias to it during the join (like this ... FROM Club c JOIN c.type t ...), but as far as I know, your where clause is implicit in the declaration of the @ManyToMany relation. If you want more details check this Stackoverflow answer.