How to filter Spring JPA OneToMany relationships when retrieving the Parent entity?

Question

How can I filter my list of OneToMany relationships when fetching the parent entity?

Here's my usecase - I have an H2 database in which I'm tracking Products and their Shipment Options. One Product can have many shipment options. Most consumers care only only about the "best" shipment option per country (chosen by some convoluted business logic), while others want to see the best shipment option per country AND per model-number.

I've solved similar scenarios before using the Spring JPA @Where(clause = "childProperty='staticValue'"), however there are many dynamic models and the where clause seems to only support a single static filter.

Any advice / help would be appreciated, everything I've tried so far has failed.

Here are my domain models:

// In file com.my.company.entity.h2.model.Product.java
@Entity
@Table(name = "Product")
public class Product {
    @Id
    @Column(length = 10)
    private int productId;

    @LazyCollection(FALSE)
    @OneToMany(cascade = REMOVE)
    @NotFound(action = IGNORE)
    @JoinColumn(name = "productId", referencedColumnName = "productId", insertable = false, updatable = false)
    private List<ProductToPrimaryShipmentOption> primaryShipmentOptions = new ArrayList<>();
}

// In file com.my.company.entity.h2.model.relationships.ProductToPrimaryShipmentOption.java:
@Entity
@Table(name = "ProductToPrimaryShipmentOption")
public class ProductToPrimaryShipmentOption {
    @EmbeddedId
    private ProductAndShipmentOptionIds id;

    @Column(columnDefinition = "CHAR", length = 3)
    private String country;

    @Column(columnDefinition = "CHAR", length = 80)
    private String model;

    // This flag indicates this is the best shipment option across all the Product's models.
    @Column
    private boolean best;

    @ManyToOne
    @JoinColumn(name = "shipmentOptionId", referencedColumnName = "shipmentOptionId", insertable = false, updatable = false)
    private ShipmentOption shipmentOption;
}

// In file com.my.company.entity.h2.model.ShipmentOption.java:
@Entity
@Table(name = "ShipmentOption")
public class ShipmentOption {
    @Id
    @Column(columnDefinition = "CHAR", length = 29)
    private String id; // <= concatenated barcode and country

    @Column(columnDefinition = "CHAR", length = 80)
    private String model;

    // Additional Details...
}

I tried using Hibernate filters, but that didn't work - even when activating them in my current session, any repository queries for Products would give me back every Shipment Option, unfiltered.

// In file com.my.company.entity.h2.model.package-info.java:
@FilterDef(
        name = "bestFilter",
        parameters = @ParamDef(name = "best", type = "boolean")
)
@FilterDef(
        name = "modelFilter",
        parameters = @ParamDef(name = "model", type = "string")
)
package com.my.company.entity.h2.model;

import org.hibernate.annotations.FilterDef;
import org.hibernate.annotations.ParamDef;

// In file com.my.company.entity.h2.model.relationships.ProductToPrimaryShipmentOption.java:
@Entity
@Table(name = "ProductToPrimaryShipmentOption")
@Filter(
        name = "modelFilter",
        condition = "model = :model"
)
@Filter(
        name = "bestFilter",
        condition = "best = :best"
)
public class ProductToPrimaryShipmentOption {...}

// In class com.my.company.infrastructure.repository.config.H2Config.java:
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "h2EntityManagerFactory",
        transactionManagerRef = "h2TransactionManager",
        basePackages = {"com.my.company.infrastructure.repository.h2"})
public class H2Config {
    @Bean(name = "h2DataSource")
    @ConfigurationProperties(prefix = "spring.h2-datasource")
    public DataSource h2DataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "h2EntityManagerFactory")
    public EntityManagerFactory h2EntityManagerFactory(
            @Qualifier("h2DataSource") DataSource h2DataSource,
            @Value("${h2.hibernate.ddl-auto:create}") String ddlAuto,
            @Value("${h2.hibernate.dialect:org.hibernate.dialect.H2Dialect}") String dialect,
            @Value("${h2.show-sql:false}") boolean showSql,
            @Value("${h2.hibernate.generate-statistics:false}") boolean generateStatistics) {
        LocalContainerEntityManagerFactoryBean h2EntityManager = new LocalContainerEntityManagerFactoryBean();
        h2EntityManager.setDataSource(h2DataSource);
        h2EntityManager.setPersistenceUnitName("h2Unit");
        h2EntityManager.setPackagesToScan("com.my.company.entity.h2.model");

        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        vendorAdapter.setGenerateDdl(true);
        vendorAdapter.setShowSql(showSql);
        h2EntityManager.setJpaVendorAdapter(vendorAdapter);

        h2EntityManager.setJpaPropertyMap(new HashMap<>() {{
            put("hibernate.ddl-auto", ddlAuto);
            put("hibernate.dialect", dialect);
            put("hibernate.generate_statistics", generateStatistics);
        }});
        h2EntityManager.afterPropertiesSet();

        return h2EntityManager.getObject();
    }

    @Bean(name = "h2TransactionManager")
    public PlatformTransactionManager h2TransactionManager(@Qualifier("h2EntityManagerFactory") EntityManagerFactory h2EntityManagerFactory) {
        JpaTransactionManager h2TransactionManager = new JpaTransactionManager();
        h2TransactionManager.setEntityManagerFactory(h2EntityManagerFactory);
        return h2TransactionManager;
    }
}

