1
    CriteriaBuilder<Tuple> cb = cbf.create(em, Tuple.class);
    cb.from(Pets.class);
    cb.select("petId");
    cb.orderByAsc("petId");
    cb.where("petId").inExpression("select pet_id from get_authorized_pet_id(5)");
    com.blazebit.persistence.PagedList<Tuple> rr =cb.page(1,10).getResultList();

The above query is trying to get all the Pets which are authorised for the account=5 user. get_authorized_pet_id is a native database query which does the checking and returns a list of pet_ids .

Blaze is not accepting this expressions . what am I missing here ?

The same thing is getting done through JPA Criteria though

criteriaBuilder.and(root.get(Pets_.PETS_ID)
                            .in(criteriaBuilder.function("SELECT pet_id from get_authorized_pet_id",
                                    List.class,
                                    criteriaBuilder.literal(getAuthorizedClient().getUser().getUserAccountId()));
Amit Mitra
  • 83
  • 6

1 Answers1

2

Oh wow, this works almost by accident :D

There is no out of the box support for entity functions yet, but you could register a custom JPQLFunction (see https://persistence.blazebit.com/documentation/1.6/core/manual/en_US/#custom-jpql-functions) or even a Hibernate SQLFunction that renders this SQL fragment and then use the function name under which you register it e.g. .inExpression("get_authorized_pet_id(5)").

Christian Beikov
  • 15,141
  • 2
  • 32
  • 58
  • `CriteriaBuilderConfiguration config = Criteria.getDefault(); JpqlFunctionGroup acl_func = new JpqlFunctionGroup("get_authorized_pet_id") ; acl_func.add("microsoft",new AclForPetsJpqlFunction()); config.registerFunction(acl_func);` `cb.where("petId").inExpressions("get_authorized_pet_id(5)");` Still says No function or macro found :( – Amit Mitra Jan 25 '23 at 18:13
  • btw after registering the function , It's get added to the registeredFunctions list of config object but is not found in CriteriaBuilderFactory.getRegisteredFunctions() list. Do we need to create the CriteriaBuilderFactory from the config object now ? – Amit Mitra Jan 25 '23 at 18:19
  • You can implement a `com.blazebit.persistence.spi.CriteriaBuilderConfigurationContributor` which is loaded by the Java service loader mechanism, within which you can register the function that is automatically picked up. – Christian Beikov Jan 26 '23 at 09:48