itosho/easy-query

CakePHP behavior plugin for easily some complicated queries.

Installs: 1 464

Dependents: 0

Suggesters: 0

Security: 0

Stars: 18

Watchers: 1

Forks: 3

Open Issues: 4

Type:cakephp-plugin

v1.2.0 2019-11-03 15:10 UTC

This package is auto-updated.

Last update: 2020-01-14 15:24:58 UTC


README

CakePHP behavior plugin for easily some complicated queries.

Build Status codecov Latest Stable Version Total Downloads License

Requirements

  • PHP 7.0+
  • CakePHP 3.5+
  • MySQL 5.6+

Installation

composer require itosho/easy-query

Usage

Upsert

$this->Tags = TableRegistry::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::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::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::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::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.