左连接Fetch在多个条件下不工作

Question

我正在编写这个查询：

SELECT distinct g FROM Group g left join fetch g.groupPlaylists as gp on gp.playEndDay >= CURRENT_DATE and gp.status <>:status where g.zoneId= :zoneId and g.status <>:status

但它抛出了一个异常：

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: with-clause not allowed on fetched associations; use filters [SELECT distinct g FROM com.instoreradio.model.Group g left join fetch g.groupPlaylists as gp on gp.playEndDay >= CURRENT_DATE and gp.status <>:status where g.zoneId= :zoneId and g.status <>:status]

这个问题有什么解决方案吗？

它在没有fetch的情况下工作，但返回了错误的GroupPlaylist

这是我的实体映射：@Entity @Table(name = "groups") @DynamicUpdate公共类组扩展BaseModel {

private static final long serialVersionUID = -4864520840627628591L;

private String storeIds;

private Long regionId;

private Long zoneId;

private Long groupManagerId;

private String groupName;

private String groupComment;

@JsonBackReference
//@JsonIgnore
@ManyToOne(cascade = CascadeType.ALL,fetch=FetchType.LAZY)
@JoinColumn(name = "company_id")
private Companies companies;

@JsonManagedReference
@OneToMany(cascade=CascadeType.ALL,mappedBy="group",orphanRemoval=true)
@Fetch(FetchMode.JOIN)
private Set<GroupPlaylist> groupPlaylists=new HashSet<>();

}

@Entity
@Table(name = "group_playlist")
@DynamicUpdate
public class GroupPlaylist extends JsonType {

/**
 * 
 */
private static final long serialVersionUID = -982336326147846219L;

@JsonBackReference
@ManyToOne(cascade = CascadeType.DETACH, fetch = FetchType.LAZY)
@Fetch(FetchMode.JOIN)
@JoinColumn(name = "group_id")
private Group group;

private Long groupCompanyId;

private String playlistTitle;

private Date playStartDay;

private Date playEndDay;

private Integer totalSongs;

private BooleanEnum isDefaultPlaylist;

@JsonManagedReference
@OneToMany(mappedBy = "groupPlaylist", fetch = FetchType.LAZY, 
orphanRemoval = true)
@Fetch(FetchMode.JOIN)
private Set<TimeSlot> timeSlots = new HashSet<>();

@JsonManagedReference
@OneToMany(mappedBy = "groupPlaylist", fetch = FetchType.LAZY, 
orphanRemoval = true)
@Fetch(FetchMode.JOIN)
private Set<StorePlaylist> storePlayLists = new HashSet<>();

@JsonManagedReference
@OneToMany(mappedBy = "groupPlaylist", fetch = FetchType.LAZY, 
orphanRemoval = true)
@Fetch(FetchMode.JOIN)
private Set<GroupPlaylistSongs> groupPlaylistSongs = new HashSet<>();

Answer 1

不能将on子句用于映射中定义的条件之外的其他条件。

查询必须使用where子句中的条件，如下所示：

SELECT distinct g 
FROM Group g 
left join fetch g.groupPlaylists as gp 
where gp.playEndDay >= CURRENT_DATE 
  and gp.status <>:status 
  and g.zoneId= :zoneId 
  and g.status <>:status