Adding parameters to a @Subselect Entity using @Filter and @FilterDef

Question

I've been attempting to accomplish the same thing mentioned here without success: Bind parameters to the sql request inside a @Subselect annotation in Spring boot?

I am trying to limit the results of this subselect to objects in table2 that have a specific value for object_type

Here's the entity I am using:

@Entity
@Immutable
@Subselect("select t2.object_id as objectId, count(*) as count\n"
    + "\tFROM table1 t1\n"
    + "\tINNER JOIN table2 t2 on t1.primary_id = t2.token_id\n"
    + "\tGROUP BY t2.object_id")
@FilterDef(
    name = "objectTypeIdStrFilter",
    parameters = @ParamDef(name = "objectTypeIdStr", type = "int")
)
@Filter(
    name = "objectTypeIdStrFilter",
    condition = "t2.object_type = :objectTypeIdStr"
)
public class ObjectCountView {
    @Id
    Integer objectId;

    @Column
    Integer count;
}

I am then trying to do something like this to enable that filter through the EntityManager:

final CriteriaBuilder cb = entityManager.getCriteriaBuilder();
final CriteriaQuery<Tuple> criteriaQuery = cb.createTupleQuery();
// Omitting code here where we build out the query
Session session = entityManager.unwrap(Session.class);
session.enableFilter("objectTypeIdStrFilter").setParameter("objectTypeIdStr", CONSTANT_VALUE_HERE).validate();
final Query query = entityManager.createQuery(criteriaQuery);
final List<Tuple> results = query.getResultList();

However when the query is executed I don't get any WHERE clause being added to the SQL statement applying that filter.

Answer 1

It looks like the solution is adding the filter variable inside of the @Subselect, instead of adding the @Filter annotation:

@Entity
@Immutable
@Subselect("select t2.object_id as objectId, count(*) as count\n"
    + "\tFROM table1 t1\n"
    + "\tINNER JOIN table2 t2 on t1.primary_id = t2.token_id\n"
    + "\tWHERE t2.object_type = :objectTypeIdStrFilter.objectTypeIdStr\n"
    + "\tGROUP BY t2.object_id")
@FilterDef(
    name = "objectTypeIdStrFilter",
    parameters = @ParamDef(name = "objectTypeIdStr", type = "int")
)
public class ObjectCountView {
    @Id
    Integer objectId;

    @Column
    Integer count;
}