Kohana Query Builder

표현 예제

Sql 

UPDATE `pages` SET `views` = views + 1 WHERE `id` = 1


변환 


DB::update('pages')
  	->set(array('views' => DB::expr('views + 1')))
  	->where('id', '=', 1)->execute(); 


Transactions


BEGIN WORK;


DB::query(NULL, "BEGIN WORK")->execute(); 
 
   DB::query(NULL, "BEGIN WORK")->execute();
  
  // Array of Page Data
  $page_data = array(
  	'id'	=> NULL,
  	'title'	=> 'My New Page',
  );
  
  $page = (bool) DB::insert('pages', array_keys($page_data))
  		->values($page_data)
  		->execute();
  
  $page_cat_data = array(
  	'page_count'	=> DB::expr('page_count + 1'),
  );
  
  $page_cat = (bool) DB::update('page_categories')
  		->set($page_cat_data)
  		->where('id', '=', 1)
  		->execute();
  
  if($page AND $page_cat)
  {
  	DB::query(NULL, "COMMIT")->execute();
  }
  else
  {
  	DB::query(NULL, "ROLLBACK")->execute();
  }




Joins


sql 문

  SELECT `articles`.`title`, `users`.`username` 
  FROM `articles` 
      JOIN `users` 
      ON (`users`.`id` = `articles`.`user_id`) 
  WHERE `articles`.`id` = 1
  LIMIT 1
 변환 표현 


 $query = DB::select('articles.title', 'users.username')
		->from('articles')
		->where('articles.id', '=', 1)
			->join('users')
			->on('users.id', '=', 'articles.user_id')
		->limit(1)
		->execute();





복잡한 Select 표현 형식


select 컬럼 지정


 DB::select('column1','column2')->from('table_name');

  ==>  SELECT `column1`, `column2` FROM `table_name`




컬럼에 as 사용하기


DB::select(array('column','my_column'))->from('table_name')->compile($db);
SELECT `column` AS `my_column` FROM `table_name`




조인 하기


 DB::select()->from('table_name')->join('table_2')->on('table_2.table_id', '=', 'table_name.id');


=>  SELECT * FROM `table_name` JOIN `table_2` ON `table_2`.`table_id` = `table_name`.`id`


group_by()


DB::select()->from('table_name')->group_by('column');


 => SELECT * FROM `table_name` GROUP BY `column`


DB::select()->from('table_name')->group_by(array('column1', 'mycol'));


=>  SELECT * FROM `table_name` GROUP BY `column1` AS `mycol`


having() 절 사용 


  DB::select()->from('table_name')->having('column','=','value');


 =>  SELECT * FROM `table_name` HAVING `column` = 'value'




order_by() 절 사용


DB::select()->from('table_name')->order_by('column', 'ASC');

=>  SELECT * FROM `table_name` ORDER BY `column` ASC




limit() 사용


 DB::select()->from('table_name')->limit(10);

=> SELECT * FROM `table_name` LIMIT 10


offset() 사용



  DB::select()->from('table_name')->limit(10)->offset(50);

=>  SELECT * FROM `table_name` WHERE `column` = 'value'


and_where() 사용


 DB::select()->from('table_name')->where('column','=','value')->and_where('column2','=','value');

=> SELECT * FROM `table_name` WHERE `column` = 'value' OR `column2` = 'value'




where_open() 사용


  DB::select()->from('table_name')->where_open()->where('column','=','value')
          ->or_where('column2','=','value')->where_close();
 =>  SELECT * FROM `table_name` WHERE (`column` = 'value' OR `column2` = 'value')


and_where_open() 사용



   DB::select()->from('table_name')->where('column','=','value')->and_where_open()->where('column2','=','value')
          ->or_where('column3','=','value')->and_where_close();


 =>  SELECT * FROM `table_name` WHERE `column` = 'value' AND (`column2` = 'value' OR `column3` = 'value')


or_where_open()


DB::select()->from('table_name')->where('column','=','value')->or_where_open()->where('column2','=','value')
          ->and_where('column3','=','value')->or_where_close();


=>  SELECT * FROM `table_name` WHERE `column` = 'value' OR (`column2` = 'value' AND `column3` = 'value')