gracetory’s blog

東池袋にある合同会社グレストリのエンジニアブログです

トップ > ゴールデンウィークなのでPHPのORMを調査してみた

ゴールデンウィークなのでPHPのORMを調査してみた

f:id:grnishi:20200504004005j:plain

はじめに

皆様どうお過ごしでしょうか？ゴールデンウィークと言えども外出は出来ないので引きこもっているgrnishiです。

普段から休日に外出する事はあまり無いのでいつもと変わらないといえば変わらないのですが。

本題

ちょっと拙作FWの大幅な改修をしておりまして、DB周りをどうしようかなーと悩んでおりまして。

外出できませんし、せっかくなので昨今のORM事情を調査して、良さそうな部分を取り入れてみようかなと思った次第です。

ちなみにORMORMと書いておりますが、調査対象は実質クエリビルダーの部分なので厳密にはORMではありません。実装ではなく、インターフェースがどんな感じなのか。

ORMとは

ここでORMの説明を長々と書いても野暮なのでwikipediaのリンクを。

ja.wikipedia.org

用意したデータベース

こちらのサイトでテストデータを10万件ほど作りました。 tm-webtools.com

テーブル定義は下記参照。イケてないとか言わないように。

mysql> desc test_tables;

Field Type Null Key Default Extra
id int(11) NO PRI NULL
family_name varchar(18) YES NULL
first_name varchar(18) YES NULL
gender tinyint(4) YES NULL
birthday int(11) YES NULL
email varchar(64) YES NULL
ip_address varchar(16) YES NULL
code int(11) YES NULL
register_date int(11) YES NULL

検証環境

GCP n1-standard-1（vCPU x 1、メモリ 3.75 GB）

PHP 7.0.27

Composer 1.8.4

MySQL 5.7.21

比較対象

Idiorm

Idiorm 1.5.7

https://github.com/j4mie/idiorm

インストール

composer require j4mie/idiorm

とりあえず基本的な接続

<?php
    require("vendor/autoload.php");
    ORM::configure('mysql:host=localhost;dbname=test');
    ORM::configure('username', 'root');
    ORM::configure('password', 'password');
    ORM::configure('driver_options', [
        PDO::MYSQL_ATTR_INIT_COMMAND       => 'SET NAMES utf8',
        PDO::ATTR_EMULATE_PREPARES         => false,
    ]);

基本的なSELECT

<?php
    // SELECT * FROM test_tables WHERE family_name = "田中";
    $people = ORM::for_table("test_tables")->where("family_name", "田中")->find_many();
    foreach ($people as $person) {
        echo $person->family_name." ".$person->first_name."\n";
    }

条件を複数(AND)

<?php
    //SELECT * FROM test_tables WHERE family_name = "田中" AND gender = 1;
    $people = ORM::for_table("test_tables")->where(["family_name" => "田中","gender" => 1])->find_many();
    foreach ($people as $person) {
        echo $person->family_name." ".$person->first_name."\n";
    }

条件を複数(OR)

<?php
    //SELECT * FROM test_tables WHERE family_name = "田中" OR family_name = "山本";
    $people = ORM::for_table("test_tables")->where_any_is([["family_name" => "田中"],["family_name" => "山本"]])->find_many();
    foreach ($people as $person) {
        echo $person->family_name." ".$person->first_name."\n";
    }

件数を取得

<?php
    //SELECT COUNT(*) FROM test_tables WHERE family_name = "田中";
    $number_of_people = ORM::for_table("test_tables")->where("family_name", "田中")->count();
    echo $number_of_people . "\n";

取得件数と取得開始位置の指定

<?php
    // SELECT * FROM test_tables WHERE family_name = "田中" LIMIT 5, 10;
    $people = ORM::for_table("test_tables")->where("family_name", "田中")->limit(5)->offset(10)->find_many();
    foreach ($people as $person) {
        echo $person->family_name." ".$person->first_name."\n";
    }

並び替え(昇順)

<?php
    // SELECT * FROM test_tables WHERE family_name = "田中" ORDER BY birthday ASC;
    $people = ORM::for_table("test_tables")->where("family_name", "田中")->order_by_asc("birthday")->find_many();
    foreach ($people as $person) {
        echo $person->family_name." ".$person->first_name."\n";
    }

並び替え(降順)

