今回は
1.データの取得項目(全項目を取得とかこの項目のみを取得とか)
2.データの取得形式(文字列で取得とか配列で取得とか)
3.データの取得条件、他(where句とかorder by句とか)
の
3.データの取得条件、他(where句とかorder by句とか)を見ていきます。多分これがクエリビルダの検索系におけるキモ
■ テーブル指定:from(‘table01‘)
$q = DB::select()->from(‘tbl_test01’);
var_dump($q->execute());SELECT * FROM `tbl_test01`
■ where条件を指定:where(‘column01‘, value01)
$q = DB::select()->from(‘tbl_test01’);
$q->where(‘column_varchar01’, ‘あああ’);
var_dump($q->execute());SELECT * FROM `tbl_test01` WHERE `column_varchar01` = ‘あああ’
■ where条件を指定(イコール以外):where(‘column01‘, operator01, value01)
$q = DB::select()->from(‘tbl_test01’);
$q->where(‘column_varchar01’, ‘<>’, ‘あああ’);
var_dump($q->execute());SELECT * FROM `tbl_test01` WHERE `column_varchar01` <> ‘あああ’
■ where条件を指定(like句):where(‘column01‘, ‘like’, ‘value01‘)
$q = DB::select()->from(‘tbl_test01’);
$q->where(‘column_varchar01’, ‘like’, ‘%ああ%’);
var_dump($q->execute());SELECT * FROM `tbl_test01` WHERE `column_varchar01` LIKE ‘%ああ%’
■ where条件を指定(in句):where(‘column01‘, ‘in’, array(‘value01‘, ‘value02‘))
$q = DB::select()->from(‘tbl_test01’);
$q->where(‘column_varchar01’, ‘in’, array(‘あああ’, ‘あああ2’));
var_dump($q->execute());SELECT * FROM `tbl_test01` WHERE `column_varchar01` IN (‘あああ’, ‘あああ2’)
■ where条件を指定(not in句):where(‘column01‘, ‘not in’, array(‘value01‘, ‘value02‘))
$q = DB::select()->from(‘tbl_test01’);
$q->where(‘column_varchar01’, ‘not in’, array(‘あああ’, ‘あああ2’));
var_dump($q->execute());SELECT * FROM `tbl_test01` WHERE `column_varchar01` NOT IN (‘あああ’, ‘あああ2’)
■ where条件を指定(between句):where(‘column01‘, ‘between’, array(‘value01‘, ‘value02‘))
$q = DB::select()->from(‘tbl_test01’);
$q->where(‘pk_id’, ‘between’, array(2, 7));
var_dump($q->execute());SELECT * FROM `tbl_test01` WHERE `pk_id` BETWEEN 2 AND 7
■ where条件を複数指定(and条件):where()を複数指定。
$q = DB::select()->from(‘tbl_test01’);
$q->where(‘column_varchar01’, ‘あああ’);
$q->where(‘column_varchar02’, ‘いいい’);
var_dump($q->execute());SELECT * FROM `tbl_test01` WHERE `column_varchar01` = ‘あああ’ AND `column_varchar02` = ‘いいい’
■ where条件を複数指定(and条件):and_where(‘column02‘, value02)
※「where」と「and_where」は同じ動き。
$q = DB::select()->from(‘tbl_test01’);
$q->where(‘column_varchar01’, ‘あああ’);
$q->and_where(‘column_varchar02’, ‘いいい’);
var_dump($q->execute());SELECT * FROM `tbl_test01` WHERE `column_varchar01` = ‘あああ’ AND `column_varchar02` = ‘いいい’
■ where条件を複数指定(or条件):or_where(‘column02‘, value02)
$q = DB::select()->from(‘tbl_test01’);
$q->where(‘column_varchar01’, ‘あああ’);
$q->or_where(‘column_varchar02’, ‘いいい’);
var_dump($q->execute());SELECT * FROM `tbl_test01` WHERE `column_varchar01` = ‘あああ’ OR `column_varchar02` = ‘いいい’
■ where条件を複数指定(複合条件その1):where_open()、where_close()
$q = DB::select()->from(‘tbl_test01’);
$q->where(‘column_varchar01’, ‘あああ’);
$q->where_open();
$q->where(‘column_varchar02’, ‘いいい’);
$q->or_where(‘column_varchar02’, ‘いいい2’);
$q->where_close();
var_dump($q->execute());SELECT * FROM `tbl_test01` WHERE `column_varchar01` = ‘あああ’ AND (`column_varchar02` = ‘いいい’ OR `column_varchar02` = ‘いいい2’)
■ where条件を複数指定(複合条件その2):and_where_open()、and_where_close()
※「where_open」と「and_where_open」、「where_close」と「and_where_close」は同じ動き。
$q = DB::select()->from(‘tbl_test01’);
$q->where(‘column_varchar01’, ‘あああ’);
$q->and_where_open();
$q->where(‘column_varchar02’, ‘いいい’);
$q->or_where(‘column_varchar02’, ‘いいい2’);
$q->and_where_close();
var_dump($q->execute());SELECT * FROM `tbl_test01` WHERE `column_varchar01` = ‘あああ’ AND (`column_varchar02` = ‘いいい’ OR `column_varchar02` = ‘いいい2’)
■ where条件を複数指定(複合条件その3):or_where_open()、or_where_close()
$q = DB::select()->from(‘tbl_test01’);
$q->where(‘column_varchar01’, ‘あああ’);
$q->or_where_open();
$q->where(‘column_varchar02’, ‘いいい2’);
$q->where(‘column_int02’, ‘>’, 1);
$q->or_where_close();
var_dump($q->execute());SELECT * FROM `tbl_test01` WHERE `column_varchar01` = ‘あああ’ OR (`column_varchar02` = ‘いいい2’ AND `column_int02` > 1)
■ テーブル結合(内部結合):->join(‘table02‘)-on(‘table01.column01‘, ‘=’, ‘table02.column02‘)
$q = DB::select()->from(‘tbl_test01’);
$q->join(‘tbl_test02’)->on(‘tbl_test01.pk_id’, ‘=’, ‘tbl_test02.test01_pk’);
var_dump($q->execute());SELECT * FROM `tbl_test01` JOIN `tbl_test02` ON (`tbl_test01`.`pk_id` = `tbl_test02`.`test01_pk`)
■ テーブル結合(外部結合):->join(‘table02‘,’LEFT/RIGHT‘)
$q = DB::select()->from(‘tbl_test01’);
$q->join(‘tbl_test02’, ‘LEFT’)->on(‘tbl_test01.pk_id’, ‘=’, ‘tbl_test02.test01_pk’);
var_dump($q->execute());SELECT * FROM `tbl_test01` LEFT JOIN `tbl_test02` ON (`tbl_test01`.`pk_id` = `tbl_test02`.`test01_pk`)
■ テーブル結合(テーブルに別名):->join(array(‘table02‘, ‘alias_table_name‘))
$q = DB::select()->from(‘tbl_test01’);
$q->join(array(‘tbl_test02’, ‘tbl02’))->on(‘tbl_test01.pk_id’, ‘=’, ‘tbl02.test01_pk’);
var_dump($q->execute());SELECT * FROM `tbl_test01` JOIN `tbl_test02` AS `tbl02` ON (`tbl_test01`.`pk_id` = `tbl02`.`test01_pk`)
■ テーブル結合(結合条件複数and条件):and_on(‘table01.column01‘, ‘=’, ‘table02.column02′)
$q = DB::select()->from(‘tbl_test01’);
$q->join(‘tbl_test02’)->on(‘tbl_test01.pk_id’, ‘=’, ‘tbl_test02.test01_pk’);
$q->and_on(‘tbl_test01.column_varchar01’, ‘=’, ‘tbl_test02.column_varchar21’);
var_dump($q->execute());SELECT * FROM `tbl_test01` JOIN `tbl_test02` ON (`tbl_test01`.`pk_id` = `tbl_test02`.`test01_pk` AND `tbl_test01`.`column_varchar01` = `tbl_test02`.`column_varchar21`)
■ テーブル結合(結合条件複数or条件):or_on(‘table01.column01‘, ‘=’, ‘table02.column02‘)
$q = DB::select()->from(‘tbl_test01’);
$q->join(‘tbl_test02’)->on(‘tbl_test01.pk_id’, ‘=’, ‘tbl_test02.test01_pk’);
$q->or_on(‘tbl_test01.column_varchar01’, ‘=’, ‘tbl_test02.column_varchar21’);
var_dump($q->execute());SELECT * FROM `tbl_test01` JOIN `tbl_test02` ON (`tbl_test01`.`pk_id` = `tbl_test02`.`test01_pk` OR `tbl_test01`.`column_varchar01` = `tbl_test02`.`column_varchar21`)
■ group by句:->group_by(‘column01‘)
$q = DB::select()->from(‘tbl_test01’)->group_by(‘column_varchar01’);
var_dump($q->execute());SELECT * FROM `tbl_test01` GROUP BY `column_varchar01`
■ group by句(複数項目):->group_by(‘column01‘, ‘column02‘)
$q = DB::select()->from(‘tbl_test01’)->group_by(‘column_varchar01’, ‘column_varchar02’);
var_dump($q->execute());SELECT * FROM `tbl_test01` GROUP BY `column_varchar01`, `column_varchar02`
■ group by句(having句指定):->having(‘column01‘, operator01, ‘column02‘)
$q = DB::select()->from(‘tbl_test01’)->group_by(‘column_varchar01’);
$q->having(‘column_varchar01’, ‘!=’, ‘あああ’);
var_dump($q->execute());SELECT * FROM `tbl_test01` GROUP BY `column_varchar01` HAVING `column_varchar01` != ‘あああ’
※「having()」関係のメソッドは「having()」以外に
・and_having()
・or_having()
・having_open()
・having_close()
・and_having_open()
・and_having_close()
・or_having_open()
・or_having_close()
がありますが、使い方は「where()」関連と同じなので(書くの面倒くさいので)省略。
■ order by句:->order_by(‘column01‘)
$q = DB::select()->from(‘tbl_test01’)->order_by(‘column_varchar01’);
var_dump($q->execute());SELECT * FROM `tbl_test01` ORDER BY `column_varchar01`
■ order by句(降順):->order_by(‘column01‘, ‘desc’)
$q = DB::select()->from(‘tbl_test01’)->order_by(‘column_varchar01’, ‘desc’);
var_dump($q->execute());SELECT * FROM `tbl_test01` ORDER BY `column_varchar01` DESC
■ order by句(複数その1):order_by()を複数くっつける
$q = DB::select()->from(‘tbl_test01’)->order_by(‘column_varchar01’, ‘desc’);
$q->order_by(‘column_varchar02’);
var_dump($q->execute());SELECT * FROM `tbl_test01` ORDER BY `column_varchar01` DESC, `column_varchar02`
■ order by句(複数その2):DB::expr()を使う
$q = DB::select()->from(‘tbl_test01’)->order_by(DB::expr(‘column_varchar01, column_varchar02 DESC’));
var_dump($q->execute());SELECT * FROM `tbl_test01` ORDER BY column_varchar01, column_varchar02 DESC
■ limit句:->limit(num)
$q = DB::select()->from(‘tbl_test01’)->limit(2);
var_dump($q->execute());SELECT * FROM `tbl_test01` LIMIT 2
■ offset句:->offset(num)
$q = DB::select()->from(‘tbl_test01’)->limit(5);
$q->offset(2);
var_dump($q->execute());SELECT * FROM `tbl_test01` LIMIT 5 OFFSET 2
以上で、FuelPHPのクエリビルダ検索関連を完了