php - 在 CakePHP 中使用下拉菜单时的外键约束

Question

我正在使用具有外键关系的下拉框。我已经在下拉菜单中填写了正确的值，但唯一的问题是当我添加用户时存在外键约束。但是，如果我只使用普通输入框并输入另一个表中存在的 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']);

这是在我的用户表中

    $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;

Answer 1

命名约定

您没有遵循命名约定，默认情况下外键名称为 belongsTo Association 是关联别名的单下划线变体，后缀为 _id所以在 CompanyLocations 的情况下那将是 company_location_id ，不仅仅是 location .

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

此外，保存列表的变量应该使用驼峰式大小写，那么您甚至不需要通过选项参数指定它:

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

更改关联默认值

如果您正在使用无法修改的旧数据库，那么您需要相应地配置 CakePHP，即通过 Table::belongsTo() 的选项参数指定自定义外键。 .

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

烘焙变得困惑

belongsTo协会CompanyLocationsTable看起来也很可疑，除非你真的有 LocationsTable应该与 CompanyLocationsTable 相关联通过:

company_locations.location_id > locations.primary_key

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

另请参阅

• Cookbook > CakePHP at a Glance > Conventions > Model and Database Conventions
• Cookbook > Database Access & ORM > Associations - Linking Tables Together > BelongsTo Associations
• Cookbook > Views > Helpers > Form > Creating Form Controls
• Cookbook > Views > Helpers > Form > Creating Inputs for Associated Data