PHPのORM idiorm 自分的メモ


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

# select
### 基本select
```php
// find_many
$people = ORM::for_table('person')->find_many();

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


### selectその他いろいろ
```php
// カラムの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 
```php
$param = ['山田', '田中', '鈴木'];
$people = ORM::for_table('person')->where_in('name', $param)->find_many();

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

### where or
```php
$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)
```php
$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
```php
$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
```php
//より下
$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 生
```php
$people = ORM::for_table('person')
            ->where('name', 'Fred')
            ->where_raw('(`age` = ? OR `age` = ?)', array(20, 25))
            ->order_by_asc('name')
            ->find_many();
```






### 発行したSQLを確認する
```php
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
```php
$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 
```php
$count = ORM::for_table('person')->min("age");

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

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

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


### 関数による並び替え
```php
$people = ORM::for_table('person')->order_by_expr('LENGTH(`name`)')->find_many();

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



##### idiorm paris の違い
https://paris.readthedocs.io/en/latest/querying.html



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