<?php
    // SELECT * FROM test_tables WHERE family_name = "田中" ORDER BY birthday DESC;
    $people = ORM::for_table("test_tables")->where("family_name", "田中")->order_by_desc("birthday")->find_many();
    foreach ($people as $person) {
        echo $person->family_name." ".$person->first_name."\n";
    }

取得するカラムを指定する

<?php
    // SELECT family_name, first_name FROM test_tables WHERE family_name = "田中";
    $people = ORM::for_table("test_tables")->select('family_name')->select('first_name')->where("family_name", "田中")->find_many();
    foreach ($people as $person) {
        echo $person->family_name." ".$person->first_name."\n";
    }

プライマリーキーで取得する

<?php
    // SELECT * FROM test_tables WHERE id = 1000 LIMIT 1
    $person = ORM::for_table("test_tables")->find_one(1000);
    echo $person->family_name." ".$person->first_name."\n";

UPDATE

<?php
    // SELECT * FROM test_tables WHERE id = 1500 LIMIT 1
    $person = ORM::for_table("test_tables")->find_one(1500);
    $person->family_name = "田中";
    // UPDATE test_table SET family_name = "田中" WHERE id = 1500
    $person->save();

INSERT

<?php
    // INSERT INTO test_tables (id, family_name, first_name, gender, birthday, email, ip_address, code, register_date) VALUES (100001, "田中", "一郎", 1, 20000101, "hogehoge@example.com", "192.168.1.1", 1234, UNIX_TIMESTAMP())
    $person = ORM::for_table("test_tables")->create();
    $person->id = 100001;
    $person->family_name = "田中";
    $person->first_name = "一郎";
    $person->gender = 1;
    $person->birthday = 20000101;
    $person->email = "hogehoge@example.com";
    $person->ip_address = "192.168.1.1";
    $person->code = 1234;
    $person->set_expr("register_date", "UNIX_TIMESTAMP()");
    $person->save();

DELETE

<?php
    // SELECT * FROM test_tables WHERE id = 100001 LIMIT 1
    $person = ORM::for_table("test_tables")->find_one(100001);
    // DELETE FROM test_table WHERE id = 100001
    $person->delete();

参考までに全部実行して処理時間と使用メモリ量

0.366 sec
peak:0.859 MB

Eloquent

Eloquent 5.5 https://github.com/illuminate/database

インストール

composer require illuminate/database

とりあえず基本的な接続

<?php
    require("vendor/autoload.php");
    $db = new \Illuminate\Database\Capsule\Manager();
    $db->addConnection([
        "driver" => "mysql",
        "host" => "localhost",
        "database" => "test",
        "username" => "root",
        "password" => "password",
        "charset" => "utf8",
    ]);
     
    $db->setAsGlobal();
    $db->bootEloquent();

    require("./entity/TestTable.php");

<?php
use Illuminate\Database\Eloquent\Model;

class TestTable extends Model {
    public $timestamps = false;
    protected $guarded = [''];
}

基本的なSELECT

<?php
    // SELECT * FROM test_tables table WHERE family_name = "田中";
    $people = TestTable::where("family_name", "田中")->get();
    foreach ($people as $person) {
        echo $person->family_name." ".$person->first_name."\n";
    }

条件を複数(AND)

<?php
    //SELECT * FROM test_tables WHERE family_name = "田中" AND gender = 1;
    $people = TestTable::where([["family_name", "田中"], ["gender", 1]])->get();
    foreach ($people as $person) {
        echo $person->family_name." ".$person->first_name."\n";
    }

条件を複数(OR)

<?php
    //SELECT * FROM test_tables WHERE family_name = "田中" OR family_name = "山本";
    $people = TestTable::where("family_name", "田中")->orWhere("family_name", "山本")->get();
    foreach ($people as $person) {
        echo $person->family_name." ".$person->first_name."\n";
    }

件数を取得

<?php
    //SELECT COUNT(*) FROM test_tables WHERE family_name = "田中";
    $number_of_people = TestTable::where("family_name", "田中")->count();
    echo $number_of_people . "\n";

取得件数と取得開始位置の指定

<?php
    // SELECT * FROM test_tables WHERE family_name = "田中" LIMIT 5 OFFSET 10;
    $people = TestTable::where("family_name", "田中")->skip(10)->take(5)->get();
    foreach ($people as $person) {
        echo $person->family_name." ".$person->first_name."\n";
    }

並び替え(昇順)

