読者です 読者をやめる 読者になる 読者になる

FuelPHPのクエリビルダを表にまとめた

※Ver1.2の情報なので最新バージョンと合わない部分があるかもしれません...

タイトルそのまま。FuelPHP1.2のクエリビルダ関連を表にまとめました。

SELECT // SELECT * FROM... \DB::select()
// SELECT `hoge`, `fuga` FROM... \DB::select(column1, column2...) \DB::select_array(array(column1, column2...))
// SELECT `hoge` AS `h`, `fuga` AS `f` FROM... \DB::select(array(column1, alias), array(column2, alias)...) \DB::select_array(array(column1, alias), array(column2, alias)...)
DISTINCT // SELECT DISTINCT `hoge` FROM... distinct(true)
FROM // SELECT * FROM `hoge` from(table)
// SELECT * FROM `hoge` AS `h` from(array(table, alias))
JOIN // SELECT * FROM `hoge` INNER JOIN `fuga` ON (`hoge.id` = `fuga.hoge_id`) join(table)->on(column1, '=', column2)
// SELECT * FROM `hoge` LEFT JOIN `fuga` ON (`hoge.id` = `fuga.hoge_id`) join(table, type)->on(column1, '=', column2) ※ type : 『INNER』 『LEFT』 『RIGHT』 ...
// SELECT * FROM `hoge` AS `h` INNER JOIN `fuga` AS `f` ON (`h.id` = `f.hoge_id`) join(array(column, alias))->on(column1, '=', column2)
WHERE // WHERE `foo` = `bar` where(column, value)
// WHERE `foo` != `bar` where(column, op, value) ※ op : 『=』『!=』『>』『<』 ... ※opが!=でvalueがNULLの場合、クエリは自動的にIS NOT NULLに
// WHERE `hoge` IN (`foo`, `bar`) where(column, 'in', array(value1, value2...)) where(column, 'not in', array(value1, value2...))
// WHERE `hoge` LIKE `%fuga%` where(column, 'like', value)
// WHERE `hoge` BETWEEN `1` AND `10` where(column, 'between', array(value1, value2))
// AND `hoge` = `fuga` and_where(column, value)
// OR `hoge` = `fuga` or_where(column, value)
// (`hoge` = `fuga` OR `foo` = `bar`) where_open() ->where(column, value) ->or_where(column, value) ->where_close()
// OR (`hoge` = `fuga` AND `foo` = `bar`) or_where_open() ->where(column, value) ->and_where(column, value) ->or_where_close()
HAVING // HAVING `hoge` = `fuga` having(column, op, value) ※ op : 『=』『!=』『>』『<』 ...
// AND HAVING `hoge` = `fuga` and_having(column, op, value) ※ op : 『=』『!=』『>』『<』 ...
// OR HAVING `hoge` = `fuga` or_having(column, op, value) ※ op : 『=』『!=』『>』『<』 ...
// HAVING (`hoge` = `fuga` AND `foo` = `bar`) having_open() ->having(column, op, value)) ->and_having(column, op, value)) ->having_close()
// AND (HAVING (`hoge` = `fuga` OR `foo` = `bar`)) and_having_open() ->having(column, op, value)) ->or_having(column, op, value)) ->and_having_close()
// OR (HAVING (`hoge` = `fuga` AND `foo` = `bar`)) or_having_open() ->having(column, op, value)) ->and_having(column, op, value)) ->or_having_close()
ORDER BY // ORDER BY `hoge` order_by(column)
// ORDER BY `hoge` ASC order_by(column, 'asc')
// ORDER BY `hoge` DESC order_by(column, 'desc')
GROUP BY // GROUP BY `hoge` group_by(column)
LIMIT // LIMIT 10 limit(value)
OFFSET // OFFSET 10 offset(value)
INSERT // INSERT INTO `hoge` (`foo`, `fuga`) VALUES (`bar`, `buz`); \DB::insert(table)->set(array( column1 => value1, column2 => value2... ))->execute();
\DB::insert(table)->columns(array( column1, column2... ))->values(array( value1, value2... ))->execute();
UPDATE // UPDATE `hoge` SET `foo` = `bar` WHERE `fuga` = `buz`; \DB::update(table) ->value(column, value) ->where(WHERE参照) ->execute();
\DB::update(table)->set(array( column1 => value1, column2 => value2... )) ->where(WHERE参照) ->execute();
DELETE // DELETE FROM `hoge` WHERE `foo` = `bar`; \DB::delete(table) ->where(WHERE参照) ->execute();

クエリビルダでCOUNTを取りたい場合はこんな感じ

$result = DB::select(DB::expr('COUNT(*) as count'))->from('users')->execute();
$result_arr = $result->current();
$count = $result_arr['count'];