Cakephp3 join 3 tables with no direct relationships

[limpeh]

hello World

need advises from World about this question,

there are 3 tables, orders, order_products and products,

their tables are as follows

  OrderProducts Table
  $this->belongsTo('Orders', [
        'foreignKey' => 'order_id',
        'joinType' => 'INNER',]);

  $this->belongsTo('Products', [
        'foreignKey' => 'product_id',
        'joinType' => 'INNER',]);


   Orders Table
    $this->hasMany('OrderProducts', [
        'foreignKey' => 'order_id',]);


  Products Table
    $this->hasMany('OrderProducts', [
        'foreignKey' => 'product_id',]);

order_products has relationship with orders and products,

orders and products has no relationship

the arrays are as follows

 orders [ { id : ‘1’, customer_name: ‘superman’}, {id:‘2’, customer_name: ‘captain america’}, ]; 
 
 order_products [ { id:'1', product_name: ‘phone’, 'product_id': '1', 'order_id:'1' } , 
 { id:'2', product_name:’'apple', product_id': '2', 'order_id:'2' }, ]; 
 
 products [ { id : ‘1’, product_image: ‘phone.jpg’}, { id:‘2’, product_image: ‘apple.jpg’}, ]; 

earlier, i managed to let orders and order details join in the query, the code as follows

$orders = $this->Orders->find()->contain(['OrderProducts']);
$this->set(compact('orders');

the array combines becomes

orders [ 

{ id : ‘1’, customer_name: ‘superman’,  order_products [ { id:'1', product_name: ‘phone’, 
'product_id': '1', 'order_id:'1' } ]  } ,

{ id:‘2’, customer_name: ‘captain america’},  order_products  [ { id: '2', product_name: ’'apple', 
 product_id': '2', 'order_id: '2' } ] },
 ] , 

is there a way to join all tables to make it become like the following where product is directly joined into the orders together like the following?

orders [ 

{ id : ‘1’, customer_name: ‘superman’,  order_products [ { id:'1', product_name: ‘phone’, 
'product_id': '1', 'order_id:'1' ,  product:[  { id : ‘1’, product_image: ‘phone.jpg’} ]  } ]  } ,

{ id:‘2’, customer_name: ‘captain america’},  order_products  [ { id: '2', product_name: ’'apple', 
 product_id': '2', 'order_id: '2' , product:[ id:‘2’, product_image: ‘apple.jpg’] } ] },
 ] , 

have experimented with join, but not successful

what is trying to achieve is to foreach the Orders where the product image, customer name and product name is shown on a table

yesterday thanks for your help, the foreach of orders is successful, now is how combine another table into the foreach.

thank you very much

[dirk]

@limpeh

$orders = $this->Orders->find()->contain(['OrderProducts']);

did you already try:

$orders = $this->Orders->find()->contain([‘OrderProducts’=>[‘Orders’,‘Products’]);

[Erwane]

I think you should user belongsToMany association.

// Orders
$this->belongsToMany(‘Products’, [‘through’ => ‘OrderProducts’])

https://book.cakephp.org/3/en/orm/associations.html#using-the-through-option

Keep others relations.

Or you can use ->contain([‘OrderProducts.Products’])

[limpeh]

hello Erwane,

thanks for your message, tried belongsToMany not successful,
i tried ->contain([‘OrderProducts.Products’]), Yes, it works!

thank you very much

[limpeh]

@dirk

$orders = $this->Orders->find()->contain([‘OrderProducts’=>[‘Orders’,‘Products’]);

hello Dirk,

i tried $orders = $this->Orders->find()->contain([‘OrderProducts’=>[‘Products’]);

it works, and thank you very much for your message reply,

[Erwane]

Both are equals

$orders = $this->Orders->find()->contain([‘OrderProducts’=>[‘Products’]);

$orders = $this->Orders->find()->contain([‘OrderProducts.Products’]);

The advantage of the first is the options you can pass to associations:

$orders = $this->Orders->find()->contain([
    'OrderProducts' => [
        'fields' => ['order_id', 'product_id', '...'],
        'Products' => [
            'fields' => ['id', 'title', 'price'],
        ],
    ],
]);

[limpeh]

Dear Erwane,

oh i see, that’s amazing!!!

i give a few experiments on the homework, give a few varieties

thank you very much