Генератор SQL запросов на PHP

Где-то полтора года назад я начал заниматься web разработкой. Начинал с функционального программирования. Примерно пол года назад я перешел на ООП и стал использовать MVC архитектуру проектирования. Недавно появилась задача оптимизировать работу с базой данных, т. к. вся связь и работа с базой осуществлялась через один класс. Это было неудобно потому, что все время приходилось вручную писать SQL — запросы. Задача была разбита на 2 этапа:

  1. Написать класс для подключения к базе данных
  2. Написать класс модели для работы с данными

Первая задача решилась очень быстро. Для ее реализации я воспользовался паттерном проектирования Singleton.
Для реализации второй задачи мне понадобилось немного больше времени. За основу был взят простой модуль управления новостями на сайте. Обязанности этого модуля включали в себя стандартный набор функций: выборка, создание, удаление и обновление записей. Класс модели наследует класс DataBase, который собственно и создает подключение к базе данных. Также этот класс отвечает за генерацию sql кода для DML операций. Класс DataBase является абстрактным, что обязывает нас во всех дочерних классах реализовывать DML методы.
Ниже представлен набор абстрактных методов, которые отвечают за управление модулем «Новости».

    //Получаем запись
    abstract public function getRecords($what='*',$where=NULL, $limit=NULL, $order=NULL,$join=NULL,$debug=false);
    //Добавляем запись
    abstract function addRecord($data=array(),$debug=false);
    //Удаляем запись(-и)
    abstract function deleteRecords($table, $where=NULL,$debug=false);
    //Обновляем запись(-и)
    abstract function setRecords($table,$what,$where,$debug=false);
    //Нестандартный запрос
    abstract function query($sql);

Теперь подробнее о каждом абстрактном методе. Реализация этих методов будет представлена ниже, когда я буду описывать класс модели News.
Некоторые входные параметры повторяются, поэтому я не будут повторно их описывать за исключение тех случаев, когда это действительно потребуется.

Метод getRecords

Этот метод позволяет получить набор записей, которые подходят под условие нашего SQL.

  • $what — передает массив полей, которые нужно выбрать из таблицы
  • $where — передает ассоциативный массив ключей в виде array('Поле'=>array('знак','значение')). Это дает нам возможность более гибко использовать предикат WHERE
  • $limit — передает ассоциативный массив ключей в виде array('начальная запись', 'количество записей'). Такая структура дает нам возможность реализовать постраничный вывод или вывод ограниченного количества записей
  • $order — ассоциативный массив array('поле'=>'вид сортировки'). Дает возможность сортировки по любому количеству столбцов.
  • $join — ассоциативный массив array('Тип связи', array('Таблица1', 'Таблица2'), array('Алиас1', 'Алиас2'), array('поле1','поле2')). Тип связи: LEFT, INNER, RIGHT, OUTER; Таблица1 и Таблица2: таблицы между которыми устанавливается связь; Аллиас1 и Аллиас2 — псевдонимы для таблицы; Поле1 и Поле2 — это соотв. PK и FK таблиц
  • $degub — этот параметр нужен для сохранения в свойства класса уже созданного sql запроса, а также параметров, которые нужно если мы используем prepare statement в PDO

Метод addRecord

Этот метод позволяет добавить запись в таблицу.
  • $data — ассоциативный массив параметров в виде: 'поле'=>'значение', которые будут вставляться в таблицу

Метод deleteRecords

Этот метод позволяет удалить запись (-и) из таблицы.
  • $table — название таблицы из которой будут удаляться данные

Метод setRecords

Этот метод позволяет обновить запись (-и) в таблице по заданному условию.
  • $what — В этом случает этот параметр передает массив в виде: 'поле'=>'значение', которые будут использоваться с оператором SET

Метод query

Этот метод позволяет выполнять нестандартные запросы. Для этого нужно просто передать нужный sql запрос в качестве параметра метода.
  • $sql — sql запрос для выполнения

Думаю многие знают, что DML тип запросов состоит из 4 видов: SELECT, INSERT, UPDATE, DELETE. Для генерации эти запросов нам нужно их разделить условно на несколько частей.
1) SELECT запрос делится на: WHAT, JOIN, WHERE, ORDER, LIMIT, GROUP, HAVING.
2) INSERT запрос: WHAT
3) UPDATE запрос: WHAT, WHERE
4) DELETE запрос: WHERE.
Получается нам всего лишь нужно генерировать отдельно эти части запроса, а потом склеить их воедино. Для генерации этих частей в классе DataBase были созданы методы, которые являются едиными не только для модуля «Новости», как в нашем случае, но и для любого другого модуля.

Рассмотрим эти методы подробнее.

Метод checkWhat

Единственным входным параметром этого метода является ассоциативный массив значений, которые либо нужно выбрать с помощью SELECT или нужно обновить с помощью UPDATE.
Параметр $what описан тут
protected function checkWhat($what)
    {
        if (is_array($what))
        {
            foreach ($what as $k=>$v)
            {
                if (!is_numeric($k)) // Проверяем является ли наш массив ассоциативным. Если да, то готовим prepare statement, а если нет, то перечисляем все столбцы через запятую для SELECT запроса
                {
                    $result['column'][]=$k."=?";
                    $result['params'][]=$v;
                }
                else {
                    $result['column'][]=$v;
                }
            }
            $result['column']=implode(",",$result['column']);
        }
        return $result;
    }

Думаю тут все довольно ясно. Проверка на массив, обход всех элементов массива и построение части строки запроса.

Метод checkJoin