<?php
    // SELECT * FROM test_tables WHERE family_name = "田中" ORDER BY birthday ASC;
    $people = TestTable::where("family_name", "田中")->orderBy("birthday", "asc")->get();
    foreach ($people as $person) {
        echo $person->family_name." ".$person->first_name."\n";
    }

並び替え(降順)

<?php
    // SELECT * FROM test_tables WHERE family_name = "田中" ORDER BY birthday DESC;
    $people = TestTable::where("family_name", "田中")->orderBy("birthday", "desc")->get();
    foreach ($people as $person) {
        echo $person->family_name." ".$person->first_name."\n";
    }

取得するカラムを指定する

<?php
    // SELECT family_name, first_name FROM test_tables WHERE family_name = "田中";
    $people = TestTable::select("family_name", "first_name")->where("family_name", "田中")->get();
    foreach ($people as $person) {
        echo $person->family_name." ".$person->first_name."\n";
    }

プライマリーキーで取得する

<?php
    // SELECT * FROM test_tables WHERE id = 1000 LIMIT 1
    $person = TestTable::find(1000);
    echo $person->family_name." ".$person->first_name."\n";

UPDATE

<?php
    // SELECT * FROM test_tables WHERE id = 1500 LIMIT 1
    $person = TestTable::find(1500);
    $person->family_name = "田中";
    // UPDATE test_table SET family_name = "田中" WHERE id = 1500
    $person->save();

INSERT

<?php
    // INSERT INTO test_tables (id, family_name, first_name, gender, birthday, email, ip_address, code, register_date) VALUES (100001, "田中", "一郎", 1, 20000101, "hogehoge@example.com", "192.168.1.1", 1234, UNIX_TIMESTAMP())
    $person = TestTable::create([
        "id" => 100001,
        "family_name" => "田中",
        "first_name" => "一郎",
        "gender" => 1,
        "birthday" => 20000101,
        "email" => "hogehoge@example.com",
        "ip_address" => "192.168.1.1",
        "code" => 1234,
        "register_date" => time(),
    ]);

DELETE

<?php
    // SELECT * FROM test_tables WHERE id = 100001 LIMIT 1
    $person = TestTable::find(100001);
    // DELETE FROM test_table WHERE id = 100001
    $person->delete();

参考までに全部実行して処理時間と使用メモリ量

0.392 sec
peak:1.501 MB

propel

http://propelorm.org/

インストール

composer require propel/propel

プロジェクトのセットアップ

vendor/bin/propel init

ダイアログに従って進めていきます。もろもろpropel用のファイルが出来上がります。

composer.jsonに下記を追加してdump-autoload

 "autoload": {
        "psr-4": {
            "Base\\" : "Base/",
            "Map\\" : "Map/",
        }
    }

とりあえず基本的な接続

<?php
    require_once("generated-conf/config.php");
    require_once("TestTables.php");
    require_once("TestTablesQuery.php");

基本的なSELECT

<?php
    // SELECT * FROM test_tables WHERE family_name = "田中";
    $people =  TestTablesQuery::create()->findByFamilyName("田中");
    foreach ($people as $person) {
        echo $person->getFamilyName()." ".$person->getFirstName()."\n";
    }

条件を複数(AND)

<?php
    //SELECT * FROM test_tables WHERE family_name = "田中" AND gender = 1;
    $people =  TestTablesQuery::create()->filterByFamilyName("田中")->filterByGender(1);
    foreach ($people as $person) {
        echo $person->getFamilyName()." ".$person->getFirstName()."\n";
    }

条件を複数(OR)

<?php
    //SELECT * FROM test_tables WHERE family_name = "田中" OR family_name = "山本";
    $people =  TestTablesQuery::create()->filterByFamilyName("田中")->_or()->filterByFamilyName("山本");
    foreach ($people as $person) {
        echo $person->getFamilyName()." ".$person->getFirstName()."\n";
    }

件数を取得

<?php
    //SELECT COUNT(*) FROM test_tables WHERE family_name = "田中";
    $number_of_people = TestTablesQuery::create()->filterByFamilyName("田中")->count();
    echo $number_of_people . "\n";

取得件数と取得開始位置の指定

<?php
    // SELECT * FROM test_tables WHERE family_name = "田中" LIMIT 5 OFFSET 10;
    $people = TestTablesQuery::create()->filterByFamilyName("田中")->limit(5)->offset(10)->find();
    foreach ($people as $person) {
        echo $person->getFamilyName()." ".$person->getFirstName()."\n";
    }

並び替え(昇順)

