티스토리 뷰

웹개발/Php

Kohana Query Builder

yaku 2011. 8. 26. 06:18


표현 예제

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')
 



 
 


 

 
 

'웹개발 > Php' 카테고리의 다른 글

PHP 정규 표현식 10가지 사용 예제  (2) 2011.09.06
Kohana Validation 체크  (0) 2011.08.26
Kohana Request  (0) 2011.08.26
Kohana 3.2 Config 얻기..  (0) 2011.08.24
Kohana 3.2 Custom Error Page 만들기.  (1) 2011.08.24
댓글
D-DAY
최근에 올라온 글
최근에 달린 댓글
Total
Today
Yesterday
«   2024/03   »
1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31
글 보관함