Параметр $join описан тут. Также предусмотрена ситуация, когда нужно объединить больше двух таблиц, тогда параметр $join может быть представлен в виде array($join1, $join2,.....,$joinN)
   //Проверяем наличие Join составляющей запроса.
    protected function checkJoin($join)
    {
        if (is_array($join) && count($join)>0)
        {
            if (!is_array($join[0])) // Если это массив, то генерируем только один join
            {
                $res[]=$this->addJoin($join[0],$join[1],$join[2],$join[3]);
            }
            else { // Если это массив массивов, то генерируем несколько join
                $res=$this->addJoinArray($join);
            }
            return implode(" ",$res);
        }
        else {
            return false;
        }
    }

    //Генерация join
    protected  function addJoin($type=' INNER ',$tables,$pseudoName,$rows)
    {
        if ($type!=='' && is_array($tables) && is_array($rows))
        {
            $t0=$tables[0];
            $t1=$tables[1];
            if (is_array($pseudoName) && count($pseudoName)>0) // если есть аллиасы, то применяем их
            {
                $t0=$pseudoName[0];
                $t1=$pseudoName[1];
            }
            return $type." JOIN `".$tables[1]."` `".$pseudoName[1]."` ON `".$t0."`.`".$rows[0]."`=`".$t1."`.`".$rows[1]."`";
        }
        else {
            return false;
        }
    }

// Генерация массива join`ов
    protected function addJoinArray($join)  
    {
        if (is_array($join))
        {
            foreach ($join as $j)
            {
                $res[]=$this->addJoin($j[0],$j[1],$j[2],$j[3]);
            }
        }
        return $res;
    }


Метод checkWhere

Метод проверяет наличие параметров для WHERE составляющей запроса. Параметр $where описан тут
protected function checkWhere($where)
    {
        if (!is_null($where) && is_array($where))
        {
            foreach ($where as $k=>$v)
            {
                $part=$k.$v[0];  // сначала соединяем часть запроса 'поле' и 'знак'
                if (!is_array($v[1])) //если не массив, то готовим prepare statement
                {
                    $part.="?";
                    $params[]=$v[1]; // добавляем параметры в массив
                }
                else { // если массив, то вероятно мы используем IN (array)
                    $part.="(".implode(",",$v[1]).")";
                }
                $res[]=$part;

            }
            $result['column']="WHERE ".implode(" AND ",$res);
            $result['params']=$params;
        }

        return $result;
    }


Метод checkLimit

Генерация предикаты LIMIT запроса. Тут все довольно просто.
protected function checkLimit($limit)
    {
        $res=false;
        if (is_array($limit) && count($limit)>0)
        {
            $res=" LIMIT ".$limit['start'];
            if (isset($limit['count']) && $limit['count']!=='') // проверяем наличие второго параметра для постраничного вывода
            {
                $res.=", ".$limit['count'];
            }
        }
        return $res;
    }


Метод checkOrder

Генерация предикаты ORDER.
protected function checkOrder($order)
    {
        if (is_array($order) && count($order)>0)
        {
            foreach ($order as $row=>$dir)
            {
                $res[]=$row." ".$dir;
            }
            return "ORDER BY ".implode(",",$res);
        }
        else {
            return false;
        }
    }

Вот и все методы, которые нужны для генерации основных частей запроса. Но т.к. мы используем prepare statement в таких частях запроса как WHERE и WHAT, то нам нужно объединить параметры эти частей, чтобы передать в PDO. Для этой задачи я написал еще один метод

Метод checkParams

Входящими параметрами являются два массива. Массив параметров WHAT и WHERE.
protected function checkParams($what,$where)
    {
        if (!isset($what) || !is_array($what))
        {
            $params=$where;
        }
        else if (!isset($where) && !is_array($where))
        {
            $params=$what;
        }
        else {
            $params=array_merge($what,$where);
        }
        return $params;
    }


Следующим этапом построения SQL запросов является итоговая генерация sql кода. Для этого я создал 4 метода: prepareSelectSQL, prepareInsertSQL, prepareDeleteSQL, prepareUpdateSQL
Рассмотрим эти методы подробнее.

Метод prepareSelectSQL

Параметры этого метода совпадают с параметрами метода getRecords. Это $what, $where, $limit, $order, $join, $debug. Эти параметры описаны тут
protected function prepareSelectSQL($what=array('*'),$where=NULL, $limit=NULL, $order=NULL,$join=NULL,$debug=false)
    {
        $what=$this->checkWhat($what);
        $where=$this->checkWhere($where);
        $limit=$this->checkLimit($limit);
        $order=$this->checkOrder($order);
        $j=$this->checkJoin($join);

        $sql="SELECT ".$what['column']." FROM `".$this->table."` `tb` ".$j." ".$where['column']." ".$order." ".$limit;
        $params=$this->checkParams($what['params'],$where['params']);
        if ($debug) // если true, то передаем sql и его параметры в  свойства класса.
        {
            $this->sql=$sql;
            $this->params=$params;
        }
        return array('sql'=>$sql,'params'=>$params);  // возвращаем готовый sql  и его параметры.

    }


Метод prepareInsertSQL

Этот метод проще, т.к. используется ограниченный набор предикат и параметров
protected function prepareInsertSQL($data,$table,$debug=false)
    {
        $params=$values=$column=array();
        foreach ($data as $c=>$p)
        {
            $column[]=$c; // поля для вставки
            $values[]="?"; // параметры для prepare statement 
            $params[]=$p; //массив реальных значений параметров

        }

        $sql=" INSERT INTO `".$table."` (".implode(",",$column).") VALUES (".implode(',',$values).")";
        if ($debug)
        {
            $this->sql=$sql;
            $this->params=$params;
        }
        return array('sql'=>$sql,'params'=>$params);
    }


