左联接 获取 在多个条件下不起作用

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]

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

它无需获取即可工作，但返回了错误的GroupPlaylist

下面是我的实体映射： @Entity @Table(名称 = "组"( @DynamicUpdate 公共类组扩展基本模型 {

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