mysql - Cake PHP 3.4 - how to add 2 inner joins on the same query? -

cake php 3.4 running on mamp. have following scenario:

sql tables

table `ingredients` (   `id` int(11) not null,   `name` varchar(255) not null,   `category_id` int(11) not null,   `measure_id` int(11) not null ) engine=innodb default charset=utf8;   table products table `products` (   `id` int(11) not null,   `name` varchar(255) not null,   `retail_price` float not null,   `best_before` int(11) not null,   `comments` text not null ) engine=innodb default charset=utf8;    table `ingredients_products` (   `ingredient_id` int(11) not null,   `product_id` int(11) not null,   `qty` double not null ) engine=innodb default charset=utf8; 

table models:

class ingredientstable extends table {      public function initialize(array $config)     {         parent::initialize($config);          $this->settable('ingredients');         $this->setdisplayfield('name');         $this->setprimarykey('id');          $this->hasmany('purchases', [             'foreignkey' => 'ingredient_id'         ]);         $this->hasmany('ingredientsproducts', [             'foreignkey' => 'ingredient_id'         ]);     }  class productstable extends table {      public function initialize(array $config)     {         parent::initialize($config);          $this->settable('products');         $this->setdisplayfield('name');         $this->setprimarykey('id');          $this->hasmany('ingredientsproducts', [             'foreignkey' => 'product_id'         ]);     } 

on cakephp, followed bookmarker tutorial of cookbok (adapted scenario).the logic have product has many ingredients. seems work fine after baking.

what want achieve is: on product view specific product, want display product fields (related id of product), ingredients. code is, i'm displaying product fields (which ok), related ids of joiner table ingredients_products.

public function view($id = null)     {         $product = $this->products->get($id, [             'contain' => ['ingredientsproducts']         ]);          $this->set('product', $product);         $this->set('_serialize', ['product']);        } 

in sql query run is:

select products.name prod, ingredients.name ingr, ingredients_products.qty qty  ingredients_products     inner join products         on ingredients_products.product_id = products.id     inner join ingredients          on ingredients_products.ingredient_id = ingredients.id 

i've been trying things found on query builder page: https://book.cakephp.org/3.0/en/orm/query-builder.html

but can't find let me query that. know how can achieved?

thanks!

this sounds lot belongs , has many relationship.

class ingredientstable extends table {     public function initialize(array $config)     {         // use through option because looks          // have additional data on ingredientsproducts table         $this->belongstomany('products', [             'through' => 'ingredientsproducts',         ]);     } }  class productstable extends table {     public function initialize(array $config)     {         $this->belongstomany('ingredients', [             'through' => 'ingredientsproducts',         ]);     }  }  class ingredientsproductstable extends table {     public function initialize(array $config)     {         $this->belongsto('ingredients');         $this->belongsto('products');     } }