データベースの基本¶

use Cake\Datasource\ConnectionManager;

$connection = ConnectionManager::get('default');
$results = $connection->execute('SELECT * FROM articles')->fetchAll('assoc');

$results = $connection
    ->execute('SELECT * FROM articles WHERE id = :id', ['id' => 1])
    ->fetchAll('assoc');

use Cake\Datasource\ConnectionManager;
use DateTime;

$connection = ConnectionManager::get('default');
$results = $connection
    ->execute(
        'SELECT * FROM articles WHERE created >= :created',
        ['created' => new DateTime('1 day ago')],
        ['created' => 'datetime']
    )
    ->fetchAll('assoc');

$results = $connection
    ->newQuery()
    ->select('*')
    ->from('articles')
    ->where(['created >' => new DateTime('1 day ago')], ['created' => 'datetime'])
    ->order(['title' => 'DESC'])
    ->execute()
    ->fetchAll('assoc');

use Cake\Datasource\ConnectionManager;
use DateTime;

$connection = ConnectionManager::get('default');
$connection->insert('articles', [
    'title' => 'A New Article',
    'created' => new DateTime('now')
], ['created' => 'datetime']);

use Cake\Datasource\ConnectionManager;
$connection = ConnectionManager::get('default');
$connection->update('articles', ['title' => 'New title'], ['id' => 10]);

use Cake\Datasource\ConnectionManager;
$connection = ConnectionManager::get('default');
$connection->delete('articles', ['id' => 10]);

use Cake\Datasource\ConnectionManager;

$connection = ConnectionManager::get('default');

ConnectionManager::setConfig('my_connection', $config);
$connection = ConnectionManager::get('my_connection');

// config/bootstrap.php の中で
use Cake\Database\TypeFactory;
use Cake\Database\Type\DateTimeFractionalType;

// デフォルトのdatetime型を、より正確なものに上書きします。
TypeFactory::map('datetime', DateTimeFractionalType::class);

// config/bootstrap.php の中で
use Cake\Database\TypeFactory;
use Cake\Database\Type\DateTimeTimezoneType;

// デフォルトのdatetime型を、より正確なものに上書きします。
TypeFactory::map('datetime', DateTimeTimezoneType::class);

// src/Database/Type/JsonType.php の中で

namespace App\Database\Type;

use Cake\Database\DriverInterface;
use Cake\Database\Type\BaseType;
use PDO;

class JsonType extends BaseType
{
    public function toPHP($value, DriverInterface $driver)
    {
        if ($value === null) {
            return null;
        }

        return json_decode($value, true);
    }

    public function marshal($value)
    {
        if (is_array($value) || $value === null) {
            return $value;
        }

        return json_decode($value, true);
    }

    public function toDatabase($value, DriverInterface $driver)
    {
        return json_encode($value);
    }

    public function toStatement($value, DriverInterface $driver)
    {
        if ($value === null) {
            return PDO::PARAM_NULL;
        }

        return PDO::PARAM_STR;
    }

}

use Cake\Database\TypeFactory;

TypeFactory::map('json', 'App\Database\Type\JsonType');

use Cake\Database\Schema\TableSchemaInterface;

class WidgetsTable extends Table
{

    public function getSchema(): TableSchemaInterface
    {
        $schema = parent::getSchema();
        $schema->columnType('widget_prefs', 'json');

        return $schema;
    }

}

// src/Database/Point.php の中で
namespace App\Database;

// 値オブジェクトはイミュータブルです。
class Point
{
    protected $_lat;
    protected $_long;

    // ファクトリーメソッド
    public static function parse($value)
    {
        // MySQLからWKBデータを解析します。
        $unpacked = unpack('x4/corder/Ltype/dlat/dlong', $value);

        return new static($unpacked['lat'], $unpacked['long']);
    }

    public function __construct($lat, $long)
    {
        $this->_lat = $lat;
        $this->_long = $long;
    }

    public function lat()
    {
        return $this->_lat;
    }

    public function long()
    {
        return $this->_long;
    }
}

namespace App\Database\Type;

