HQL用于告知某个值是否在连接表中

Question

我有两个实体表(Scenario和Tag)和一个表示多对多关系的连接表。

select count(*)
from scenarios
where scenarios.id not in (
    select tag_to_scenarios.scenarioid
    from tag_to_scenarios
    join tags ON tags.id = tag_to_scenarios.tagid
    where (
        exists( 
            select concat(group_concat(tags.tagname), ',') as tag_list
            from scenarios as sub_scenarios
            left outer join tag_to_scenarios ON tag_to_scenarios.scenarioid = sub_scenarios.id
            join tags ON tags.id = tag_to_scenarios.tagid
            where scenarios.id = sub_scenarios.id
            group by scenarios.id
            having (position('@regression,' in tag_list) = 0))
        )
    )
 );

以下是正在使用的模型的相关部分：

@Entity
@Table(name="tags")
public class Tag {

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

public void setId(int id) {
    this.id = id;
}

public int getId() {
    return id;
}

@NotNull
@Column(name = "tagname")
private String tagName;

public void setTagName(String tagName) {
    this.tagName = tagName;
}

public String getTagName() {
    return tagName;
}

}

@Entity

@Table(name=“场景”)公共类场景{

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

public int getId() {
    return this.id;
}

public void setId(int id) {
    this.id = id;
}
...
@ManyToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
@JoinTable(name = "tag_to_scenarios",
    joinColumns = {@JoinColumn(name = "scenarioid", nullable = false, updatable = false)},
    inverseJoinColumns = {@JoinColumn(name = "tagid", nullable = false, updatable = false)},
    uniqueConstraints = @UniqueConstraint(name = "scenarioid", columnNames = {"tagid"})
)
private Set<Tag> tags = new HashSet<Tag>(0);

public Set<Tag>  getTags() {
    return this.tags;
}

public void setTags(Set<Tag> tags) {
    this.tags = tags;
}
}

我需要将这条SQL语句转换为HQL，因为我的模型中没有连接表。

Answer 1

以下HQL可以帮助您完成此任务：

  SELECT s from Scenario s
    JOIN s.tags t 
   WHERE t.name = :tag 
GROUP BY s 
  HAVING count(t) = 1

学分来自以下站点：

http://www.sergiy.ca/how-to-write-many-to-many-search-queries-in-mysql-and-hibernate/

Answer 2

老实说，我不理解您的SQL查询，但是如果您想要做的就是获取具有给定标志的场景，那么下面的HQL查询应该就足够了：

SELECT s.id FROM Scenario s JOIN tags t WHERE t.tagName = :tagName