Skip to content

itosho/easy-query

master
Switch branches/tags

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?
Code

Latest commit

 

Git stats

Files

Permalink
Failed to load latest commit information.
Type
Name
Latest commit message
Commit time
 
 
 
 
 
 
 
 
 
 
 
 

Easy Query

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

Build Status codecov Latest Stable Version Total Downloads License

Requirements

  • PHP 7.2+
  • CakePHP 4.0+
  • MySQL 5.6+

⚠️ For CakePHP3.x, use 1.x branch.

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.