QueryDsl - Exclude results based on nested array contents

Question

I'm trying to get all Posts which don't contain certain category using QueryDsl

My models are defined as:

Post

@QueryEntity
@Table(name = "posts")
public class PostEntity implements {
    @Id
    @Column(name = "id")
    private String id;

    @OneToMany
    @JoinTable(
            name = "post_categories",
            joinColumns = @JoinColumn(name = "post_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(name = "category_id", referencedColumnName = "id")
    )
    private List<CategoryEntity> categories;
}

Category

@QueryEntity
@Table(name = "categories")
public class CategoryEntity {
    @Id
    @Column
    private String id;

}

(some Lombok annotations omitted for brevity)

The two are related through post_categories join table to tag posts with categories.

I've tried using the query similar to this one, to exclude posts categorised as news:

var query = QPostEntity
                .postEntity
                .categories.any().id.notIn("news");

However that still returns posts in that category - only way I got it to work properly is to include all post categories in notIn statement.

Question: How do I query for Posts which don't contain specific category?

Update #1

Seems the query above generates subquery similar to

where exists(
    select 1 from post_categories where category_id not in ('news')
    )

which also includes all the posts with other categories. I found the following query does produce correct results (not moved before exists statement):

where not exists(
    select 1 from post_categories where category_id in ('news')
    )

Which can be done by rewriting querydsl as:

.categories.any().id.in("news").not();

However that seems to be very confusing. Any better way of doing it?

Something's not clear... Shouldn't news be a value for "name" column in category? — Lorelorelore, Jun 16 '21 at 13:03
I think it's correct @Lorelorelore - the `news` is the primary key in this case, it's a string. I'll remove the `name` from the example to make it more obvious. — Alex, Jun 16 '21 at 13:20

score 2 · Answer 1 · answered Jun 16 '21 at 14:11

I would try to solve this with subqueries. Can you try the following?

SubQueryExpression<String> subquery = JPAExpressions.select(QCategoryEntity.categoryEntity.id)
                .from(QCategoryEntity.categoryEntity)
                .where(CategoryEntity.categoryEntity.eq("news"));

        return new JPAQueryFactory(em)
                .select(QPostEntity.postEntity)
                .from(QPostEntity.postEntity)
                .innerJoin(QPostEntity.postEntity.categories)
                .where(QCategoryEntity.categoryEntity.id.notIn(subquery));

Probably you are not using the JPAQueryFactory... if not, could you share how you are actually performing the query?

QueryDsl - Exclude results based on nested array contents

1 Answers1