Как составить запрос с помощью Criteriq Query?

Question

Имеются таблицы:

gift_certificate (подарочный сертификат)
tag (тег)
gift_certificate_to_tag_relation (для связи многие-ко-многим между подарочными сертификатами и тегами, содержит ID)

Как составить запрос с помощью Criteria Query на поиск подарочных сертификатов, которые имеют определенный набор тегов. Теги передаются в Set<String> tagNames.

Java сущности:

GiftCertificate.class:

private Long id;
@Column(nullable = false, unique = true)
private String name;

@Column(nullable = false)
private String description;

@Column(nullable = false)
private BigDecimal price;

@Column(nullable = false)
private Integer duration;

@Column(nullable = false)
private LocalDateTime createDate;

@Column(nullable = false)
private LocalDateTime lastUpdateDate;

Tag.class:

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

@Column(nullable = false, unique = true)
private String name;

GiftCertificateToTagRelation.class:

@Id
@ManyToOne
@JoinColumn(name = DatabaseColumnName.GIFT_CERTIFICATE_ID)
private GiftCertificate giftCertificate;

@Id
@ManyToOne
@JoinColumn(name = DatabaseColumnName.TAG_ID)
private Tag tag;

Что я пробую:

Set<String> tagNames = ...

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<GiftCertificate> criteriaQuery = builder.createQuery(GiftCertificate.class);
Root<GiftCertificate> root = criteriaQuery.from(GiftCertificate.class);
Root<GiftCertificateToTagRelation> relationRoot = criteriaQuery.from(GiftCertificateToTagRelation.class);
Join<GiftCertificateToTagRelation, Tag> tagJoin = relationRoot.join("tag");
Predicate condition = tagJoin.get("name").in(tagNames);
criteriaQuery.where(condition)
    .groupBy(root)
    .having(builder.count(root).in(tagNames.size()));

В результате данного запроса Hibernate формирует SQL запрос (в случае передачи двух имен тегов):

select *
from gift_certificate gc
         cross join gift_certificate_to_tag_relation relation
         inner join tag tag3_ on relation.tag_id = tag3_.id
where tag3_.name in ('tag1', 'tag2')
group by gc.id
having count(gc.id) in (2)
order by gc.id;

Но данный SQL код возвращает пустой результат. Я обнаружил, что если в строке cross join добавить условие on gc.id = relation.gift_certificate_id, то в результате получается желаемый результат:

select *
from gift_certificate gc
         cross join gift_certificate_to_tag_relation relation on gc.id = relation.gift_certificate_id
         inner join tag tag3_ on relation.tag_id = tag3_.id
where tag3_.name in ('tag1', 'tag2')
group by gc.id
having count(gc.id) in (2)
order by gc.id;

Я понимаю, что условие ON вызывается на объекте Join<?, ?>. Но как тогда c с помощью Criteria Query задать это словие ON (on gc.id = relation.gift_certificate_id)?

Answer 1

Так как результат преобразования выглядит следущим образом:

select *
from gift_certificate gc
         cross join gift_certificate_to_tag_relation relation
         inner join tag tag3_ on relation.tag_id = tag3_.id
where tag3_.name in ('tag1', 'tag2')
group by gc.id
having count(gc.id) in (2)
order by gc.id;

Здесь в строке cross join gift_certificate_to_tag_relation relation не хватает условия ON, при добавлении которого строка примет вид:

cross join gift_certificate_to_tag_relation relation on gc.id = relation.gift_certificate_id

Для решения данной проблемы с помощью CriteriaQuery необходимо добавить данное условие в строку inner join tag tag3_ on relation.tag_id = tag3_.id, тогда финальный SQL запрос будет выглядеть следующим образом:

select *
from gift_certificate gc
         cross join gift_certificate_to_tag_relation relation 
         inner join tag tag3_ on relation.tag_id = tag3_.id AND gc.id = relation.gift_certificate_id
where tag3_.name in ('tag1', 'tag2')
group by gc.id
having count(gc.id) in (2)
order by gc.id;

Переводим это на CriteriaQuery и получаем:

Root<GiftCertificateToTagRelation> relation = query.from(GiftCertificateToTagRelation.class);
Join<GiftCertificateToTagRelation, Tag> rel = relation.join(ParameterName.TAG);
Predicate on = builder.equal(root, relation.get(ParameterName.GIFT_CERTIFICATE));
rel.on(on); // inner join tag t on rel.tag_id = t.id and (gc.id = rel.gift_certificate_id)
Predicate condition = rel.get(ParameterName.NAME).in(tagNames);

БЛОГ НА HUSL

Как составить запрос с помощью Criteriq Query?

Ответы (1 шт):