Cakephp 3 select within a select complex query

Question

I have a mysql select statement which I ran directly in phpmyadmin. It works find. How do I write the mysql statement for this in cakephp 3. I appreciate the help.

The logic is this: table product_categories has product_id and category_id linking both the category table and the product table. Table product_options has product_id, size, color, qty. I am trying to display products based on a category, but I want to display only those with color RED.

select *from product_options, (select * FROM products Products 
left JOIN product_categories pc ON Products.id = pc.product_id
WHERE pc.category_id = 74)  as ptotals
left join product_options po ON ptotals.id = po.product_id
where po.color = 'RED'

Zbigniew Ledwoń Zbigniew Ledwoń · Accepted Answer · 2019-06-25T04:52:14

->matching() - is your friend here. Start with:

$query = $this->Products->find('all')
            ->matching('Categories')
            ->where([
                'ProductCategories.product_id' => $category['id'],
                'ProductCategories.category_id' => '74'];

or like they do it in docs https://book.cakephp.org/3.0/en/orm/retrieving-data-and-resultsets.html#filtering-by-associated-data-via-matching-and-joins

$query = $this->Products->find();
$query->matching('Categories', function ($q) {
    return $q->where(['Category.id' => '74']);
});

and build your query from there.

Cakephp 3 select within a select complex query

1 Answers