INSERT [一括登録]

複数件データ登録時、クエリ発行数を減らすために一括登録する手順。

namespace App\Model\Table;

use Cake\ORM\Query;
use Cake\ORM\RulesChecker;
use Cake\ORM\Table;
use Cake\Validation\Validator;

/**
 * MCompanies Model
 */
class MCompaniesTable extends Table
{    
    /**
     * 登録
     * 
     * @param array $targets
     * @return boolean
     */
    public function insert($targets)
    {
        $conn = $this->connection();
        try {
            $conn->begin();
            $query = $this->query()->insert(['company_code', 'company_name', 'is_deleted']);
            foreach ($targets as $target) {
                $query->values($target);
            }
            $query->execute();
            $conn->commit();
            return true;
        } catch (Exception $e) {
            // エラー処理
            return false;
        }
    }
}

ループ処理で登録情報をvaluesに設定し、再度に一度だけexecute実行。

定義した登録処理を呼び出す。

namespace App\Model\Table;

use Cake\Console\Shell;
use \SplFileObject;

class TestShell extends Shell
{
    public $tasks = ['Util'];

    public function initialize()
    {
        parent::initialize();
        $this->loadModel('MCompanies');
    }

    /**
     * 登録
     */
    public function insert()
    {
        // 登録データ作成
        $targets = array();
        for ($i = 1; $i < 11; $i++) {
            $targets[] = array(
                "company_code" => $i,
                "company_name" => "test",
                "is_deleted" => 0);
        }

        if ($this->MCompanies->insert($targets) === false) {
            $this->out("Failure");
            return;
        }
        $this->out("Success");
    }

}

UPDATE

更新処理は1件単位にクエリ発行する方式を記載。

namespace App\Model\Table;

use Cake\ORM\Query;
use Cake\ORM\RulesChecker;
use Cake\ORM\Table;
use Cake\Validation\Validator;

/**
 * MCompanies Model
 */
class MCompaniesTable extends Table
{   
    /**
     * 更新
     * 
     * @param array $targets
     * @return boolean
     */
    public function update($targets)
    {
        $conn = $this->connection();
        try {
            foreach ($targets as $target) {
                $company = array(
                    "company_name" => $target["company_name"],
                    "is_deleted" => $target["is_deleted"]
                );
                $condition = array("company_code" => $target["company_code"]);
                $this->updateAll($company, $condition);
            }
            $conn->commit();
            return true;
        } catch (Exception $e) {
            // エラー処理
            return false;
        }
    }
}

定義した登録処理を呼び出します。

namespace App\Shell;

use Cake\Console\Shell;
use \SplFileObject;

/**
 * Test shell command.
 */
class TestShell extends Shell
{
    public $tasks = ['Util'];

    public function initialize()
    {
        parent::initialize();
        $this->loadModel('MCompanies');
    }

    /**
     * 更新
     */
    public function update()
    {
        $targets = array();
        for ($i = 1; $i < 11; $i++) {
            $targets[] = array(
                "company_code" => $i,
                "company_name" => "xxxxxx",
                "is_deleted" => 1);
        }

        if ($this->MCompanies->update($targets) === false) {
            $this->out("Failure");
            return;
        }
        $this->out("Success");
    }

}

【CakePHP3】MySQL上で一括登録と更新

INSERT [一括登録]

UPDATE