Метод prepareDeleteSQL

Запрос для удаления записей. Используем название таблицы и набор параметров для предикаты WHERE.
 protected function prepareDeleteSQL($table,$where,$debug=false)
    {
        $where=$this->checkWhere($where);
        $sql="DELETE FROM `".$table."` ".$where['column'];
        $params=$this->checkParams($what,$where['params']);
        if ($debug)
        {
            $this->sql=$sql;
            $this->params=$params;
        }
        return array('sql'=>$sql,'params'=>$params);
    }


Метод prepareUpdateSQL

Генерируем sql запрос для обновления записей в таблице.
 protected function prepareUpdateSQL($table,$what,$where,$debug=false)
    {
        $what=$this->checkWhat($what);
        $where=$this->checkWhere($where);
        $sql="UPDATE `".$table."` SET ".$what['column']." ".$where['column'];
        $params=$this->checkParams($what['params'],$where['params']);
        if ($debug)
        {
            $this->sql=$sql;
            $this->params=$params;
        }
        return array('sql'=>$sql,'params'=>$params);

    }


Выше был описан класс DataBase, который отвечает за подключение к базе данных и генерацию DML sql запросов. Ниже приведен полный код этого класса.
Abstract class DataBase
<?
abstract class DataBase {
    static private  $_db=NULL;
    public  $sql='';
    public $params=array();

    /*
     * Блокируем __construct и __clone для того,
     * чтобы невозможно было создать новый объект через new.
     * */
    private function __construct()
    {
        return false;
    }

    private function __clone()
    {
        return false;
    }

    //Получаем записи
    abstract public function getRecords($what='*',$where=NULL, $limit=NULL, $order=NULL,$join=NULL,$debug=false);
    //Добавляем запись
    abstract function addRecord($data=array(),$debug=false);
    //Удаляем запись(-и)
    abstract function deleteRecords($table, $where=NULL,$debug=false);
    //Обновляем запись(-и)
    abstract function setRecords($table,$what,$where,$debug=false);
    //Нестандартный запрос
    abstract function query($sql);

    /*
     * Синглтон подключения к базе данных. Если объект уже создан,
     * то просто возвращается экземпляр объекта, если нет,
     * то создается новое подключение к базн данных.
     * Можно напрямую пользоваться классом PDOChild
     * */

    public static function getInstance($registry)
    {
        if (is_null(self::$_db))
        {
            self::$_db=new PDOchild($registry);
        }
        return self::$_db;
    }

    /*
     * Добавляем join к запросу.
     * type - тип нужного join
     * tables - массив таблиц которые будут связываться
     * pseudoName - псевдонимы для таблиц
     * row - поля по которым производится связь
     * */

    protected  function addJoin($type=' INNER ',$tables,$pseudoName,$rows)
    {
        if ($type!=='' && is_array($tables) && is_array($rows))
        {
            $t0=$tables[0];
            $t1=$tables[1];
            if (is_array($pseudoName) && count($pseudoName)>0)
            {
                $t0=$pseudoName[0];
                $t1=$pseudoName[1];
            }
            return $type." JOIN `".$tables[1]."` `".$pseudoName[1]."` ON `".$t0."`.`".$rows[0]."`=`".$t1."`.`".$rows[1]."`";
        }
        else {
            return false;
        }
    }

    /*
     * Добавляем несколько join к запросу
     * join - массив массивов join array(join,join)
     * */

    protected function addJoinArray($join)
    {
        if (is_array($join))
        {
            foreach ($join as $j)
            {
                $res[]=$this->addJoin($j[0],$j[1],$j[2],$j[3]);
            }
        }
        return $res;

    }

    /*
     * Генерируем SELECT sql
     * what- поля которые нужно выбрать в виде массива
     * where- условие выбора в виде массива array(поле=>array(знак=,значение))
     * limit-лимит записей в виде массива array(начальная запись, количество)
     * order- сортировка array (поле=>направление)
     * join- массив join
     * debug- если true то в свойство класса sql записывается текущий sql запрос и в свойство params записываются параметры
     * */

    protected function prepareSelectSQL($what=array('*'),$where=NULL, $limit=NULL, $order=NULL,$join=NULL,$debug=false)
    {
        $what=$this->checkWhat($what);
        $where=$this->checkWhere($where);
        $limit=$this->checkLimit($limit);
        $order=$this->checkOrder($order);
        $j=$this->checkJoin($join);

        $sql="SELECT ".$what['column']." FROM `".$this->table."` `tb` ".$j." ".$where['column']." ".$order." ".$limit;
        $params=$this->checkParams($what['params'],$where['params']);
        if ($debug)
        {
            $this->sql=$sql;
            $this->params=$params;
        }
        return array('sql'=>$sql,'params'=>$params);
    }

    /*
     * Генерируем Insert sql
     * data- массив пар поле-значение для вставки
     * table- таблица куда вставляется значение
     * debug- если true то в свойство класса sql записывается текущий sql запрос и в свойство params записываются параметры
     * */

    protected function prepareInsertSQL($data,$table,$debug=false)
    {
        foreach ($data as $c=>$p)
        {
            $column[]=$c;
            $values[]="?";
            $params[]=$p;
        }

        $sql=" INSERT INTO `".$table."` (".implode(",",$column).") VALUES (".implode(',',$values).")";
        if ($debug)
        {
            $this->sql=$sql;
            $this->params=$params;
        }
        return array('sql'=>$sql,'params'=>$params);
    }

    /*
     * Генерируем Delete sql
     * where- Условие для удаления
     * table- таблица из которой удаляются записи
     * debug- если true то в свойство класса sql записывается текущий sql запрос и в свойство params записываются параметры
     * */

