class Db_Select (Конструктор SQL запросов)

Класс построения SQL запросов для выборки данных (select).
Представляет из себя реализацию интерфейса Zend_Db_Select, отличается упрощенной логикой и большей производительностью.
Поведение практически идентичное Zend_Db_Select. Доступен в  DVelum 0.9  и выше

class Db_Select
{

/**
* Добавить в запрос условие FROM
* @param mixed $table string table name or array('alias'=>'tablename')
* @param mixed $columns
* @return Db_Select
*/
public function from($table , $columns = "*")

Примеры:

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

/**
* Добавить в запрос условие DISTINCT
* @return Db_Select
*/
public function distinct()

Пример:

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

/**
* Добавить в запрос условие WHERE
* @param string $condition
* @param mixed $bind
* @return Db_Select
*/
public function where($condition , $bind = false)

Примеры:

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

/**
* Добавить в запрос условие OR WHERE
* @param string $condition
* @param mixed $bind
* @return Db_Select
*/
public function orWhere($condition , $bind = false)

Пример:

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

/**
* Добавить в запрос условие GROUP
* @param mixed $fields string field name or array of field names
* @return Db_Select
*/
public function group($fields) Примеры:

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

/**
* Добавить в запрос условие HAVING
* @param string $condition
* @param mixed $bind
* @return Db_Select
*/
public function having($condition , $bind = false)

Пример:

    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");

/**
* Добавить в запрос условие OR HAVING
* @param string $condition
* @param mixed $bind
* @return Db_Select
*/
public function orHaving($condition , $bind = false)

Пример:

    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");

/**
* Добавить другую таблицу используя объединение JOIN
* @param string $condition
* @param mixed $bind
* @return Db_Select
*/
public function join($table, $cond, $cols ='*')

Пример:

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

/**
* Добавить другую таблицу используя объединение INNER JOIN
* @param mixed $table
* @param mixed $cond
* @param mixed $cols
* @return Db_Select
*/
public function joinInner($table, $cond, $cols ='*')

Пример:

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

/**
* Добавить другую таблицу используя объединение LEFT JOIN
* @param mixed $table
* @param mixed $cond
* @param mixed $cols
* @return Db_Select
*/
public function joinLeft($table, $cond, $cols ='*')

/**
* Добавить другую таблицу используя объединение RIGHT JOIN
* @param mixed $table
* @param mixed $cond
* @param mixed $cols
* @return Db_Select
*/
public function joinRight($table, $cond, $cols ='*')

/**
* Добавить в запрос условие LIMIT
* @param integer $count
* @param integer $offset - optional
* @return Db_Select
*/
public function limit($count, $offset = false)

Примеры:

    $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);
       

/**
* Устанавливаем limit и count исходя из номера страницы.
* @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)

Пример:

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

/**
* Добавить в запрос условие ORDER
* @param mixed $spec
* @return Db_Select
*/
public function order($spec)

Примеры:

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

/**
* Квотировать строку как идентификатор
* @param string $str
* @return string
*/
public function quoteIdentifier($str)

}

comments powered by Disqus