use App\Database\Point;
use Cake\Database\DriverInterface;
use Cake\Database\Expression\FunctionExpression;
use Cake\Database\ExpressionInterface;
use Cake\Database\Type\BaseType;
use Cake\Database\Type\ExpressionTypeInterface;

class PointType extends BaseType implements ExpressionTypeInterface
{
    public function toPHP($value, DriverInterface $d)
    {
        return Point::parse($value);
    }

    public function marshal($value)
    {
        if (is_string($value)) {
            $value = explode(',', $value);
        }
        if (is_array($value)) {
            return new Point($value[0], $value[1]);
        }

        return null;
    }

    public function toExpression($value): ExpressionInterface
    {
        if ($value instanceof Point) {
            return new FunctionExpression(
                'POINT',
                [
                    $value->lat(),
                    $value->long()
                ]
            );
        }
        if (is_array($value)) {
            return new FunctionExpression('POINT', [$value[0], $value[1]]);
        }
        // その他のケースを処理
    }

    public function toDatabase($value, DriverInterface $driver)
    {
        return $value;
    }
}

TypeFactory::build('datetime')->useImmutable();
TypeFactory::build('date')->useImmutable();
TypeFactory::build('time')->useImmutable();
TypeFactory::build('timestamp')->useImmutable();

$statement = $connection->query('UPDATE articles SET published = 1 WHERE id = 2');

$statement = $connection->execute(
    'UPDATE articles SET published = ? WHERE id = ?',
    [1, 2]
);

$statement = $connection->execute(
    'UPDATE articles SET published_date = ? WHERE id = ?',
    [new DateTime('now'), 2],
    ['date', 'integer']
);

$query = $connection->newQuery();
$query->update('articles')
    ->set(['published' => true])
    ->where(['id' => 2]);
$statement = $query->execute();

$query = $connection->newQuery();
$query->select('*')
    ->from('articles')
    ->where(['published' => true]);

foreach ($query as $row) {
    // Do something with the row.
}

$connection->begin();
$connection->execute('UPDATE articles SET published = ? WHERE id = ?', [true, 2]);
$connection->execute('UPDATE articles SET published = ? WHERE id = ?', [false, 4]);
$connection->commit();

$connection->transactional(function ($connection) {
    $connection->execute('UPDATE articles SET published = ? WHERE id = ?', [true, 2]);
    $connection->execute('UPDATE articles SET published = ? WHERE id = ?', [false, 4]);
});

// execute は指定された値でバインドして SQL ステートメントを実行します。
$statement = $connection->execute(
    'SELECT * FROM articles WHERE published = ?',
    [true]
);

// prepare はプレースホルダーのための準備をします。
// 実行する前にパラメーターをバインドする必要があります。
$statement = $connection->prepare('SELECT * FROM articles WHERE published = ?');

$statement = $connection->prepare(
    'SELECT * FROM articles WHERE published = ? AND created > ?'
);

// 複数項目のバインド
$statement->bind(
    [true, new DateTime('2013-01-01')],
    ['boolean', 'date']
);

// １項目ずつのバインド
$statement->bindValue(1, true, 'boolean');
$statement->bindValue(2, new DateTime('2013-01-01'), 'date');

$statement = $connection->prepare(
    'SELECT * FROM articles WHERE published = :published AND created > :created'
);

// 複数項目のバインド
$statement->bind(
    ['published' => true, 'created' => new DateTime('2013-01-01')],
    ['published' => 'boolean', 'created' => 'date']
);

// １項目ずつのバインド
$statement->bindValue('published', true, 'boolean');
$statement->bindValue('created', new DateTime('2013-01-01'), 'date');

$statement->execute();

// １行読み込む
$row = $statement->fetch('assoc');

// 全行を読み込む
$rows = $statement->fetchAll('assoc');

// 全行読み込んだ結果を順次処理する
foreach ($statement as $row) {
    // Do work
}

$rowCount = count($statement);
$rowCount = $statement->rowCount();

$code = $statement->errorCode();
$info = $statement->errorInfo();