// In class com.my.company.infrastructure.repository.service.ProductRepositoryService.java:
@Slf4j
@Repository
public class ProductRepositoryService {
    @PersistenceContext(unitName = "h2Unit")
    private EntityManager entityManager;

    public Flux<Product> findAllProducts() {
        return Flux.fromIterable(new JpaTableIterable<>((lastProduct, requestSize) -> {
            int id = lastProduct == null ? 0 : lastProduct.getId();
            return findNextProducts(id, requestSize);
        }, productRepository::count));
    }
    
    @Transactional(transactionManager = "h2TransactionManager")
    protected List<Product> findNextProducts(int id, int requestSize) {
        Session session = entityManager.unwrap(Session.class);
        Filter filter = session.enableFilter("bestFilter");
        filter.setParameter("best", true);
        List<Product> products = productRepository
                .findAllByIdGreaterThanOrderByIdAsc(id, PageRequest.of(0, requestSize));
        session.disableFilter("bestFilter");
        return products;
    } 
}

I tried using Spring Specifications, but again, I'm simply getting back every relationship, unfiltered :(

// In file com.my.company.infrastructure.repository.h2.ProductRepository.java:
@Repository
@Transactional(transactionManager = "h2TransactionManager")
public interface ProductRepository extends JpaRepository<Product, Integer>, JpaSpecificationExecutor<Product> {
    //...
}

// In file com.my.company.entity.h2.model.Product_.java:
@StaticMetamodel(Product.class)
public class Product_ {
    public static volatile SingularAttribute<Product, Integer> productId;
    public static volatile ListAttribute<Product, ProductToPrimaryShipmentOption> primaryShipmentOptions;
}

// In file com.my.company.entity.h2.model.specification.Specifications.java: 
public class Specifications {
    public static Specification<Product> nextProducts(int lastId) {
        return (root, query, builder) -> {
            Join<Product, ProductToPrimaryShipmentOption> join = root.join(Product_.primaryPackages, JoinType.LEFT);
            return builder.and(
                    builder.greaterThan(root.get(Product_.id), lastId),
                    builder.equal(join.get("best"), true);
        };
    }
}

// In file com.my.company.infrastructure.repository.service.ProductRepositoryService.java:
@Slf4j
@Repository
public class ProductRepositoryService {
    @Transactional(transactionManager = "h2TransactionManager")
    protected List<Product> findNextProducts(int id, int requestSize) {
        return productRepository
                .findAll(nextProducts(id), PageRequest.of(0, requestSize))
                .getContent();
    }
}

UPDATE:

Yet another attempt that failed was using the Hibernate @FilterJoinTable annotation. Still, I see the HQL queries printing in the logs without the filter clause and the collection contains all the unfiltered results.

Here's what I tried:

// In file com.my.company.entity.h2.model.Product.java
@Entity
@Table(name = "Product")
public class Product {
    @Id
    @Column(length = 10)
    private int productId;

    @LazyCollection(FALSE)
    @ManyToMany(cascade = REMOVE)
    @NotFound(action = IGNORE)
    @JoinTable(name = "ProductToPrimaryShipmentOption",
            joinColumns = @JoinColumn(name = "productId", insertable = false, updatable = false),
            inverseJoinColumns =  @JoinColumn(name = "shipmentOptionId", insertable = false, updatable = false)
    )
    @FilterJoinTable(
            name = "bestFilter",
            condition = "best = :best"
    )
    private List<ShipmentOption> filteredShipmentOptions = new ArrayList<>();
}