<?php
    // SELECT * FROM test_tables WHERE family_name = "田中" ORDER BY birthday ASC;
    $people = TestTablesQuery::create()->filterByFamilyName("田中")->orderBy("birthday", "asc")->find();
    foreach ($people as $person) {
        echo $person->getFamilyName()." ".$person->getFirstName()."\n";
    }

並び替え(降順)

<?php
    // SELECT * FROM test_tables WHERE family_name = "田中" ORDER BY birthday DESC;
    $people = TestTablesQuery::create()->filterByFamilyName("田中")->orderBy("birthday", "desc")->find();
    foreach ($people as $person) {
        echo $person->getFamilyName()." ".$person->getFirstName()."\n";
    }

取得するカラムを指定する

<?php
    // SELECT family_name, first_name FROM test_tables WHERE family_name = "田中";
    $people = TestTablesQuery::create()->select(["family_name", "first_name"])->filterByFamilyName("田中")->find();
    foreach ($people as $person) {
        echo $person["family_name"]." ".$person["first_name"]."\n";
    }

なぜカラム指定すると配列が返ってくるのだろうか。。。

プライマリーキーで取得する

<?php
    // SELECT * FROM test_tables WHERE id = 1000 LIMIT 1
    $person = TestTablesQuery::create()->findPk(1000);
  echo $person->getFamilyName() . " " . $person->getFirstName() . "\n";

UPDATE

<?php
    // SELECT * FROM test_tables WHERE id = 1500 LIMIT 1
    $person = TestTablesQuery::create()->findPk(1500);
    $person->setFamilyName("田中");
    // UPDATE test_table SET family_name = "田中" WHERE id = 1500
    $person->save();

INSERT

<?php
    // INSERT INTO test_tables (id, family_name, first_name, gender, birthday, email, ip_address, code, register_date) VALUES (100001, "田中", "一郎", 1, 20000101, "hogehoge@example.com", "192.168.1.1", 1234, UNIX_TIMESTAMP())
    $person = new TestTables();
    $person->setId(100001);
    $person->setFamilyName("田中");
    $person->setFirstName("一郎");
    $person->setGender(1);
    $person->setBirthday(20000101);
    $person->setEmail("hogehoge@example.com");
    $person->setIpAddress("192.168.1.1");
    $person->setCode(1234);
    $person->setRegisterDate(time());
    $person->save();

DELETE

<?php
    // SELECT * FROM test_tables WHERE id = 100001 LIMIT 1
    $person = TestTablesQuery::create()->findPk(100001);
    // DELETE FROM test_table WHERE id = 100001
    $person->delete();

参考までに全部実行して処理時間と使用メモリ量

0.392 sec
peak:1.303 MB

PHP ActiveRecord

www.phpactiverecord.org

インストール

composer require php-activerecord/php-activerecord

とりあえず基本的な接続

<?php
    ActiveRecord\Config::initialize(function($config) {
        $config->set_model_directory("/path/to/your/model_directory");
        $config->set_connections([
            "development" => "mysql://root:password@localhost/test?charset=utf8",
        ]);
        $config->set_default_connection("development");
    });

基本的なSELECT

<?php
    // SELECT * FROM test_tables WHERE family_name = "田中";
    $option = ["conditions" => ["family_name = ?", "田中"]];
    $people = TestTable::all($option);
    foreach ($people as $person) {
        echo $person->family_name." ".$person->first_name."\n";
    }

条件を複数(AND)

<?php
    //SELECT * FROM test_tables WHERE family_name = "田中" AND gender = 1;
    $option = ["conditions" => ["family_name = ? AND gender = ?", "田中", 1]];
    $people = TestTable::all($option);
    foreach ($people as $person) {
        echo $person->family_name." ".$person->first_name."\n";
    }

条件を複数(OR)

<?php
    //SELECT * FROM test_tables WHERE family_name = "田中" OR family_name = "山本";
    $option = ["conditions" => ["family_name = ? OR family_name = ?", "田中", "山本"]];
    $people = TestTable::all($option);
    foreach ($people as $person) {
        echo $person->family_name." ".$person->first_name."\n";
    }

件数を取得

<?php
    //SELECT COUNT(*) FROM test_tables WHERE family_name = "田中";
    $option = ["conditions" => ["family_name = ?", "田中"]];
    $number_of_people = TestTable::count($option);
    echo $number_of_people . "\n";

取得件数と取得開始位置の指定

