PHPのORM idiorm 自分的メモ

PHPのオブジェクト関係マッピング（Object-relational mapping、O/RM、ORM）、idiormについて自分でよく使うものをまとめました。

select

基本select

// find_many
$people = ORM::for_table('person')->find_many();

// select_many
$people = ORM::for_table('person')->select_many('name', 'age')->find_many();

selectその他いろいろ

// カラムのalias
$people = ORM::for_table('person')->select_many(array('first_name' => 'name'), 'age', 'height')->find_many();

// limit
$people = ORM::for_table('person')->where('gender', 'female')->limit(5)->offset(10)->find_many();

// group by 
$people = ORM::for_table('person')->where('gender', 'female')->group_by('name')->find_many();

// having
$people = ORM::for_table('person')->group_by('name')->having_not_like('name', '%bob%')->find_many();

// count
$people_count = ORM::for_table('person')->select_expr('COUNT(*)', 'count')->find_many();

// now
$people = ORM::for_table('person')->select_many('name', 'age', 'height')->select_expr('NOW()', 'timestamp')->find_many();
// SELECT `name`, `age`, `height`, NOW() AS `timestamp` FROM `person`;

where

where in

$param = ['山田', '田中', '鈴木'];
$people = ORM::for_table('person')->where_in('name', $param)->find_many();

// SELECT * FROM `person` WHERE 'name' IN ('山田', '田中', '鈴木');

where or

$data = [];
$terms = ['都','道','府'];

$records = ORM::for_table('prefecture')->where_in('nameType',$terms)->order_by_asc('population')->find_many();

foreach($records as $record){
    $data[] = $record->name;
}

$records = ORM::for_table('prefecture')->where_like('nameType',"%.$type")->order_by_asc('population')->find_many();

where or (2)

$people = ORM::for_table('person')
            ->where_any_is(array(
                array('name' => 'Joe', 'age' => 10),
                array('name' => 'Fred', 'age' => 20)))
            ->find_many();

// 実行されるSQL
// SELECT * FROM `widget` WHERE (( `name` = 'Joe' AND `age` = '10' ) OR ( `name` = 'Fred' AND `age` = '20' ));

複数のLIKEのOR

$res = ORM::for_table('table') 
            ->select('name') 
            ->where_raw('(`name` LIKE ? OR `yomi` LIKE ?)', array('%XXX%', '%XXX%')) 
            ->findArray();

$res = ORM::for_table('table') 
            ->where_any_is(
            array( array('name' => '%XXX%'), array('yomi' => '%XXX%')), 
            array('name' => "LIKE", 'yomi' => "LIKE")) 
            ->findArray();

○以上○未満のwhere

//より下
$people = ORM::for_table('person')->where_lt('age', 10)->find_many();
//より上
$people = ORM::for_table('person')->where_gt('age', 5)->find_many();
//未満
$people = ORM::for_table('person')->where_lte('age', 10)->find_many();
//以上
$people = ORM::for_table('person')->where_gte('age', 5)->find_many();

where 生

$people = ORM::for_table('person')
            ->where('name', 'Fred')
            ->where_raw('(`age` = ? OR `age` = ?)', array(20, 25))
            ->order_by_asc('name')
            ->find_many();

発行したSQLを確認する

ORM::configure('logging', true);
$sqls = ORM::get_query_log() ;
var_dump($sqls);


echo ORM::get_last_query();
echo ORM::get_query_log();

ORM::configure('logger', function($log_string, $query_time) {
  logsave("test logger", $log_string . ' in ' . $query_time);
});

update

php

$people = ORM::for_table('person')->find_one(5);
$person->name = '山田太郎';
$person->save();

複数レコードある場合は、ループしなければならない

php

$people = ORM::for_table('person')->find_many();
foreach ($people as $person) {
    $person->name = '山田太郎';
    $person->save();
}

insert

php

$p = ORM::for_table('person')->create();
$p->name = 'Joe Bloggs';
$p->age = 40;
$p->save();

delete

php

$test = ORM::for_table('test')->where('name', 'sample')->find_array();
foreach($test as $t) {
  $t->delete();
}

join

$records = ORM::for_table('addresses')
       ->left_outer_join('members', 'members.id=addresses.memid')
       ->find_many();

foreach ($records as $r) {
    echo sprintf("id: %s, name: %s, pref: %s<br>"
        ,$r->id, $r->name, $r->pref);
}

MIN、、MAX、SUM、AVG

$count = ORM::for_table('person')->min("age");

//実行されるSQL
SELECT MIN(age) AS 'min' FROM `person` LIMIT 1;

offset limit

$count = ORM::for_table('person')->offset(2)->limit(3)->find_many();

//実行されるSQL
SELECT * FROM `person` LIMIT 3 OFFSET 2;

関数による並び替え

$people = ORM::for_table('person')->order_by_expr('LENGTH(`name`)')->find_many();

//実行されるSQL
SELECT * FROM `person` ORDER BY LENGTH(`name`)

idiorm paris の違い

---

参考サイト

https://idiorm.readthedocs.io/en/latest/querying.html#raw-queries
https://idiorm.readthedocs.io/en/latest/index.html
https://qiita.com/iritec/items/5342a8b6031c982c85c4