Как с помощью Criteria api написать запрос с join и вложенным запросом в него?
Есть запрос:
SELECT *
FROM `recipes`
INNER JOIN (
SELECT `recipe_id`
FROM `recipes_ingredients`
WHERE `ingredient_id` IN :ingredients
GROUP BY `recipe_id`
HAVING COUNT(ingredient_id) >= 1) AS `ingredients_result`
ON `recipes`.`id` = `ingredients_result`.`recipe_id`
ORDER BY `id` DESC
Не понимаю как с помощью Criteria можно вложить запрос в join.
var criteriaBuilder = entityManager.getCriteriaBuilder();
var query = criteriaBuilder.createQuery(Recipe.class);
var root = query.from(Recipe.class);
query.select(root);
if(!ingredients.isEmpty()) {
var subquery = query.subquery(Long.class);
var subRoot = subquery.from(RecipeIngredient.class);
subquery.select(subRoot.get("recipe").get("id"))
.where(criteriaBuilder.in(subRoot.get("ingredient").get("id")).value(ingredients))
.groupBy(subRoot.get("recipe").get("id"))
.having(criteriaBuilder.ge(criteriaBuilder.count(subRoot.get("ingredient").get("id")), 1L));
query.where(criteriaBuilder.in(root.get("id")).value(subquery));
}
query.orderBy(criteriaBuilder.desc(root.get("id")));
var typedQuery = entityManager.createQuery(query)
.setFirstResult(page)
.setMaxResults(size);
log.info("SQL : {}", typedQuery.unwrap(org.hibernate.query.Query.class).getQueryString());