<?php
    // SELECT * FROM test_tables WHERE family_name = "田中" LIMIT 10, 5;
    $option = [
        "conditions" => ["family_name = ?", "田中"],
        "limit" => 5,
        "offset" => 10,
    ];
    $people = TestTable::all($option);
    foreach ($people as $person) {
        echo $person->family_name." ".$person->first_name."\n";
    }

並び替え(昇順)

<?php
    // SELECT * FROM test_tables WHERE family_name = "田中" ORDER BY birthday ASC;
    $option = [
        "conditions" => ["family_name = ?", "田中"],
        "order" => "birthday asc",
    ];
    $people = TestTable::all($option);
    foreach ($people as $person) {
        echo $person->family_name." ".$person->first_name."\n";
    }

並び替え(降順)

<?php
    // SELECT * FROM test_tables WHERE family_name = "田中" ORDER BY birthday DESC;
    $option = [
        "conditions" => ["family_name = ?", "田中"],
        "order" => "birthday desc",
    ];
    $people = TestTable::all($option);
    foreach ($people as $person) {
        echo $person->family_name." ".$person->first_name."\n";
    }

取得するカラムを指定する

<?php
    // SELECT family_name, first_name FROM test_tables WHERE family_name = "田中";
    $option = [
        "conditions" => ["family_name = ? AND gender = ?", "田中", 1],
        "select" => "family_name, first_name",
    ];
    $people = TestTable::all($option);
    foreach ($people as $person) {
        echo $person->family_name." ".$person->first_name."\n";
    }

プライマリーキーで取得する

<?php
    // SELECT * FROM test_tables WHERE id = 1000
    $person = TestTable::find(1000);
    echo $person->family_name." ".$person->first_name."\n";

UPDATE

<?php
    // SELECT * FROM test_tables WHERE id = 1500
    $person = TestTable::find(1500);
    $person->family_name = "田中";
    // UPDATE test_tables SET family_name = "田中" WHERE id = 1500
    $person->save();

INSERT

<?php
    // INSERT INTO test_tables (id, family_name, first_name, gender, birthday, email, ip_address, code, register_date) VALUES (100001, "田中", "一郎", 1, 20000101, "hogehoge@example.com", "192.168.1.1", 1234, UNIX_TIMESTAMP())
    $person = new TestTable();
    $person->id = 100001;
    $person->family_name = "田中";
    $person->first_name = "一郎";
    $person->gender = 1;
    $person->birthday = 20000101;
    $person->email = "hogehoge@example.com";
    $person->ip_address = "192.168.1.1";
    $person->code = 1234;
    $person->register_date = time();
    $person->save();

DELETE

<?php
    // SELECT * FROM test_tables WHERE id = 100001
    $person = TestTable::find(100001);
    // DELETE FROM test_tables WHERE id = 100001
    $person->delete();

参考までに全部実行して処理時間と使用メモリ量

0.380 sec
peak:0.983 MB

以下は実装がめんどくさいので必須バージョン等事情により未実装調査のみ

CakePHP

cakephp.org

使用例

