cakephp: targetForeignKey in belongsToMany doesn't have effect, defaults to target model name

Description

Still not sure this is actually a bug but can’t get any help anywhere. this association

       $this->belongsToMany('Bonus', [
            'foreignKey' => 'product_id',
            'joinTable' => 'products_bonuses',
            'className' => 'Products',
            'targetForeignKey' => 'bonus_id',
        ]);

generates this query

SELECT ProductsBonuses.id         AS Bonus_CJoin__id,
       ProductsBonuses.product_id AS Bonus_CJoin__product_id,
       ProductsBonuses.bonus_id   AS Bonus_CJoin__bonus_id,
       Bonus.id                   AS Bonus__id
FROM products Bonus
         INNER JOIN products_bonuses ProductsBonuses ON Bonus.id = ProductsBonuses.product_id
WHERE ProductsBonuses.product_id in (57, 58);

which gives me wrong results. When I correct it to use bonus_id instead of product_id in INNER JOIN I get correct results. When I remove targetForeignKey it has no effect. When I give it invalid value it also has no effect.

According to documentation

targetForeignKey: The name of the foreign key that references the target model found on the join model [...] The default value for this key is the underscored, singular name of the target model, suffixed with ‘_id’.

it feels like it always defaults to class name (instead of alias) and in my case I’m joining the same table but under different alias.

CakePHP Version

4.3.6

PHP Version

7.4

About this issue

Original URL
State: closed
Created 2 years ago
Comments: 18 (10 by maintainers)

Most upvoted comments

Sure.

// in ProductsTable
    $this->belongsToMany('LinkedProducts', [
            'className' => 'Products',
            'joinTable' => 'products_linked_products',
            'foreignKey' => 'product_id',
            'targetForeignKey' => 'linked_product_id',
        ]);

// In ProductsLinkedProductsTable
        $this->belongsTo('ParentProducts', [
            'foreignKey' => 'product_id',
            'joinType' => 'INNER',
        ]);
        $this->belongsTo('LinkedProducts', [
            'foreignKey' => 'linked_product_id',
            'joinType' => 'INNER',
        ]);

Now all of your aliases are unique and shouldn’t conflict.

markstory on Mar 14, 2023

Hei! Finally reproduced

Import provided .sql below
Run cake bake model Products
Run cake bake model ProductsLinkedProducts
Add LinkedProducts association to ProductsTable:

$this->belongsToMany('LinkedProducts', [
    'className' => 'Products',
    'joinTable' => 'products_linked_products',
    'foreignKey' => 'product_id',
    'targetForeignKey' => 'linked_product_id',
]);

Create and run test command (cake bake command test):

public function execute(Arguments $args, ConsoleIo $io)
{
    /** @var ProductsTable $productsTable */
    $productsTable = $this->fetchTable('Products');

    $query = $productsTable->find()->contain('LinkedProducts');

    $first = $query->first();

    dd($first->toArray());
}

Result will be:

[
  'id' => (int) 1,
  'name' => 'Parent Product (1)',
  'linked_products' => [
    (int) 0 => [
      'id' => (int) 1,
      'name' => 'Parent Product (1)',
      '_joinData' => [
        'id' => (int) 1,
        'product_id' => (int) 1,
        'linked_product_id' => (int) 2
      ]
    ]
  ]
]

But if i remove association for ProductsLinkedProducts from ProductsTable then query returns correct linked_products entries:

[
  'id' => (int) 1,
  'name' => 'Parent Product (1)',
  'linked_products' => [
    (int) 0 => [
      'id' => (int) 2,
      'name' => 'Child Product',
      '_joinData' => [
        'id' => (int) 1,
        'product_id' => (int) 1,
        'linked_product_id' => (int) 2
      ]
    ]
  ]
]

The problem is that query ignoring targetForeignKey option if there are already relation for specified joinTable which seems wrong. One table should have ability to have two exact relation classes but joined from different fields.

By the way - this problem doesn’t exists in cake2.

Thanks!

Here the .sql

CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

INSERT INTO `products` VALUES (1,'Parent Product (1)'),(2,'Child Product');

CREATE TABLE `products_linked_products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `linked_product_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `products_linked_products_products_product_id_fk` (`product_id`),
  KEY `products_linked_products_products_lined_product_id_fk` (`linked_product_id`),
  CONSTRAINT `products_linked_products_products_lined_product_id_fk` FOREIGN KEY (`linked_product_id`) REFERENCES `products` (`id`),
  CONSTRAINT `products_linked_products_products_product_id_fk` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

INSERT INTO `products_linked_products` VALUES (1,1,2);

misty-mountain on Mar 9, 2023