How to filter association entities across a mapping table using Spring Data JPA(Hibernate)?

Question

Is there a way to filter a soft deleted many-to-many association using @OneToMany and @ManyToOne, across an intermediate entity(mapping table)?

product and product_option_group are in N:M relation. I'm implementing the soft deletion using the disabled_datetime column and want to filter a collection of ProductOptionGroup from the Product entity. This post is using @ManyToMany and @Where to achieve this. I followed and it worked(disabled product_option_groups are filtered from product.getProductOptionGroups()). Note @Where on ProductOptionGroup class.

// `product` <-> `product-product_option_group` <-> `product_option_group`

@Entity
@Table(name = "product")
public class Product implements Serializable {
    ...

    @ManyToMany
    @JoinTable(name = "product-product_option_group",
            joinColumns = @JoinColumn(name = "product_id"),
            inverseJoinColumns = @JoinColumn(name = "product_option_group_id"))
    private final Set<ProductOptionGroup> productOptionGroups = new HashSet<>();

    ...
}

@Entity
@Table(name = "product_option_group")
@Where(clause = "disabled_datetime is null")
public class ProductOptionGroup implements Serializable {
    ...

    @Column(name = "disabled_datetime")
    private ZonedDateTime disabledDatetime;

    ...
}

but I want to use @OneToMany towards the product-product_option_group table, like this.

@Entity
@Table(name = "product")
public class Product implements Serializable {
    ...

    @OneToMany(mappedBy = "id.product")
    private final Set<ProductProductOptionGroup> productProductOptionGroups = new HashSet<>();

    ...
}

@Entity
@Table(name = "`product-product_option_group`")
public class ProductProductOptionGroup implements Serializable {
    @EmbeddedId
    private final ProductProductOptionGroupId id = new ProductProductOptionGroupId();

    ...
}

@Embeddable
public class ProductProductOptionGroupId implements Serializable {
    @ManyToOne(optional = false, fetch = FetchType.LAZY)
    @JoinColumn(name = "product_id", referencedColumnName = "id")
    private Product product;

    @ManyToOne(optional = false, fetch = FetchType.LAZY)
    @JoinColumn(name = "product_option_group_id", referencedColumnName = "id")
    @Where(clause = "disabled_datetime is null")
    private ProductOptionGroup productOptionGroup;
}

@Entity
@Table(name = "product_option_group")
@Where(clause = "disabled_datetime is null")
public class ProductOptionGroup implements Serializable {
    ...
}

But then the @Where annotations won't work anymore, so disabled product_option_groups are also selected from product.getProductProductOptionGroups().stream().map(o -> o.getId().getProductOptionGroup().collect(Collectors.toList()). How to solve this?

user2652379 · Answer 1 · 2021-06-29T01:34:29.663

I'd like to add workarounds while waiting for answers.

First, I can add a view as a filter.

CREATE VIEW `product-product_option_group-enabled` AS
SELECT *
FROM `product-product_option_group` ppog
JOIN product_option_group pog ON ppog.product_option_group_id = pog.id
    AND pog.disabled_datetime IS NULL;

@Entity
@Table(name = "`product-product_option_group-enabled`") // using the view as a filter
public class ProductProductOptionGroupEnabled implements Serializable {
    @EmbeddedId
    private final ProductProductOptionGroupId id = new ProductProductOptionGroupId();

    ...
}

Then using ProductProductOptionGroupEnabled for reading and ProductProductOptionGroup for any data modification might solve the problem. I don't like this.

Multiple collections of an entity. Have to be careful when modifying it.
2 different entities on the same table. Orchestrating these might be hard(equals() and hashCode()).
Adds a view. Better be careful when the schema is being changed.

Maybe I can utilize Updatable and Insertable Views, but I would still need ProductProductOptionGroup for selecting every row.

Second, I can filter the collection from Java.

@Entity
@Table(name = "product")
public class Product implements Serializable {
    ...

    @OneToMany(mappedBy = "id.product")
    private final Set<ProductProductOptionGroup> productProductOptionGroups = new HashSet<>();

    public Set<ProductProductOptionGroup> getProductProductOptionGroups() {
        return productProductOptionGroups.stream()
                .filter(o -> o.getId().getProductOptionGroup().getDisabledDatetime() == null)
                .collect(Collectors.toSet());
    }

    public Set<ProductProductOptionGroup> getProductProductOptionGroupsAll() {
        return productProductOptionGroups;
    }
    
    ...
}

Then use getProductProductOptionGroups() to get a filtered collection. I don't like this either.

It'll select every row from product-product_option_group table regardless of disabled_datetime.
Creates another collection. Have to be careful when modifying it.
javax.persistence.EntityNotFoundException: Unable to find with id xxx might happen. It can be solved by join fetching the trobling entity

score -1 · Answer 2 · answered Jul 05 '21 at 23:08

-1

The WHERE clause adds filtering capabilities to the FROM-SELECT structure. It is essential in any JPQL query that you retrieve selective objects from the database. in my opinion, you should check the correct implementation of @where see the following reference.

answered Jul 05 '21 at 23:08

carleetho

1
1

I'm using `org.hibernate.annotations.Where` for the `@Where` annotation. I couldn't find any other implementation. What's is the correct implementation of `@Where`? – user2652379 Jul 06 '21 at 01:57
Sadly, `org.hibernate.annotations.Where` only accepts SQL, not JPQL. – user2652379 Jul 06 '21 at 02:37

How to filter association entities across a mapping table using Spring Data JPA(Hibernate)?

2 Answers2