データベースの基本¶

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;

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

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

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

namespace App\Database\Type;

use Cake\Database\Driver;
use Cake\Database\Type;
use PDO;

class JsonType extends Type
{

    public function toPHP($value, Driver $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, Driver $driver)
    {
        return json_encode($value);
    }

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

}

use Cake\Database\Type;

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

use Cake\Database\Schema\TableSchema;

class WidgetsTable extends Table
{

    protected function _initializeSchema(TableSchema $schema)
    {
        $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 からのデータをパース
        return new static($value[0], $value[1]);
    }

    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\Expression\FunctionExpression;
use Cake\Database\Type as BaseType;
use Cake\Database\Type\ExpressionTypeInterface;

class PointType extends BaseType implements ExpressionTypeInterface
{
    public function toPHP($value, Driver $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)
    {
        if ($value instanceof Point) {
            return new FunctionExpression(
                'POINT',
                [
                    $value->lat(),
                    $value->long()
                ]
            );
        }
        if (is_array($value)) {
            return new FunctionExpression('POINT', [$value[0], $value[1]]);
        }
        // その他のケースを処理
    }
}

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

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

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

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

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

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

foreach ($query as $row) {
    // 行に何かする
}

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

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

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

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

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

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

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

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

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

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

$stmt->execute();

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

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

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

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

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