How to write this custom query in cakephp


#1

Hi, I want to convert this query according to CakePHP 3

SELECT p.*
FROM products p
WHERE EXISTS (
  SELECT 1
  FROM product_attributes a
  JOIN category_maps c ON c.product_id = a.product_id
  WHERE c.category_id = 1 and a.attribute_name  = 'color'
and a.attribute_value = 'red');

I already bake model for these table

category_maps
products
attributes


#2

Here is an example of using subqueries: https://book.cakephp.org/3.0/en/orm/query-builder.html#subqueries