README

CakePHP 行为插件，用于轻松生成一些复杂的查询，例如（批量）插入/更新等。

需求

• PHP 8.1+
• CakePHP 5.0+
• MySQL 8.0+ / MariaDB 10.4+

注意

• 对于 CakePHP4.x，请使用 3.x 标签。
• 对于 CakePHP3.x，请使用 1.x 标签。

安装

composer require itosho/easy-query

用法

Upsert

$this->Tags = TableRegistry::getTableLocator()->get('Tags');
$this->Tags->addBehavior('Itosho/EasyQuery.Upsert', [
    'uniqueColumns' => ['name'],
    'updateColumns' => ['description', 'modified'],
]);

$data = [
    'name' => 'cakephp',
    'description' => 'php web framework',
];
$entity = $this->Tags->newEntity($data);
$this->Tags->upsert($entity);

批量 Upsert

$this->Tags = TableRegistry::getTableLocator()->get('Tags');
$this->Tags->addBehavior('Itosho/EasyQuery.Upsert', [
    'updateColumns' => ['description', 'modified'],
]);

$data = [
    [
        'name' => 'cakephp',
        'description' => 'php web framework',
    ],
    [
        'name' => 'rubyonrails',
        'description' => 'ruby web framework',
    ]
];
$entities = $this->Tags->newEntities($data);
$this->Tags->bulkUpsert($entities);

批量插入

$this->Articles = TableRegistry::getTableLocator()->get('Articles');
$this->Articles->addBehavior('Itosho/EasyQuery.Insert');

$data = [
    [
        'title' => 'First Article',
        'body' => 'First Article Body',
        'published' => '1',
    ],
    [
        'title' => 'Second Article',
        'body' => 'Second Article Body',
        'published' => '0',
    ]
];
$entities = $this->Articles->newEntities($data);
$this->Articles->bulkInsert($entities);

插入选择

仅插入一条记录。

case1

指定搜索条件。

$this->Articles = TableRegistry::getTableLocator()->get('Articles');
$this->Articles->addBehavior('Itosho/EasyQuery.Insert');

$data = [
    'title' => 'New Article?',
    'body' => 'New Article Body?',
];
$entity = $this->Articles->newEntity($data);
$condition = ['title' => 'New Article?'];

$this->Articles->insertOnce($entities);

生成的 SQL 如下。

INSERT INTO articles (title, body)
SELECT 'New Article?', 'New Article Body?' FROM tmp WHERE NOT EXISTS (    
    SELECT * FROM articles WHERE title = 'New Article?'
)

case2

自动设置搜索条件与插入记录。

$this->Articles = TableRegistry::getTableLocator()->get('Articles');
$this->Articles->addBehavior('Itosho/EasyQuery.Insert');

$data = [
    'title' => 'New Article',
    'body' => 'New Article Body',
];
$entity = $this->Articles->newEntity($data);

$this->Articles->insertOnce($entities);

生成的 SQL 如下。

INSERT INTO articles (title, body)
SELECT 'New Article', 'New Article Body' FROM tmp WHERE NOT EXISTS (    
    SELECT * FROM articles WHERE title = 'New Article' AND body = 'New Article Body'
)

高级

如果您想自动更新 created 和 modified 字段，需要使用 Timestamp 行为。您还可以通过使用 event 配置手动更改操作，如下所示。

// default value is true
$this->Articles->addBehavior('Itosho/EasyQuery.Insert', [
    'event' => ['beforeSave' => false],
]);

贡献

欢迎在 GitHub 上提交错误报告和拉取请求 https://github.com/itosho/easy-query。

许可证

此插件作为开源软件，根据 MIT 许可证 的条款提供。