CakePHP behavior plugin for easily generating some complicated queries like (bulk) insert/upsert etc.
- PHP 8.1+
- CakePHP 5.0+
- MySQL 8.0+ / MariaDB 10.4+
- For CakePHP4.x, use 3.x tag.
- For CakePHP3.x, use 1.x tag.
composer require itosho/easy-query$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);$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);For inserting a record just once.
Specify search conditions.
$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);Generated SQL is below.
INSERT INTO articles (title, body)
SELECT 'New Article?', 'New Article Body?' FROM tmp WHERE NOT EXISTS (
SELECT * FROM articles WHERE title = 'New Article?'
)Auto set search conditions with a inserting record.
$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);Generated SQL is below.
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'
)Need to use Timestamp behavior, if you want to update created and modified fields automatically.
And you can change the action manually by using event config like this.
// default value is true
$this->Articles->addBehavior('Itosho/EasyQuery.Insert', [
'event' => ['beforeSave' => false],
]);Bug reports and pull requests are welcome on GitHub at https://github.com/itosho/easy-query.
The plugin is available as open source under the terms of the MIT License.
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent