class Db_Select

Class for building SQL SELECT requests represents an implementation of the Zend_Db_select interface, features simplified logic and better performance.

Functionality is practically identical to that of Zend_Db_Select, so it is easy to use for those, who is familiar with the latter. Introduced in DVelum 0.9

class Db_Select

{

/**
* Add a FROM clause to the query
* @param mixed $table string table name or array('alias'=>'tablename')
* @param mixed $columns
* @return Db_Select
*/
public function from($table , $columns = "*")

Examples:

	$sql = new Db_Select();

1)
	SELECT `table`.`id`, `table`.`name`, `table`.`date` FROM `table`;
	$sql->from('table','id,name,date');
	

2)
	SELECT `table`.`id`, `table`.`title`, `table`.`name` FROM `table`;
	$sql->from('table', array('id','title','name'));
	

3)
	SELECT COUNT(*) AS `count`, `table`.`name` AS `field_name`, `table`.`order` FROM `table`;
	$sql->from('table', array(
		   'count'=>'COUNT(*)',
		   'field_name'=>'name',
		   'order'
		   )
	);	
	
4)
	SELECT COUNT(*) AS `count`, `t`.`name` AS `field_name`, `t`.`order` FROM `some_table` AS `t`;
	$sql->from(
	   array('t'=>'some_table'), 				 	
	   array(
	      'count'=>'COUNT(*)',
	      'field_name'=>'name',
	      'order'
	      )
	);

/**
* Add a DISTINCT clause
* @return Db_Select
*/
public function distinct()

Example:

    SELECT DISTINCT `table`.* FROM `table`;
    $sql = new Db_Select();
    $sql->from('table')->distinct();	

/**
* Add a WHERE clause
* @param string $condition
* @param mixed $bind
* @return Db_Select
*/
public function where($condition , $bind = false)

Examples:

    $sql = new Db_Select();

1)
    SELECT `table`.* FROM `table` WHERE (`id` =7) ORDER BY `name` DESC;
    $sql->from('table')->where('`id` =?',7)->order('name DESC');
	
2)
    SELECT `table`.* FROM `table` WHERE (`id` =0.600000) ORDER BY `name` DESC;
    $sql->from('table')->where('`id` =?',0.6)->order('name DESC');

3)
    SELECT `table`.* FROM `table` WHERE (`code` ='code') ORDER BY `name` DESC;
    $sql->from('table')->where('`code` =?','code')->order('name DESC');

4)
    SELECT `table`.* FROM `table` WHERE (`code` IN('first','second')) ORDER BY `name` DESC;
 
    $sql->from('table')
    ->where('`code` IN(?)',array('first','second'))
    ->order('name DESC');

5)
    SELECT `table`.* FROM `table` WHERE (`id` IN(7,8,9)) ORDER BY `name` DESC;
    $sql->from('table')->where('`id` IN(?)',array(7,8,9))->order('name DESC');

/**
* Add a OR WHERE clause to the query
* @param string $condition
* @param mixed $bind
* @return Db_Select
*/
public function orWhere($condition , $bind = false)

Example:

    SELECT `table`.*
    FROM `table`
    WHERE
        (`id` =7 AND `code` ='code')
    OR
	(`id` =8 )
    OR
	(`id` =9);
    $sql = new Db_Select();
    $sql->from('table')
    ->where('`id` =?',7)
    ->where('`code` =?',"code")
    ->orWhere('`id` =?',8)
    ->orWhere('`id` =?',9);

/**
* Add a GROUP clause to the query
* @param mixed $fields string field name or array of field names
* @return Db_Select
*/
public function group($fields)

Examples:

    $sql = new Db_Select();

1)	
    SELECT `table`.* FROM `table` GROUP BY `type`,`cat`;
    $sql->from('table')->group(array('type','cat'));
		
2)	
    SELECT `table`.* FROM `table` GROUP BY `type`;	
    $sql->from('table')->group('type');
3)
    SELECT `table`.* FROM `table` GROUP BY `type`,`cat`;
    $sql->from('table')->group('type,cat');
		

/**
* Add a HAVING clause to the query
* @param string $condition
* @param mixed $bind
* @return Db_Select
*/
public function having($condition , $bind = false)

Example:

    SELECT CONCAT(code,"i") AS `c_code` FROM `sb_content` HAVING (`c_code` ='index');
    $sql = new Db_Select();
    $sql->from('sb_content' , array('c_code'=>'CONCAT(code,"i")'))
        ->having('`c_code` =?',"index");

/**
* Add a OR HAVING clause to the query
* @param string $condition
* @param mixed $bind
* @return Db_Select
*/
public function orHaving($condition , $bind = false)

Example:

    SELECT CONCAT(code,"i") AS `c_code` FROM `sb_content` HAVING (`c_code` ='index') OR (`c_code` ='articles');

    $sql = new Db_Select();
    $sql->from('sb_content' , array('c_code'=>'CONCAT(code,"i")'))
     ->having('`c_code` =?',"index")
     ->orHaving('`c_code` =?',"articles");

/**
* Adding another table to the query using JOIN
* @param string $condition
* @param mixed $bind
* @return Db_Select
*/
public function join($table, $cond, $cols ='*')

Example:

    SELECT `a`.*, `b`.`title`, `b`.`time` FROM `table` AS `a` INNER JOIN `table2` AS `b` ON a.code = b.id;
    $sql = new Db_Select();
    $sql->from(array('a'=>'table'))
        ->join(
		array('b'=>'table2'),
		'a.code = b.id',
		array('title','time')
	);
	

/**
* Adding another table to the query using INNER JOIN
* @param mixed $table
* @param mixed $cond
* @param mixed $cols
* @return Db_Select
*/
public function joinInner($table, $cond, $cols ='*')

Example:

    SELECT `a`.*, `b`.`title`, `b`.`time`, `c`.`title` AS `ctitle`, `c`.`time` AS `ctime`
    FROM
	`table` AS `a`
    INNER JOIN `table2` AS `b` ON a.code = b.id
    INNER JOIN `table3` AS `c` ON b.code = c.id;

    $sql = new Db_Select();
    $sql->from(array('a'=>'table'))
    ->joinInner(
		array('b'=>'table2'),
		'a.code = b.id',
		array('title','time')
	)
    ->joinInner(
		array('c'=>'table3'),
		'b.code = c.id',
		array('ctitle'=>'title','ctime'=>'time')
	);
	

/**
* Adding another table to the query using LEFT JOIN
* @param mixed $table
* @param mixed $cond
* @param mixed $cols
* @return Db_Select
*/
public function joinLeft($table, $cond, $cols ='*')

/**
* Adding another table to the query using RIGHT JOIN
* @param mixed $table
* @param mixed $cond
* @param mixed $cols
* @return Db_Select
*/
public function joinRight($table, $cond, $cols ='*')

/**
* Adding a LIMIT clause to the query
* @param integer $count
* @param integer $offset - optional
* @return Db_Select
*/
public function limit($count, $offset = false)

Examples:

    $sql = new Db_Select();
1)
    SELECT `table`.* FROM `table` LIMIT 20,10;
    $sql->from('table')->limit(10 ,20);
		
2)				
    SELECT `table`.* FROM `table` LIMIT 10;
    $sql->from('table')->limit(10);	

/**
* Setting the limit and count by page number.
* @param int $page Limit results to this page number.
* @param int $rowCount Use this many rows per page.
* @return Db_Select This Db_Select object.
*/
public function limitPage($page, $rowCount)

Example:

    SELECT `table`.* FROM `table` LIMIT 30,10;
    $sql = new Db_Select();
    $sql->from('table')->limitPage(4, 10); 

/**
* Adding an ORDER clause to the query
* @param mixed $spec
* @return Db_Select
*/
public function order($spec)

Examples:

    $sql = new Db_Select();

1)
    SELECT `table`.* FROM `table` ORDER BY `name` DESC,`group` ASC;
    $sql->from('table')->order(array('name'=>'DESC','group'=>'ASC'));

2)
    SELECT `table`.* FROM `table` ORDER BY `name`,`group`;
    $sql->from('table')->order(array('name','group'));
	
3)
    SELECT `table`.* FROM `table` ORDER BY name ASC,group DESC;
    $sql->from('table')->order(array('name ASC','group DESC'));
			
4)
    SELECT `table`.* FROM `table` ORDER BY `name` DESC,`group` ASC;
    $sql->from('table')->order('name DESC, group ASC');

5)
    SELECT `table`.* FROM `table` ORDER BY `name` DESC;
    $sql->from('table')->order('name DESC');		
	

/**
* Quote a string as an identifier
* @param string $str
* @return string
*/
public function quoteIdentifier($str)

}

comments powered by Disqus