<?php
    use Cake\ORM\TableRegistry;

    $test_tables = TableRegistry::getTableLocator()->get('TestTables');

    // SELECT * FROM test_tables WHERE family_name = "田中";
    $people = $test_tables->find('all', [
        "conditions" => ["TestTable.family_name = " => "田中"],
    ])->all();

    // SELECT * FROM test_tables WHERE family_name = "田中" AND gender = 1;
    $people = $test_tables->find('all', [
        "conditions" => ["TestTable.family_name" => "田中", "TestTable.gender" => 1],
    ])->all();

    //SELECT * FROM test_tables WHERE family_name = "田中" OR family_name = "山本";
    $people = $test_tables->find('all', [
        "conditions" => ["OR" => ["TestTable.family_name" => "田中", "TestTable.gender" => 1]],
    ])->all();

    //SELECT COUNT(*) FROM test_tables WHERE family_name = "田中";
    $number_of_people = $test_tables->find('all', [
        "conditions" => ["TestTable.family_name = " => "田中"],
    ])->count();

    // SELECT * FROM test_tables WHERE family_name = "田中" LIMIT 10, 5;
    $people = $test_tables->find('all', [
        "conditions" => ["TestTable.family_name = " => "田中"],
        "limit" => 5,
        "offset" => 10,
    ])->all();

    // SELECT * FROM test_tables WHERE family_name = "田中" ORDER BY birthday ASC;
    $people = $test_tables->find('all', [
        "conditions" => ["TestTable.family_name = " => "田中"],
        "order" => ['TesetTable.birthday' => 'ASC'],
    ])->all();

    // SELECT * FROM test_tables WHERE family_name = "田中" ORDER BY birthday DESC;
    $people = $test_tables->find('all', [
        "conditions" => ["TestTable.family_name = " => "田中"],
        "order" => ['TesetTable.birthday' => 'DESC'],
    ])->all();

    // SELECT family_name, first_name FROM test_tables WHERE family_name = "田中";
    $people = $test_tables->find('all', [
        "conditions" => ["TestTable.family_name = " => "田中"],
        "fields" => ["TestTable.family_name", "TestTable.firstname"],
    ])->all();

    // SELECT * FROM test_tables WHERE id = 1000
    $person = $test_tables->get(1000);

    // SELECT * FROM test_tables WHERE id = 1500
    $person = $test_tables->get(1500);
    $person->family_name = "田中";
    // UPDATE test_tables SET family_name = "田中" WHERE id = 1500
    $test_tables->save($person);

    // INSERT INTO test_tables (id, family_name, first_name, gender, birthday, email, ip_address, code, register_date) VALUES (100001, "田中", "一郎", 1, 20000101, "hogehoge@example.com", "192.168.1.1", 1234, UNIX_TIMESTAMP())
    $person = $test_tables->newEntity();
    $person->id = 100001;
    $person->family_name = "田中";
    $person->first_name = "一郎";
    $person->gender = 1;
    $person->birthday = 20000101;
    $person->email = "hogehoge@example.com";
    $person->ip_address = "192.168.1.1";
    $person->code = 1234;
    $person->register_date = time();
    $test_tables->save($person);

    // SELECT * FROM test_tables WHERE id = 100001
    $person = $test_tables->get(100001);
    // DELETE FROM test_tables WHERE id = 100001
    $test_tables->delete($person);

FuelPHP

fuelphp.com

使用例

<?php
    // SELECT * FROM test_tables WHERE family_name = "田中";
    $people = Model_TestTable::query()->where("family_name", "田中")->get();

    // SELECT * FROM test_tables WHERE family_name = "田中" AND gender = 1;
    $people = Model_TestTable::find("all", [
        "where" => [
            ["family_name", "田中"],
            ["gender", 1],
        ]
    ]);

    //SELECT * FROM test_tables WHERE family_name = "田中" OR family_name = "山本";
    $people = Model_TestTable::find("all", [
        "where" => [
            ["family_name", "田中"],
            "or" => [
                ["family_name", "山本"],
            ]
        ]
    ]);


    //SELECT COUNT(*) FROM test_tables WHERE family_name = "田中";
    $number_of_people = Model_TestTable::query()->where("family_name", "田中")->count();

    // SELECT * FROM test_tables WHERE family_name = "田中" LIMIT 10, 5;
    $people = Model_TestTable::query()->where("family_name", "田中")->limit(5)->offset(10)->get();

    // SELECT * FROM test_tables WHERE family_name = "田中" ORDER BY birthday ASC;
    $people = Model_TestTable::query()->where("family_name", "田中")->order_by("birthday", "asc")->get();

    // SELECT * FROM test_tables WHERE family_name = "田中" ORDER BY birthday DESC;
    $people = Model_TestTable::query()->where("family_name", "田中")->order_by("birthday", "desc")->get();

    // SELECT family_name, first_name FROM test_tables WHERE family_name = "田中";
    $people = Model_TestTable::query()->where("family_name", "田中")->select("family_name", "first_name")->get();

    // SELECT * FROM test_tables WHERE id = 1000
    $people = Model_TestTable::find(1000);

    // SELECT * FROM test_tables WHERE id = 1500
    $person = Model_TestTable::find(1500);
    $person->family_name = "田中";
    // UPDATE test_tables SET family_name = "田中" WHERE id = 1500
    $person->save();

    // INSERT INTO test_tables (id, family_name, first_name, gender, birthday, email, ip_address, code, register_date) VALUES (100001, "田中", "一郎", 1, 20000101, "hogehoge@example.com", "192.168.1.1", 1234, UNIX_TIMESTAMP())
    $person = new Model_TestTable();
    $person->id = 100001;
    $person->family_name = "田中";
    $person->first_name = "一郎";
    $person->gender = 1;
    $person->birthday = 20000101;
    $person->email = "hogehoge@example.com";
    $person->ip_address = "192.168.1.1";
    $person->code = 1234;
    $person->register_date = time();
    $person->save();

    // SELECT * FROM test_tables WHERE id = 100001
    $person = Model_TestTable::find(100001);
    // DELETE FROM test_tables WHERE id = 100001
    $person->delete();

