How to build a JPA Specification for a given SQL query with entities on Many to Many relationship?

Question

I have the following entities:

@Entity
@Table(name = "advertisement")
public class AdEntity {
   @Id
   @GeneratedValue(strategy = GenerationType.UUID)
   private String id;
   
   @ManyToMany(fetch = FetchType.LAZY, cascade = { CascadeType.PERSIST, CascadeType.MERGE })
   @JoinTable(name = "ad_tags", joinColumns = {@JoinColumn(name = "ad", foreignKey = @ForeignKey(name = "fk_at_ad")) }, inverseJoinColumns = {@JoinColumn(name = "tag", foreignKey = @ForeignKey(name = "fk_at_tag")) })
   private Set<TagEntity> tags;
.
.
.
}

@Entity
@Table(name = "tag")
public class TagEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    private String id;

    @ManyToMany(mappedBy = "tags")
    private Set<AdEntity> ads;
.
.
.
}

The idea is to create a filter search to retrieve all advertisements that contains all the tags provided, so an IN clause doesn't work since it will retrieve all advertisements that have at least one of the tags. I was able to create a sql query that retrieves what I need as follows:

select ad.id from advertisement ad
where ad.id in (select at.ad x
from ad_tags at
join tag t on t.id = at.tag
where t.name in ('tag1', 'tag2', 'tag3')
group by x
having count(tag) = 3)

So the problem that I have now is that I want to create a JPA Specification for this query

I have found some documentation to try to build part of the query, however the issue is that I have a ManyToMany relationship, so I'm not completely sure how to build the Specification, this is what I have, but it doesn't work:

public static Specification<AdEntity> filterTags(List<TagEntity> tags) {
    
  return (root, query, criteriaBuilder) -> {
            
    query.distinct(true);

            
    Subquery<TagEntity> tagSubquery = query.subquery(TagEntity.class);
    Root<TagEntity> tagRoot = tagSubquery.from(TagEntity.class);
            
    Expression<Collection<AdEntity>> adTags = tagRoot.get("ads");
            
    Expression<Long> countExpression = criteriaBuilder.count(root);
            
    query.multiselect(root.get("tags"), countExpression);
            
    query.groupBy(root.get("tags"));
            
    Predicate havingPredicate = criteriaBuilder.equal(countExpression, tags.size());
            
    query.having(havingPredicate);      
    tagSubquery.select(tagRoot);        
    tagSubquery.where(tagRoot.in(tags), criteriaBuilder.isMember(root, adTags));
    
            
    return criteriaBuilder.exists(tagSubquery);
  };
            
}

What should be the right way?

Answer 1

Victor.

The following specification:

public static Specification<AdEntity> byTagsAllIn(Set<String> tags) {
    return (root, query, builder) -> {
        query.groupBy(root.get(AdEntity_.ID));
        query.having(builder.equal(builder.count(root), tags.size()));
        return root.joinSet(AdEntity_.TAGS, JoinType.INNER).get(TagEntity_.TAG_NAME).in(tags);
    };
}

will generate pseudo SQL like this:

Hibernate: 
    select
        a1_0.id,
        a1_0.advertisement_name 
    from
        advertisements a1_0 
    join
        (ad_tags t1_0 
    join
        tags t1_1 
            on t1_1.id=t1_0.tag) 
                on a1_0.id=t1_0.ad 
        where
            t1_1.tag_name in (?,?,?) 
        group by
            a1_0.id 
        having
            count(a1_0.id)=?

Consider to use the hibernate-jpamodelgen to generate Static metamodels AdEntity_ and TagEntity_ on project build