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

Question

Cake PHP 3.4 running on MAMP. I have the 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, i followed the bookmarker tutorial of the cookbok (adapted to my scenario).The logic would be to have a product that has many ingredients. This seems to work fine after baking.

What i want to achieve is: on the product view for a specific product, I want to display the product fields (related to the id of the product), but also the ingredients. With my code as it is, I'm displaying the product fields (which is ok), but only the related ids of the 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 the query I would run is:

SELECT products.name as prod, ingredients.name as ingr, ingredients_products.qty as qty 
FROM 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 I found on the Query builder page: https://book.cakephp.org/3.0/en/orm/query-builder.html

but I can't find anything that would let me do a query like that. Does anyone know how can this be achieved?

Thanks!

score 0 · Answer 1 · answered Apr 07 '17 at 15:13

0

I'm not sure I completely follow what you want to achieve, but if you just want all the ingredients for a particular product you can contain the Ingredients for the product like this:-

$product = $this->Products->get($id, [
    'contain' => ['Ingredients']
]);

However, if you wanted to achieve the SQL query described in your question then you could define the model for the joins table and then query that. So you'd have a IngredientsProductsTable model:-

class IngredientsProductsTable extends Table
{
    public function initialize(array $config)
    {
        $this->belongsTo('Ingredients');
        $this->belongsTo('Products');
    }
}

Then in your controller:-

$this->loadModel('IngredientsProducts');
$data = $this->IngredientsProducts->find('all')
    ->contain(['Ingredients', 'Products']);

answered Apr 07 '17 at 15:13

drmonkeyninja

8,490
4
31
59

Hi! thank you for your answer. Sorry if I wasn't clear with my question. What I'm trying to do is that when I open the view for a specific product, in that view, I want to display all the ingredients related to that product as per the joiner table ingredients_products. I tried defining the model IngredientsProductsTable and now I get all of the products. How could I modify the $this->loadModel('IngredientsProducts'); $data = $this->IngredientsProducts->find('all') ->contain(['Ingredients', 'Products']); to look for a specific product id? thanks! – Tzi Apr 07 '17 at 16:19
In that case you just need to use my first example and `contain` the Ingredients. That will use the joins table to retrieve the relevant ingredients for the product being returned. – drmonkeyninja Apr 07 '17 at 16:30
Great! but it's strange, when using the first example I get a "Products is not associated with Ingredients" error. – Tzi Apr 07 '17 at 16:45

score 0 · Accepted Answer · answered Apr 07 '17 at 21:02

This sounds a lot like a belongs to and has many relationship.

class IngredientsTable extends Table
{
    public function initialize(array $config)
    {
        // Use through option because it looks like you 
        // have additional data on your 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');
    }
}

This did the trick. Thank you! I've read about the belongsToMany but didn't knew how to implement it on this example. — Tzi, Apr 08 '17 at 07:11

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

2 Answers2