RESOLVED: How to reference to a specific table on LEFT JOIN

Need Help
1

I need for an evaluation all orders for which a coupon was created for specific brands for a specific campaign. For this I have a Finder function that is defined in the OrderTable.

The following associations are defined as:
OrdersTable

$this->hasMany('OrdersItems', [
  'className' => 'OrdersItems',
  'foreignKey' => 'order_id'
]);
 $this->belongsTo('DiscountCoupons', [
   'className' => 'DiscountCoupons',
   'foreignKey' => 'discount_coupon_id'
]);

OrdersItemsTable

$this->belongsTo('Orders', [
    'foreignKey' => 'order_id',
]);
$this->hasOne('DiscountCoupons', [
    'foreignKey' => 'orders_items_id',
]);
$this->belongsTo('Products', [
    'foreignKey' => 'product_id',
]);

DiscountCouponsTable

$this->belongsTo('DiscountCampaigns', [
    'className' => 'DiscountCampaigns',
    'foreignKey' => 'discount_campaign_id'
]);
$this->belongsTo('OrdersItems', [
    'className' => 'OrdersItems',
    'foreignKey' => 'orders_items_id'
]);

The Finder Function

public function findExchangeBox(Query $query, array $options): Query
{
    return $query
        ->contain([
            'ExchangerightForOrders' => [
                'OrdersItems' => [
                    'Products' => [
                        'Brands',
                    ],
                ],
            ],
        ])
        ->innerJoinWith('OrdersItems')
        ->innerJoinWith('OrdersItems.Products')
        ->innerJoinWith('OrdersItems.Products.Brands', function ($q) {
            return $q->where([
                'Products.brand_id IN' => [7, 15, 16, 17, 22, 24, 35, 39, 40],
            ]);
        })
        ->innerJoinWith('OrdersItems.DiscountCoupons', function ($q) {
            return $q->where([
                'DiscountCoupons.orders_items_id = OrdersItems.id',
                'DiscountCoupons.discount_campaign_id' => 42,
                'DiscountCoupons.status' => 1,
            ]);
        })
        ->where([
            'OR' => [
                'AND' => [
                    'Orders.status NOT IN' => ['SM', 'CP', 'EX'],
                ],
                'Orders.is_exchange_box' => true,
                'Orders.exchangeright_for_order_id IS NOT NULL',
            ],
        ])
        ->group('Orders.id');
}

generates this SQL Query

SELECT
    several fields ...
FROM 
  orders Orders 
  INNER JOIN orders_items OrdersItems ON (
    Orders.id = (OrdersItems.order_id) 
    AND OrdersItems.deleted IS NULL
  ) 
  INNER JOIN products Products ON Products.id = (OrdersItems.product_id) 
  INNER JOIN brand Brands ON (
    Products.brand_id in (
      '7', '15', '16', '17', '22', '24', '35', 
      '39', '40'
    ) 
    AND Brands.id = (Products.brand_id)
  ) 
  LEFT JOIN discount_coupons DiscountCoupons ON DiscountCoupons.id = (Orders.discount_coupon_id) 
  LEFT JOIN shipping_methods ShippingMethods ON ShippingMethods.id = (Orders.shipping_method_id) 
  LEFT JOIN measures Measures ON Measures.id = (Orders.measure_id) 
  LEFT JOIN orders ExchangerightForOrders ON ExchangerightForOrders.id = (
    Orders.exchangeright_for_order_id
  ) 
WHERE 
  (
    Orders.status not in ('SM', 'CP', 'EX') 
    OR Orders.is_exchange_box = 1 
    OR Orders.exchangeright_for_order_id IS NOT NULL
  ) 
GROUP BY 
  Orders.id 
ORDER BY 
  Orders.id desc

but i need to change the reference and LEFT JOIN at

LEFT JOIN discount_coupons DiscountCoupons ON DiscountCoupons.id = (Orders.discount_coupon_id)

to

INNER JOIN discount_coupons DiscountCoupons ON (
    OrdersItems.id = DiscountCoupons.orders_items_id 
    AND DiscountCoupons.discount_campaign_id = 42
    AND DiscountCoupons.status = 1
)

so even if i replace the innerJoinWith for “OrdersItems.DiscountCoupons” with

->innerJoin(['DiscountCoupons' => 'discount_coupons'], 'DiscountCoupons.orders_items_id = OrdersItems.id')

it does not give me the desired Query
So what i’m dojng wrong here?

2

Got it.
I had to define a second association with joinType and use this one with innerJoinWith

OrdersItemsTable

$this->hasOne('DiscountCouponsFilter', [
  'className' => 'DiscountCoupons',
  'foreignKey' => 'orders_items_id',
  'joinType' => 'INNER',
]);