Easy Query

CakePHP behavior plugin for easily generating some complicated queries like (bulk) insert/upsert etc.

Requirements

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

Notice

For CakePHP4.x, use 3.x tag.
For CakePHP3.x, use 1.x tag.

Installation

composer require itosho/easy-query

Usage

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

Bulk 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);

Bulk Insert

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

Insert Select

For inserting a record just once.

case1

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?'
)

case2

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

Advanced

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],
]);

Contributing

Bug reports and pull requests are welcome on GitHub at https://github.com/itosho/easy-query.

License

The plugin is available as open source under the terms of the MIT License.