CodeIgniter

codeigniter.com

使用例

<?php
    $test_tables = $db->table("test_tables");

    // SELECT * FROM test_tables WHERE family_name = "田中";
    $people = $test_tables->where("family_name", "田中")->get();

    // SELECT * FROM test_tables WHERE family_name = "田中" AND gender = 1;
    $people = $test_tables->where("family_name", "田中")->where("gender", 1)->get();

    //SELECT * FROM test_tables WHERE family_name = "田中" OR family_name = "山本";
    $people = $test_tables->where("family_name", "田中")->orWhere("family_name", "山本")->get();

    //SELECT COUNT(*) FROM test_tables WHERE family_name = "田中";
    $number_of_people = $test_tables->where("family_name", "田中")->selectCount("*")->get();


    // SELECT * FROM test_tables WHERE family_name = "田中" LIMIT 10, 5;
    $people = $test_tables->where("family_name", "田中")->limit(10, 20)->get();

    // SELECT * FROM test_tables WHERE family_name = "田中" ORDER BY birthday ASC;
    $people = $test_tables->where("family_name", "田中")->orderBy("birthday", "ASC")->get();

    // SELECT * FROM test_tables WHERE family_name = "田中" ORDER BY birthday DESC;
    $people = $test_tables->where("family_name", "田中")->orderBy("birthday", "DESC")->get();


    // SELECT family_name, first_name FROM test_tables WHERE family_name = "田中";
    $people = $test_tables->where("family_name", "田中")->select("family_name", "first_name")->get();

    // SELECT * FROM test_tables WHERE id = 1000
    $person = $test_tables->where("id", 1000)->get();

    $test_tables->set("family_name", "田中");
    $test_tables->where("id", 1500);
    // UPDATE test_tables SET family_name = "田中" WHERE id = 1500
    $test_tables->update();

    // INSERT INTO test_tables (id, family_name, first_name, gender, birthday, email, ip_address, code, register_date) VALUES (100001, "田中", "一郎", 1, 20000101, "hogehoge@example.com", "192.168.1.1", 1234, UNIX_TIMESTAMP())
    $data = [
        "id" => 100001,
        "family_name" => "田中",
        "first_name" => "一郎",
        "gender" => 1,
        "birthday" => 20000101,
        "email" => "hogehoge@example.com",
        "ip_address" => "192.168.1.1",
        "code" => 1234,
        "register_date" => time(),
    ];
    $test_tables->set($data);
    $test_tables->insert();

    $test_tables->where("id", 100001);
    // DELETE FROM test_tables WHERE id = 100001
    $test_tables->delete();

Kohana

kohanaframework.org

使用例

<?php
    $test_tables = ORM::factory("test_tables");

    // SELECT * FROM test_tables WHERE family_name = "田中";
    $people = $test_tables->where("family_name", "=", "田中")->find_all();

    // SELECT * FROM test_tables WHERE family_name = "田中" AND gender = 1;
    $people = $test_tables->where("family_name", "=", "田中")->where("gender", 1)->find_all();

    //SELECT * FROM test_tables WHERE family_name = "田中" OR family_name = "山本";
    $people = $test_tables->where("family_name", "=", "田中")->or_where("family_name", "=", "山本")->find_all();

    //SELECT COUNT(*) FROM test_tables WHERE family_name = "田中";
    $number_of_people = $test_tables->where("family_name", "=", "田中")->count_all();

    // SELECT * FROM test_tables WHERE family_name = "田中" LIMIT 10, 5;
    $people = $test_tables->where("family_name", "=", "田中")->offset(10)->limit(5)->find_all();

    // SELECT * FROM test_tables WHERE family_name = "田中" ORDER BY birthday ASC;
    $people = $test_tables->where("family_name", "=", "田中")->->order_by("birthday", "ASC")->find_all();

    // SELECT * FROM test_tables WHERE family_name = "田中" ORDER BY birthday DESC;
    $people = $test_tables->where("family_name", "=", "田中")->order_by("birthday", "DESC")->find_all();

    // SELECT family_name, first_name FROM test_tables WHERE family_name = "田中";
    $people = $test_tables->where("family_name", "田中")->select("family_name", "first_name")->find_all();

    // SELECT * FROM test_tables WHERE id = 1000
    $person = $test_tables->find(1000);

    // SELECT * FROM test_tables WHERE id = 1500
    $person = $test_tables->find(1500);
    $person->family_name = "田中";
    // UPDATE test_tables SET family_name = "田中" WHERE id = 1500
    $person->save();

    // INSERT INTO test_tables (id, family_name, first_name, gender, birthday, email, ip_address, code, register_date) VALUES (100001, "田中", "一郎", 1, 20000101, "hogehoge@example.com", "192.168.1.1", 1234, UNIX_TIMESTAMP())
    $person->ORM::factory("test_tables");
    $person->id = 100001;
    $person->family_name = "田中";
    $person->first_name = "一郎";
    $person->gender = 1;
    $person->birthday = 20000101;
    $person->email = "hogehoge@example.com";
    $person->ip_address = "192.168.1.1";
    $person->code = 1234;
    $person->register_date = time();
    $person->save();

    // SELECT * FROM test_tables WHERE id = 100001
    $person = $test_tables->find(100001);
    // DELETE FROM test_tables WHERE id = 100001
    $person->delete();