    protected function prepareDeleteSQL($table,$where,$debug=false)
    {
        $where=$this->checkWhere($where);
        $sql="DELETE FROM `".$table."` ".$where['column'];
        $params=$this->checkParams($what,$where['params']);
        if ($debug)
        {
            $this->sql=$sql;
            $this->params=$params;
        }
        return array('sql'=>$sql,'params'=>$params);
    }

    /*
     * Генерируем Update sql
     * table- таблица из которой удаляются записи
     * what - массив поле значение для обновления
     * where- Условие для обновления
     * debug- если true то в свойство класса sql записывается текущий sql запрос и в свойство params записываются параметры
     */

    protected function prepareUpdateSQL($table,$what,$where,$debug=false)
    {
        $what=$this->checkWhat($what);
        $where=$this->checkWhere($where);
        $sql="UPDATE `".$table."` SET ".$what['column']." ".$where['column'];
        $params=$this->checkParams($what['params'],$where['params']);
        if ($debug)
        {
            $this->sql=$sql;
            $this->params=$params;
        }
        return array('sql'=>$sql,'params'=>$params);

    }

    /*
     * Проверяем наличие параметра join
     * Если он есть, то проверяем является ли он единственным, если да то addJoin
     * если нет, то addJoinArray
     * Если join нет, то ничего не возвращаем
     * */

    protected function checkJoin($join)
    {
        if (is_array($join) && count($join)>0)
        {
            if (!is_array($join[0]))
            {
                $res[]=$this->addJoin($join[0],$join[1],$join[2],$join[3]);
            }
            else {
                $res=$this->addJoinArray($join);
            }
            return implode(" ",$res);
        }
        else {
            return false;
        }
    }

    /*
     * Проверяем наличие параметра what
     * Если этот параметр явл. массивом,
     * то генерируем массив поле=>? и массив параметров для prepare SQL
     * */

    protected function checkWhat($what)
    {
        if (is_array($what))
        {
            foreach ($what as $k=>$v)
            {
                if (!is_numeric($k))
                {
                    $result['column'][]=$k."=?";
                    $result['params'][]=$v;
                }
                else {
                    $result['column'][]=$v;
                }
            }
            $result['column']=implode(",",$result['column']);
        }
        return $result;
    }

    /*
     * Проверяем наличие параметра Where
     * Если этот параметр явл массивом,
     * то генерируем массив поле=>? и массив параметров для prepare SQL
     * если v[0](sign)= IN и значение value это массив, то можно сгенерировать IN (array);
     * Можно также генерировать условие LIKE, но не тестил.
     * Возвращает массив полей и параметров для sql
     * */

    protected function checkWhere($where)
    {
        if (!is_null($where) && is_array($where))
        {
            foreach ($where as $k=>$v)
            {
                $part=$k.$v[0];
                if (!is_array($v[1]))
                {
                    $part.="?";
                    $params[]=$v[1];
                }
                else {
                    $part.="(".implode(",",$v[1]).")";
                }
                $res[]=$part;

            }
            $result['column']="WHERE ".implode(" AND ",$res);
            $result['params']=$params;
        }

        return $result;
    }

    /*
     * Проверяем наличие параметра Limit
     * Если этот параметр явл массивом,
     * то генерируем LIMIT для SQL
     * Возвращает строку LIMIT  с разбиением на страницы или без него
     * */

    protected function checkLimit($limit)
    {
        if (is_array($limit) && count($limit)>0)
        {
            $res=" LIMIT ".$limit['start'];
            if (isset($limit['count']) && $limit['count']!=='')
            {
                $res.=", ".$limit['count'];
            }
        }
        return $res;
    }

    /*
     * Проверяем наличие параметра Order
     * Если этот параметр явл массивом,
     * то генерируем ORDER для SQL
     * Возвращает массив ORDER
     * */

    protected function checkOrder($order)
    {
        if (is_array($order) && count($order)>0)
        {
            foreach ($order as $row=>$dir)
            {
                $res[]=$row." ".$dir;
            }
            return "ORDER BY ".implode(",",$res);
        }
        else {
            return '';
        }
    }

    /*
     * Проверяем наличие параметров для prepare sql
     * Параметры состоят из массива параметров WHAT и массива параметров WHERE.
     * Это нужно для того, чтобы prepare sql
     * работал и с update, select, delete, insert
     * Объединяет два массива what и where
     * */

    protected function checkParams($what,$where)
    {
        if (!isset($what) || !is_array($what))
        {
            $params=$where;
        }
        else if (!isset($where) && !is_array($where))
        {
            $params=$what;
        }
        else {
            $params=array_merge($what,$where);
        }
        return $params;
    }
}

?>


Теперь настало время описать класс модели News. Этот класс реализует все абстрактные методы класса-родителя DataBase и статический метод getObject. Этот метод возвращает экземпляр объекта этого класс. Этот метод был создан для того, чтобы отпала необходимость в создании объекта класса News путем использования ключевого слова new. Вот как это выглядит:
$news=News::getObject()->getRecords(params);

Каждый метод этого класса вызывает нужный ему генератор sql запроса и передает итоговый запрос и параметры в PDO для выполнения запроса. Ниже приведен полный код класса модели News.
class News
<?
class News extends DataBase
{
    public $table='news'; //Главная талица
    public $id_row='id'; // primary key
    
    public function __construct()
    { 
        $registry=new Registry(); //параметры подключения к базе данных
        $this->db=parent::getInstance($registry);
    }

    // для доступа к классу через статический метод
    public static function getObject()
    {
        return new News();
    }

