[SOLVED] Using database native functions in blaze persistence queries

Using database native functions in blaze persistence queries

    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()));

Solution

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)").