Phalcon

phalcon.io

使用例

<?php
    // SELECT * FROM test_tables WHERE family_name = "田中";
    $people = TestTable::find(
        [
            "conditions" => "family_name = '田中'",
        ]
    );

    // SELECT * FROM test_tables WHERE family_name = "田中" AND gender = 1;
    $people = TestTable::find(
        [
            "conditions" => "family_name = '田中' AND gender = 1",
        ]
    );

    //SELECT * FROM test_tables WHERE family_name = "田中" OR family_name = "山本";
    $people = TestTable::find(
        [
            "conditions" => "family_name = '田中' OR family_name = '山本'",
        ]
    );

    //SELECT COUNT(*) FROM test_tables WHERE family_name = "田中";
    $number_of_people = TestTable::count(
        [
            "conditions" => "family_name = '田中'",
        ]
    );

    // SELECT * FROM test_tables WHERE family_name = "田中" LIMIT 10, 5;
    $people = TestTable::find(
        [
            "conditions" => "family_name = '田中'",
            "offset" => 10,
            "limit" => 5,
        ]
    );

    // SELECT * FROM test_tables WHERE family_name = "田中" ORDER BY birthday ASC;
    $people = TestTable::find(
        [
            "conditions" => "family_name = '田中'",
            "order" => "birthday ASC",
        ]
    );

    // SELECT * FROM test_tables WHERE family_name = "田中" ORDER BY birthday DESC;
    $people = TestTable::find(
        [
            "conditions" => "family_name = '田中'",
            "order" => "birthday DESC",
        ]
    );

    // SELECT family_name, first_name FROM test_tables WHERE family_name = "田中";
    $people = TestTable::find(
        [
            "conditions" => "family_name = '田中'",
            "columns" => "family_name, first_name",
        ]
    );

    // SELECT * FROM test_tables WHERE id = 1000
    $person = TestTable::findFirst(1000);

    // SELECT * FROM test_tables WHERE id = 1500
    $person = TestTable::findFirst(1500);
    $person->family_name = "田中";
    // UPDATE test_tables SET family_name = "田中" WHERE id = 1500
    $person->save();

    // INSERT INTO test_tables (id, family_name, first_name, gender, birthday, email, ip_address, code, register_date) VALUES (100001, "田中", "一郎", 1, 20000101, "hogehoge@example.com", "192.168.1.1", 1234, UNIX_TIMESTAMP())
    $person = new TestTable();
    $person->id = 100001;
    $person->family_name = "田中";
    $person->first_name = "一郎";
    $person->gender = 1;
    $person->birthday = 20000101;
    $person->email = "hogehoge@example.com";
    $person->ip_address = "192.168.1.1";
    $person->code = 1234;
    $person->register_date = time();
    $person->save();

    // SELECT * FROM test_tables WHERE id = 100001
    $person = TestTable::findFirst(100001);
    // DELETE FROM test_tables WHERE id = 100001
    $person->delete();

最後に

やり方はさまざまですね。どれが良いとかそういうのは無く、好みの問題ですよね。

doctrineが無いって？気のせいです。。。

というわけでこれらを参考に設計しようかと思います。