   // получаем записи из таблицы.
    public function getRecords($what=array('*'),$where=NULL, $limit=NULL, $order=NULL,$join=NULL,$debug=false)
    {
        $data=$this->prepareSelectSQL($what,$where, $limit, $order,$join,$debug);
        $res=$this->db->prepare($data['sql']);
	$res->execute($data['params']);
	$result=$query->fetchAll(PDO::FETCH_OBJ);
        return $result;

    }

    public function addRecord($data=array(),$debug=false)
    {
        $data=$this->prepareInsertSQL($data,$this->table,$debug);
        $query=$this->db->prepare($data['sql']);
	return $query->execute($data['params']));       
    }

    public function deleteRecords($table, $where=NULL,$debug=false)
    {
        $data=$this->prepareDeleteSQL($table,$where,$debug);
        $query=$this->db->prepare($data['sql']);
	$result=$query->execute($data['params']);
	return $result;
    }

    public function setRecords($table,$what,$where,$debug=false)
    {
        $data=$this->prepareUpdateSQL($table,$what,$where,$debug);
        $query=$this->db->prepare($data['sql']);
	$result=$query->execute($data['params']);
	return  $result;
    }

    public function query($sql)
    {
        $query=$this->db->prepare($sql);
	$query->execute();
	$result=$query->fetchAll(PDO::FETCH_OBJ);
        return $result;
    }

}

?>


В принципе на этом можно завершать. Конечно же, можно было еще добавить генерацию предикаты GROUP BY и HAVING, но я решил этого не делать. Думаю, что принцип построения запросов я изложил ясно и проблем с использованием не возникнет. В итоге мы получили механизм построения sql запросов, который не завязан на конкретной структуре таблицы в БД и может применяться к разным типам DML SQL запросов. Если нужно будет, могу создать репозиторий на github.
Буду рад услышать критику и предложения по улучшению метода.
AdBlock похитил этот баннер, но баннеры не зубы — отрастут

Подробнее
Реклама

