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

PHP 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'];