CakePHP 3使用下拉菜单时的外键约束(Foreign key constraint when using drop down in CakePHP)

我需要一些帮助来使用具有外键关系的下拉框。我已经得到了填充正确值的下拉菜单，但唯一的问题是当我添加一个用户时存在外键约束。但我可以让用户，如果我只是使用正常的输入框，并键入另一个表中存在的ID。例如，当我在我的add.ctp中输入id时，它可以工作

echo $this->Form->input('location');

但是当我使用它时，它不会

echo $this->Form->input('location_id', array('type' => 'select', 'options' => $CompanyLocations));

这是我的UsersController中的添加功能

public function add() { $user = $this->Users->newEntity(); if ($this->request->is('post')) { $user = $this->Users->patchEntity($user, $this->request->data); if ($this->Users->save($user)) { $this->Flash->success(__('The user has been saved.')); return $this->redirect(['action' => 'index']); } $this->Flash->error(__('The user could not be saved. Please, try again.')); } $CompanyLocations= $this->Users->CompanyLocations->find('list'); $this->set(compact('CompanyLocations')); $this->set(compact('user')); $this->set('_serialize', ['user']);

这是在我的UsersTable中

$this->belongsTo('CompanyLocations');

和我的CompanyLocationsTable

public function initialize(array $config) { parent::initialize($config); $this->table('company_locations'); $this->displayField('location_name'); $this->primaryKey('location_id'); $this->belongsTo('Locations', [ 'foreignKey' => 'location_id', 'joinType' => 'INNER' ]); }

和我的mysql代码

CREATE TABLE IF NOT EXISTS southpac_team.company_locations ( location_id INT NOT NULL AUTO_INCREMENT, location_name VARCHAR(45) NULL, PRIMARY KEY (location_id)) ENGINE = InnoDB; DROP TABLE IF EXISTS southpac_team.users ; CREATE TABLE IF NOT EXISTS southpac_team.users ( id INT NOT NULL AUTO_INCREMENT, username VARCHAR(20) NOT NULL, password VARCHAR(255) NOT NULL, name VARCHAR(255) NOT NULL, department INT NULL, mobile VARCHAR(255) NULL, email VARCHAR(255) NULL, extension INT NULL, lame_number INT NULL, spa_auth_number VARCHAR(15) NULL, creation_date DATE NULL, picture VARCHAR(255) NULL, employed TINYINT(1) NOT NULL, location INT NOT NULL, PRIMARY KEY (id), INDEX get location_idx (location ASC), CONSTRAINT get location FOREIGN KEY (location) REFERENCES southpac_team.company_locations(location_id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

I am using a drop down box with a foreign key relationship. I have got the drop down filling in the correct values but the only problem is when I add a user there is a foreign key constraint. But I can make users if I just use the normal input box and type an id that exists in the other table. For example when I enter the id with this in my add.ctp, it works:

echo $this->Form->input('location');

but when I use this it doesn't

echo $this->Form->input('location_id', array('type' => 'select', 'options' => $CompanyLocations));

This is my add function in my UsersController

public function add() { $user = $this->Users->newEntity(); if ($this->request->is('post')) { $user = $this->Users->patchEntity($user, $this->request->data); if ($this->Users->save($user)) { $this->Flash->success(__('The user has been saved.')); return $this->redirect(['action' => 'index']); } $this->Flash->error(__('The user could not be saved. Please, try again.')); } $CompanyLocations= $this->Users->CompanyLocations->find('list'); $this->set(compact('CompanyLocations')); $this->set(compact('user')); $this->set('_serialize', ['user']);

This is in my UsersTable

$this->belongsTo('CompanyLocations');

and my CompanyLocationsTable

public function initialize(array $config) { parent::initialize($config); $this->table('company_locations'); $this->displayField('location_name'); $this->primaryKey('location_id'); $this->belongsTo('Locations', [ 'foreignKey' => 'location_id', 'joinType' => 'INNER' ]); }

and my MySQL code

CREATE TABLE IF NOT EXISTS southpac_team.company_locations ( location_id INT NOT NULL AUTO_INCREMENT, location_name VARCHAR(45) NULL, PRIMARY KEY (location_id)) ENGINE = InnoDB; DROP TABLE IF EXISTS southpac_team.users ; CREATE TABLE IF NOT EXISTS southpac_team.users ( id INT NOT NULL AUTO_INCREMENT, username VARCHAR(20) NOT NULL, password VARCHAR(255) NOT NULL, name VARCHAR(255) NOT NULL, department INT NULL, mobile VARCHAR(255) NULL, email VARCHAR(255) NULL, extension INT NULL, lame_number INT NULL, spa_auth_number VARCHAR(15) NULL, creation_date DATE NULL, picture VARCHAR(255) NULL, employed TINYINT(1) NOT NULL, location INT NOT NULL, PRIMARY KEY (id), INDEX get location_idx (location ASC), CONSTRAINT get location FOREIGN KEY (location) REFERENCES southpac_team.company_locations(location_id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

最满意答案

命名约定

您没有遵循命名约定，默认情况下， belongsTo关联的外键名称是关联别名的单数下划线变体，后缀为_id因此在CompanyLocations中将为company_location_id ，而不仅仅是location 。

echo $this->Form->input('company_location_id');

另外，持有列表的变量应该使用camel套件，那么您甚至不需要通过options参数指定它：

$companyLocations= $this->Users->CompanyLocations->find('list'); $this->set(compact('companyLocations'));

更改关联的默认值

如果您使用的是不能修改的遗留数据库，则需要相应地配置CakePHP，即通过Table::belongsTo()的options参数指定自定义外键。

$this->belongsTo('CompanyLocations', [ 'foreignKey' => 'location' ]);

烘烤变得困惑

CompanyLocationsTable的belongsTo关联看起来也很腥，除非您确实有一个LocationsTable应该通过以下方式与CompanyLocationsTable关联：

company_locations.location_id > locations.primary_key

我猜你已经通过烘焙创建了模型，它将location_id当作外键处理，因为它匹配belongsTo关联的默认外键命名方案。

也可以看看

Cookbook> CakePHP概览>约定>模型和数据库约定 食谱>数据库访问和ORM>关联 - 将表链接在一起>属于关联 食谱>视图>助手>表单>创建表单控件 食谱>视图>助手>表单>为关联数据创建输入

Naming conventions

You are not following the naming conventions, by default the foreign key name for a belongsTo association is the singular underscored variant of the association alias, postfixed with _id so in the case of CompanyLocations that would be company_location_id, not just location.

echo $this->Form->input('company_location_id');

Also the variable holding the list should use camel casing, then you don't even need to specify it via the options argument:

$companyLocations= $this->Users->CompanyLocations->find('list'); $this->set(compact('companyLocations'));

Change the association defaults

If you are working with a legacy database that you cannot modify, then you need to configure CakePHP accordingly, ie specify the custom foreign key via the options argument of Table::belongsTo().

$this->belongsTo('CompanyLocations', [ 'foreignKey' => 'location' ]);

Bake gets confused

The belongsTo association in CompanyLocationsTable looks fishy too, unless you really have a LocationsTable that should be associated with CompanyLocationsTable via:

company_locations.location_id > locations.primary_key

I guess you've created the model via bake, which treated location_id as a foreign key since it matches the default foreign key naming scheme for a belongsTo association.