Комментарии 47

      –3
      не совсем ясно к чему эти посты?
        0
        Мне кажется, метод getRecords можно прекрасно заменить LINQ-подобным запросом при помощи к.-л. библиотеки из указанных мною постов. Может я ошибаюсь, но lдля решения вашей задачи следовало бы взять к.-л. ORM. Или я неверно понял суть реализации…
          0
          Да, я с Вами согласен на счет ORM, но это было бы слишком глобально, т.к. эта задача применялась на уже написанной CMS. Думаю использование ORM очень сильно повлияло бы на CMS, а вот с помощью этого способа я просто немного упорядочил работу с базой данных.
      +1
      if (!is_null($where) && is_array($where))

      а как is_array может «пропустить» null? Ну т.е. для чего проверка на нулл?

      возможно вы хотели проверить isset(), его ставят чтоб не выпадали notice ошибки в скриптах, но в вашем случае isset тоже не нужен.
        0
        проверку на NULL я добавил для того, чтобы можно было входящие параметры метода заменить на NULL, если я их не буду использовать.
          +2
          is_array достаточно.
        0
        en.wikipedia.org/wiki/Doctrine_%28PHP%29
        Если для самообразования то ок, иначе лучше брать готовый продукт с хорошей документацией. Doctrine один из вариантов.
          +1
          1. имхо лучше называть методы аналогично соответствующим SQL функциям — проще, нагляднее.
          2. очень полезно было бы вставить mysql_real_escape_string или свой ее аналог для отсечения всяких нехорошестей. И, при беглом просмотре кода, не нашел (плохо искал?) где добавляются кавычки вокруг значений. Что если значение поля будет (без окружающих кавычек) «мама мыла 'раму', но это ей не нравилось»?
          3. было бы не лишним добавить "`" для названий полей
          4. может быть полезным сделать обертки для getRecords, которые будут возвращать строку как массив, вложенный массив для списка объектов, одно значение, пару значений и т.д.?
          SELECT `key`=>`value` FROM `table` => array(key=>value)
          SELECT `value` FROM `table` LIMIT 1 => value
          SELECT `value` FROM `table` => array(value, value, value)
          SELECT * FROM `table` LIMIT 1 => array(...)
          SELECT * FROM `table`=> array(array(...), array(...), ...)
          Это заметно упрощает работу с библиотекой

          PS Лично я (специфика работы) предпочитаю insert/update/delete делать через аналогичную библиотеку, а вот select`ы писать руками — реализация всеобъемлющей функции будет слишком тяжелой: вложенные запросы, сложные условия выборки, вызов хранимых процедур и далее по списку.
            –1
            1) Спасибо за совет, в будущем учту.
            2) Я ее не использовал, т.к. для работы с БД использую PDO. Насколько я знаю, то PDO не нуждается в исп. mysql_real_escape_string.(может я и ошибаюсь)
            3) "`" не добавлял, т.к. подразумевается, что поля будут указываться вместе с аллиасами, т.к. специфика базы с которой сейчас работаю требует Join использовать практически для любого запроса.
              0
              2. Если используете PDO::prepare, то да, дополнительно эскейпить не нужно. Но я бы сравнил по быстродействию.
              3. Чудо-пользователи могут создать табличку с полем where и привет. Никто же не мешает же делать что-то вроде t1.`field1`, где t1 — алиас для таблицы `table1`.

              PS Лично мне идея написать обертку для PDO не кажется очень хорошей.
            0
            А мне всегда было удобнее писать запросы руками. По-моему, так нагляднее
              0
              Да, но если у тебя запрос в 2-4 строки, то наглядность убывает и код становится нечитабельным.
                +1
                2-4 строки это детский лепет :)
                Вот когда отформатированный запрос занимает 36 строчек, это буйство :) И тут ORM скорее в тягость.
                  0
                  В таком случае, при использовании ORM бывает полезно перенести запрос на сторону БД (в хранимую процедуру, или представление), а из ORM уже просто делать вызов соответствующей функции, в которую ORM обернет хранимку.
              +1
              Это если линейно писать. А если форматировать, то минимум 4 строчки и гораздо лучше воспринимается сознанием)
                0
                SELECT
                tb.*,
                tb2.name,
                tb2.body_m,
                tb3.catalog_id,
                tb4.status_id

                FROM product tb

                LEFT JOIN ru_product tb2 ON tb2.product_id=tb.id
                LEFT JOIN product_catalog tb3 ON tb3.product_id=tb.id
                LEFT JOIN product_status_set tb4 ON tb4.product_id=tb.id
                WHERE tb.active='1' AND tb3.catalog_id=? AND tb3.product_id!='10'
                GROUP BY tb.id
                ORDER BY rand()
                LIMIT 6

                Думаю такие запросы даже отформатированные будут смотреться не очень хорошо среди остального кода. Гораздо лучше заменить это все несколькими строчками кода, который сгенерирует этот sql запрос автоматически. Конечно же, это мое IMHO.
                  0
                  Это вы еще не видели как XML с дикой структурой на T-SQL выбирается. У меня там и по двести-триста строк выражения попадались. Зато на выхлопе конфетка.
                    0
                    SELECT
                        tb.*, tb2.name, tb2.body_m, tb3.catalog_id, tb4.status_id 
                    FROM
                                             product tb
                        LEFT JOIN ru_product tb2 ON tb2.product_id=tb.id
                        LEFT JOIN product_catalog tb3 ON tb3.product_id=tb.id
                        LEFT JOIN product_status_set tb4 ON tb4.product_id=tb.id
                    WHERE
                        tb.active='1' AND tb3.catalog_id=? AND tb3.product_id!='10'
                    GROUP BY tb.id
                    ORDER BY rand()
                    LIMIT 6
                    


                    нормально смотрится, если бардак в коде не разводить. К тому же это на много нагляднее.

                    А с несколькими строчками сложнее — либо оно маленькое, шустрое, но не сильно функциональное, либо вырастает в монструозное чудовище. Вы написали гораздо больше чем несколько строчек кода, а ведь это всего лишь обертка для PDO и она ооочень многого не умеет (OUTER JOIN, подзапросы, вызов хранимых процедур и т.д.).
                    +6
                    Ох какой знакомый велосипед. 3 года назад я был на этой же стадии. :)
                      0
                      Мне кажется проще взять готовый query builder, которых на том же phpclasses целый вагон.
                        +1
                        Если взять готовый, то как научиться писать свой? ))
                        +1
                        //Получаем запись
                        abstract public function getRecords($what='*',$where=NULL, $limit=NULL, $order=NULL,$join=NULL,$debug=false);

                        самое интересное начинается, когда сортируем чаще, чем ограничиваем и решаем поменять параметры $limit и $order местами. или когда решаем добавить ещё один параметр, который ставится после $debug. или когда решаем переделать список параметров на параметр в виде массив. или когда…

                        наверное все через это проходили, прежде чем выбрать какую-нибудь библиотеку для работы с базой данных.
                          0
                          Вот кстати, идея поменять все параметры на ассоциативный массив появилась буквально вчера. Думаю такой вариант будет лучше чем то, что есть сейчас.
                            0
                            нет, решение будет плохое (не в обиду будет сказано).

                            плохо в массиве будет то, что у вас не будет автодополнения, надо делать дополнительные проверки на наличие/отсутствие данных в массиве, вы будете забывать обновлять комментарии к функции при добавлении/удалении необходимых параметров и в конце концов разные классы будут передавать разные параметры во вроде бы одну функцию. поддержка кода будет очень сложной.

                            не делайте так.
                              0
                              Хм. А казалось, что лучше, т.к. обход параметров будет происходить в массиве, и проблема с тем, чтобы не писать вместо ненужных параметров NULL отпадет.
                                0
                                В любом случае, спасибо за совет.
                                  0
                                  да пожалуйста. ещё раз повторюсь, что написать своё решение для работы с базой данных — это дело чести для каждого разработчика на пхп. это наверное обязательный этап, чтобы увидеть какие вообще бывают решения, их плюсы/минусы, познакомится с архитектурой и с самим кодом.
                                0
                                Выше вам правильно подсказали и объяснили почему так делать нельзя. Но не подсказали решение получше:

                                $db->
                                   what($smt1)->
                                   where($smt2)->
                                   limit(23)->
                                   order($smt3)->
                                   join($smt4)->
                                   getRecords($debug) 
                                ;
                                
                                  0
                                  Формально это решение правильное, но я бы поспорил с удобством его применения на практике. Стремясь уйти от громоздких SQL-запросов мы приходим к громоздкому PHP-коду.
                                  Что проще написать и проще поддерживать?:
                                    $sql = 'SELECT `smt1` FROM `smt2` ORDER BY `smt3` LIMIT 32';
                                    $DB->getRecords($sql);
                                  

                                  или
                                    $db->
                                       what($smt1)->
                                       where($smt2)->
                                       limit(23)->
                                       order($smt3)->
                                       getRecords($debug);
                                  

                                  Добавьте к этому заведомую ограниченность второго варианта.

                                  Мое субъективное имхо: в таких библиотечках самое сложное это понять ту грань, за которую перешагивать не нужно, чтобы не получить неповоротливого монстра.
                                    0
                                    Абсолютно верно! На вкус и цвет помидоры для каждого свои.
                                    Но во втором варианте мне нравится:
                                    1. Автодополнение
                                    2. Семантика
                                    3. Абстракция, особенно, если в where() передаётся не строка, а некоторый структурированный кусок данных.
                                    4. Обычно, это короче:

                                    $dbNews->select();    // select всё же правильнее, чем getRecords
                                    // VS
                                    $query = 'SELECT * FROM `'. TBL_NEWS .'`';
                                    $mysql->exec($query);
                                    
                                    
                                    // примерчик посложнее
                                    $filter = array('login'=>$_POST['login'], 'password'=>$_POST['password']);
                                    $dbUsers->where($filter)->selectOneRow();
                                    
                                    // VS
                                    $query = 'SELECT * FROM `'. TBL_USERS .'` WHERE
                                       `login`="'. $mysql->escape((string)$_POST['login']) .'"
                                        AND `password`="'. $mysql->escape((string)$_POST['password']) .'"';
                                    $mysql->exec($query);
                                    


                                    5. Второй вариант локаничнее и транзитивнее, например:
                                    $filter = array('active'=>1);
                                    $cnt = $dbNews->where($filter)->getCount();
                                    $pager = new Pager($cnt, 20);
                                    $items = $dbNews->
                                        where($filter)->
                                        limit($pager->getLimit())->
                                        select()
                                    ;
                                    // $filter был создан один раз и использован дважды
                                    


                                    Ещё пример:
                                    class productsController {
                                       protected $_defaultFilter = array(
                                            'active'=>1,
                                            'cnt_images > ' => 0 // картинок больше чем 0
                                        );
                                       
                                    
                                       public function indexAction() {
                                            $filter                 = $this->_defaultFilter;
                                            $filter['category_id']  = $_POST['catId'];
                                             // ...
                                       }
                                    }
                                    


                                    Ограниченность — это недоработка. Систему необходимо делать гибкой. Если у кого-то не получается — это его проблемы. Единственное но: некоторые сложные запросы проще написать строкой, чем использовать билдер. Но в моей практике таких запросов не больше 2%. Зато я экономлю своё время в 98% остальных случаев.
                                      0
                                      Согласен, но Ваши примеры хороши для несложных запросов. Возьмем последний Ваш пример: представьте что Вам нужно выбирать товары, но так чтобы они были только в активных категориях, причем первыми должны идти товары из промо-списка, который хранится в отдельной таблице. Ваши действия?

                                      PS Естественно, свое мнение я не навязываю и абсолютной истиной его не считаю — у каждого свой стиль работы.
                                        0
                                        Ну лично в моей абстракции, это выглядит так:

                                        $fields = array(
                                           '*',
                                           '(SELECT COUNT(*) FROM `promo` WHERE ... ) AS `is_promo`'
                                        );
                                        $order = array('is_promo'=>DESC);
                                        
                                        
                                        $dbProducts->
                                            fields($fields)->    // метод fields()  - это то что у автора метод what()
                                            joinFromRels('categories', ' AND `caterory`.`active`=1')->
                                            order($order)->
                                            select()
                                        ;
                                        


                                        Поясню конструкцию с джоином:
                                        В системе есть глобальный список описывающий все связи (описывает все foreign_keys). Метод joinFromRels($relName) позволяет сгенерировать join для описанной связи. Второй аргумент не обязателен, но он позволяет дополнителнить условие ON [condition] что бы получилось:
                                        ON
                                          `categories`.`id` = `products`.`cat_id`
                                          AND `categories`.`active`=1
                                        


                                        Естественно, в системе есть более низкоуровневый метод $dbProducts->join(...) — позволяет сгенерировать любой джоин, хотя это сложнее. А вот подцеплять джоины из заранее описанного массива гараздо удобнее :) Можно например так:

                                        $dbContinents->joinFromRels(array(
                                           'countries' => array(
                                                 'towns' => array(
                                                     'streets',
                                                     'peoples'
                                                ),
                                            ),
                                        ));
                                        // Для континентов подцепит страны, для стран подцепит города, а для городов подцепит улицы и людей.
                                        

                                        Разве это не клёво?
                                          0
                                          Это здорово. Но представьте себе тихий ужас нового человека, которому нужно сходу влиться в Ваш проект. Или исправить что-то спустя три года, особенно если Вы уже будете работать в другом месте.

                                          По Вашему первому примеру: согласитесь, не очень правильно мешать в одном месте билдер и SQL.

                                          PS Кстати, а список внешних ключей Вы руками обновляете или автоматически?
                                            0
                                            Представьте тихий ужас секретарши которую попросят написать SQL-запрос… Да, появляется дополнительный порог вхождения так же как и в любой другой ORM. Зато, если вы не меняете персонал каждые 3 месяца, то работа делается быстрее, ведь изучить надо только 1 раз, а потом экономить время на каждом SQL-запросе. Кстати, изучать не так уж сложно. Я вам уже половину системы показал (самую сложную её часть) и надеюсь, всё понятно рассказал.

                                            согласитесь, не очень правильно мешать в одном месте билдер и SQL.

                                            Судя по заданному вопросу, вы уже вникли в суть и ваша догадка абсолютно верна. Система транзитивна, по этому до многих вещей можно додуматься самостоятельно не читая документацию. Например, то что второй параметр может быть массивом $filter :)

                                            AdminCreator всё делает сам :) Я просто говорю: создай мне новую таблицу -> с «такими то» полями… «таких то» типов ..., одно из полей category_id с типом «связь один_ко_многим» к таблице categories -> СГЕНЕРИРОВАТЬ -> БАЦ! получаю готовый php и SQL-код для создания таблицы с foreign keys.

                                            Сразу после этого я могу писать:

                                            dbFactory(MODEL_PRODUCTS)->
                                              joinFromRels(MODEL_CATEGORIES)->
                                              select()
                                            ;
                                            // Обратите внимание, каждая конструкция автодополняется!
                                            // по этому, печатать приходится только это:
                                            dbF->(MP)->
                                              JFR(MC)->
                                             sel()
                                            ;
                                            
                                              0
                                              Ну что ж, мне остается только по-доброму позавидовать ))

                                              Кстати, обращу внимание на важность документирования и изучения документации новыми сотрудниками — как правило идет процесс «деградации» понимания системы. Разработчики, используя в своей работе только базовые функции ORM, забывают / не знают о более хитрых инструментах и особенностях их использования.
                                                0
                                                Завидовать — плохо, даже по доброму. Лучше приходите к нам работать ))

                                                Пока с этим проблем нет. Наверное, потому что система относительно простая. Да, возможно некоторые забывают про существование низкоуровневых методов типа join(), потому что используют только joinFromRels(), но быстро освежают память при необходимости. PHPDoc-комментов в коде больше, чем самого кода — это облегчает процесс. Есть tdd-тесты в которых можно посмотреть интерфейс методов. Есть документация.
                                                  0
                                                  Спасибо за приглашение, конечно, но ездить далековато.
                                                0
                                                А как с быстродействием, между прочим? И нет ли паразитных запросов (тот же список внешних ключей ведь в базе хранится)?
                                                  0
                                                  В отличие от других ORM, наша ничего не знает о структуре БД и сама никогда не выполняет никаких запросов. Иногда это плохо, иногда хорошо. Но никакого оверхеда нет. Все SQL-запросы максимально примитивны. Единственный оверхеад,- это сгенерировать из массива $filter кусок SQL-запроса, но всё это делается на стороне php очень быстро.

                                                  Например, в TDD-тестах выполняется больше 1300 запросов к БД. Эти тесты проходят за ~5 сек (на моём компе под виндой). Так что оверхеад совершенно незаметен.

                                                  А связи описываются в bootstrap.php в виде php-кода:

                                                  My_Db_Rels_GlobalList::addHasMany( ... );
                                                  My_Db_Rels_GlobalList::addMany2Many( ... ...);
                                                  

                                                  Таким образом, foreign keys-ов может и не существовать. Более того, так получилось, что система поддерживает шардинг (хотя мы к этому и не стремились) — т.е. разные таблицы могут храниться на разных mysql-серверах, но при этом, между ними будет связь. Использовать join-ы уже не получится, но у нас есть ещё один более удобный механизм выцепления связанных данных который будет отлично работать
                                                    0
                                                    А данные в bootstrap.php вписываются ручками или программно?
                                                      0
                                                      Это палка о двух концах.
                                                      Раньше вписывались ручками. Но AdminCreator не мог дописывать в середину файла новые строки, по этому, пришлось сделать отдельную папку и написать скрипт который рекурсивно находит все файлы и подключает классы:
                                                      /libs/My/Module/***.class.php

                                                      БуутСтрап автоматом для всех делает:

                                                      new My_Module_News();
                                                      new My_Module_Users();
                                                      new My_Module_Catalog_Categories();
                                                      new My_Module_Catalog_Products();
                                                      ...
                                                      


                                                      Соответственно, каждый модуль может инициализировать свои собственные константы и связи.

                                                      Теперь AdminCreator для каждой таблицы создаёт отдельный модуль, но из-за этого получается небольшой оверхеад (совсем не ощутимый). Однако, на высоко нагруженном проекте все модули можно объединить в один файл, что бы не было оверхеда.
                                                        0
                                                        Может быть стоит подумать о том чтобы автоматически выдергивать связи из самой базы и писать в один файл, а после изменения структуры БД генерировать этот файл заново? Впрочем в чужой монастырь, как говорится.
                                        0
                                        как доказали на практике товарищи из лагеря .NET, это — самое лучшее решение. А еще в нем работают рефакторинги, ни в одном другом решении такого не будет.
                                  0
                                  Когда-то давно создал класс для работы с БД, до сих пор пользуюсь.
                                  суть: вставка\обновление — вызов метода с параметрами: Таблица, Массив данных. Помимо этого есть методы для выборки данных, тоже в виде надстроек над PDO.
                                  Пример:

                                  $folder_id = self::insert_array('##_contacts', array('note' => $post['folder_new'],
                                  'ownerid' => $player_id,
                                  'isfolder' => 1,
                                  'parentid' => 0));

                                  /**
                                  * Вставка записи в таблицу
                                  * @param string $prmTable идентификатор таблицы
                                  * @param array $prmData массив данных (ключ соответствует названию колонки)
                                  * return mixed ID добавленной записи
                                  */
                                  public function insert_array($prmTable, $prmData)
                                  {
                                  $fields = "";
                                  $fieldsVals = "";
                                  foreach (array_keys($prmData) as $key) {
                                  $fields .= (($fields == "")? '': ','). $key;
                                  $fieldsVals .= (($fieldsVals == "")? ':': ',:'). $key;
                                  }
                                  $query = «insert into ». self::parse_dbprefix($prmTable). " ({$fields}) values ({$fieldsVals})";
                                  self::execute($query, $prmData);
                                  return $this->db()->handle()->lastInsertId();
                                  }

                                    0
                                    Что вам мешало взять любой ORM?
                                      0
                                      Ответ на Ваш вопрос находится в самом начале комментариев.

                                    Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                                    Самое читаемое