CakePHP 3.4

CakePHP 3.4 - 如何在同一个查询中添加2个内部连接？(Cake PHP 3.4 - how to add 2 inner joins on the same query?)

运行在MAMP上的CakePHP 3.4。 我有以下情况：

SQL表

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;

表模型：

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' ]); }

在CakePHP上，我遵循了cookbok的书签教程（适应于我的场景）。逻辑将是有一个产品有很多成分。 烘烤后，这似乎工作正常。

我想实现的目标是：在特定产品的产品视图中，我想显示产品字段（与产品ID相关），还要显示成分。 使用我的代码，我显示了产品字段（这是可以的），但只显示了joiner表ingredients_products的相关ID。

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

在SQL中，我将运行的查询是：

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

我一直在尝试在“查询”构建器页面上找到的东西： https : //book.cakephp.org/3.0/en/orm/query-builder.html

但我找不到任何可以让我做这样的查询的东西。 有谁知道这是如何实现的？

谢谢！

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!

最满意答案

这听起来很像一个属于并且有很多关系。

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 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'); } }