Query Builder vs конкатенация запроса ручками

Lewik · 25 Июн 2014

Активист написал(а):
Чем плох SQL?

Некроссплатформеностью.
qb можно таскать по коду и модифицировать его. А строку придется парсить. Пагинацию на qb делать оч просто, на строке надо или руками писать или еще чего.
qb не отменяет необходимости понимать как он работает.

WMix · 25 Июн 2014

Некроссплатформеностью

qb на самом деле тоже, без хардкодных expressions не обойтись

Активист · 25 Июн 2014

Lewik написал(а):
Некроссплатформеностью.
qb можно таскать по коду и модифицировать его. А строку придется парсить. Пагинацию на qb делать оч просто, на строке надо или руками писать или еще чего.
qb не отменяет необходимости понимать как он работает.

Щитооо?Не кроссплатформено?) Т.е. под виндой мой SQL уже работать не будет? Или вы говорите о разных СУБД. Если о разных СУБД, то нужны модели на разные СУБД, поскольку везде есть тонкости, особенно в оптимизации, а это ключевой момент. Например MySQL и PostgreSQL - очень разные, хоть и похожи, и даже по сути по другому лучше строить структуру БД. Например, наличие SEQUENCE у последнего позволяет ввести уникальность для множества таблиц. Что-то типа GUID, но в int

По поводу пагинации, в чем проблема?

PHP:

<?php
class app_subscriber_models_base extends app_models_base
{
//...  
    public function getSubscribersByUserId($user_id)
    {
      
        if ($this->getPagination()->getOffset() !== null && $this->getPagination()->getLimit() > 0)
        {
            $db = (new app_db())->query_params("select sql_calc_found_rows * from `app_subscriber_base` where `user_id` = ? limit ?, ?", array("iii", $user_id, $this->getPagination()->getOffset(), $this->getPagination()->getLimit()));
          
            $this->getPagination()->setCount($db->found_rows());
        }
        else
        {
            $db = (new app_db())->query_params("select * from `app_subscriber_base` where `user_id` = ?", array("i", $user_id));
        }
      
        $return = array();
      
        while ($row = $db->fetch())
        {
            $return[] = (new app_subscriber_base())->setAttributes($row);
        }
      
        return $return;
    }

Lewik · 25 Июн 2014

Да, я имел ввиду разные субд.
Безусловно за всеядность приходится платить оптимизацией. Но в случае хайлоада, qb видимо не применяются или применяются не всеядные qb.
Очевидно, вы свою точку зрения не поменяете. Ну а я пока не намерен менять свою. Мнениями мы обменялись и срацца дальше причин нет =).

Активист · 25 Июн 2014

Lewik написал(а):
Да, я имел ввиду разные субд.
Безусловно за всеядность приходится платить оптимизацией. Но в случае хайлоада, qb видимо не применяются или применяются не всеядные qb.
Очевидно, вы свою точку зрения не поменяете. Ну а я пока не намерен менять свою. Мнениями мы обменялись и срацца дальше причин нет =).

А потом такие проекты превращаются в некую ЦМС-ку, в которой люди кешируют запрос в файлы, мемкеши, в HTML, что бы снизить нагрузку, ибо невозможно. Разные СУБД - разные модели.

WMix · 25 Июн 2014

Активист, проблем никаких нет!

PHP:

$pagination = new Pagination( $db::factory('app_subscriber_base')->get(array('user_id' => $user_id)), $page, $count );

Активист · 25 Июн 2014

WMix написал(а):
Активист, проблем никаких нет!

PHP:

$pagination = new Pagination( $db::factory('app_subscriber_base')->get(array('user_id' => $user_id)), $page, $count );

Ваш параметр не учитывает SQL_CALC_FOUND_ROWS для MySQL. наверное в вашем qb это учтено?

Сделайте такой мне SQL через билдер (поиск маршрута движения от остановки "с" до остановки "д" по маршруту от точки "а" до точки "б").

PHP:

public function searchTicketByStopsAndDate($dispatchStation, $arrivalStation, $date)
    {
        $db = new app_db();

        try
        {
            $datetime = new DateTime($date);
        }
        catch(Exception $e)
        {
            return array();
        }
    
        $db->query("
                select
                    *
                from
                    `root_bricks`            
                left join
                    `root_info` on `root_bricks`.`root_id` = `root_info`.`id`
                left join
                    `schedule`  on `root_bricks`.`root_id` = `schedule`.`root_id`
                right join
                    `carriers`  on `carriers`.`id` = `root_bricks`.`carrier_id`
                right join
                    `root_nodes` as `begin_node` on `root_bricks`.`root_id` = `begin_node`.`root_id` && `root_bricks`.`begin_point` = `begin_node`.`begin_point` -- @todo убрать из sql в будущем, разобравшись в коде
                right join
                    `root_nodes` as `end_node` on `root_bricks`.`root_id` = `end_node`.`root_id` && `root_bricks`.`end_point` = `end_node`.`end_point`            
                where
                    `schedule`.`dispatch_date` = '".$datetime->format("Y-m-d")."'
                    and `root_bricks`.`cost` > 0
                    and    `root_bricks`.`begin_point` = '".$db->escape($dispatchStation)."'
                    and    `root_bricks`.`end_point` = '".$db->escape($arrivalStation)."'
                    and `begin_node`.`order` <= `end_node`.`order`
                order by
                    `schedule`.`dispatch_datetime`
            ");
    
    
        $return = array();
    
        while ($row = $db->fetch_qualified_array())
        {
            $result =
            (new app_tickets_search_result())
            ->setDispatchStationId($dispatchStation)
            ->setArrivalStationId($arrivalStation)
            ->setBusRoot((new app_bus_root())->setAttributes($row['root_info']))
            ->setBusRootBrick((new app_bus_root_brick())->setAttributes($row['root_bricks']))
            ->setBusSchedule((new app_bus_schedule())->setAttributes($row['schedule']))
            ->setCarrier((new app_carriers_carrier())->setAttributes($row['carriers']))
            ;
        
            if ($result->getDispatchDatetime(null))
            {
                /* не продаем за два часа до отправки */
                $now = new DateTime("now");
                $now->add(new DateInterval("PT3H"));
            
                if ($result->getDispatchDatetime(null) > $now)
                {
                    $return[] = $result;
                }
            }
        }
    
        return $return;
    }

или еще одна типичная (но не тривиальная задача - фильтр типа "и" для каталога по разным параметрам с возможностью выбора нескольких возможных значений для одного параметра , explain про индексы ниже):

PHP:

public function getItemsByCategoriesIds(array $array, $onlyPublic = false, $toTop = false)
    {
        if (!sizeof($array))
        {
            return array();
        }
    
        $toTopStr = $toTop ? "`app_catalog_item`.`isTop` desc, " : null;

        $db = new app_db();

        array_walk($array, array($db, "escape"));
    
        $add = array();
        if(is_array($this->filter)) {
            foreach($this->filter as $param=>$value) {
                if($value==0) continue;
                $value_arr = explode(",", $value);
            
                $add_str =  " (`app_catalog_filter_values`.`param`='" . $db->escape($param) . "'";
            
                if (sizeof($value_arr) != 0) {
                    $add_str .=  " AND (";
                    foreach ($value_arr as $elem) {
                        $add_str .= "`app_catalog_filter_values`.`value` = '" . $db->escape($elem) . "' or ";
                    }
                    $add_str = substr($add_str, 1, -3);
                    $add_str .= ")";
                }
                $add_str .= ")";
                $add[] = $add_str;
            }
        }

        if(!is_array($this->filter) || count($add)==0) {

            $sql = "select " . ($this->getPagination()?"sql_calc_found_rows":"") . " `app_catalog_item`.* from `app_catalog_item`
                    LEFT JOIN `app_catalog_category` ON `app_catalog_category`.`id` = `app_catalog_item`.`categoryId`
                    where ".($onlyPublic ? "`app_catalog_item`.`public` = '1' and (" : null)." `categoryId` IN ('".implode("', '", $array)."') ".($onlyPublic ? ")" : null)."
                    order by {$toTopStr} `app_catalog_category`.`position`, `position`";

        } else {

            $sql = "select " . ($this->getPagination()?"sql_calc_found_rows":"") . "`app_catalog_item`.*, count(`app_catalog_item`.`id`) as matched
                from `app_catalog_item`
                LEFT JOIN `app_catalog_category` ON `app_catalog_category`.`id` = `app_catalog_item`.`categoryId`
                RIGHT JOIN `app_catalog_filter_values` ON(`app_catalog_filter_values`.`item` = `app_catalog_item`.`id` )
                WHERE ".($onlyPublic ? "`app_catalog_item`.`public` = '1' and (" : null)."`categoryId` IN ('".implode("', '", $array)."') AND (";


            $sql .= implode(" OR ", $add) . ") ".($onlyPublic ? ")" : null)." GROUP BY `app_catalog_item`.`id` HAVING `matched` = '" . count($add) . "'";
            $sql .= " order by {$toTopStr} `app_catalog_category`.`position`, `position`";
        }

        if($this->getPagination()) {         
            $sql .= " LIMIT ".$this->getPagination()->getOffset().", ".$this->getPagination()->getLimit() . "";
        }
    
        $db->query($sql);
        //echo $sql; die();
        if($this->getPagination()) {
            $this->getPagination()->setCount($db->found_rows());
        }

        $objects = array();

        while ($row = $db->fetch())
        {
            $objects[] = (new app_catalog_item())->setAttributes($row);
        }

        return $objects;
    }

Explain:

Код:

mysql> explain
    -> select sql_calc_found_rows`app_catalog_item`.*, count(`app_catalog_item`.`id`) as matched
    -> from `app_catalog_item`
    -> left join `app_catalog_category` ON `app_catalog_category`.`id` = `app_catalog_item`.`categoryId`
    -> right join `app_catalog_filter_values` ON(`app_catalog_filter_values`.`item` = `app_catalog_item`.`id` )
    -> where `app_catalog_item`.`public` = '1' and (`categoryId` IN ('691') and ((`app_catalog_filter_values`.`param`='10' and
    -> (`app_catalog_filter_values`.`value` = '62' or `app_catalog_filter_values`.`value` = '63' )) or
    -> (`app_catalog_filter_values`.`param`='19' AND (`app_catalog_filter_values`.`value` = '29' ))) )
    -> group by `app_catalog_item`.`id`
    -> having `matched` = '2'
    -> order by `app_catalog_category`.`position`, `position`
    -> limit 0, 51
    -> \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: app_catalog_filter_values
         type: range
possible_keys: PRIMARY,param
          key: param
      key_len: 8
          ref: NULL
         rows: 83
        Extra: Using where; Using index; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: app_catalog_item
         type: eq_ref
possible_keys: PRIMARY,categoryId,categoryId_2
          key: PRIMARY
      key_len: 4
          ref: xxx.app_catalog_filter_values.item
         rows: 1
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: app_catalog_category
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: xxx.app_catalog_item.categoryId
         rows: 1
        Extra:
3 rows in set (0.00 sec)

mysql>

Типичные селекты по одному параметру конечно можно дергать, но представим в чем его преимущество перед SQL запросом, написанном в строчке?

c0dex · 25 Июн 2014

Активист, заканчивайте уже

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

флоппик · 25 Июн 2014

where ".($onlyPublic ? "`app_catalog_item`.`public` = '1' and (" : null)." `categoryId` IN ('".implode("', '", $array)."') ".($onlyPublic ? ")" : null)."

Вот это (тут очень плохое слово, запрещенное модератором, но единственно подходящее), конечно. Для этого и нужен орм.

AmdY · 25 Июн 2014

ну вот хороший пример, зачем нужны qb, а то такие простыни без qb поддерживать вовсе ад.

c0dex · 25 Июн 2014

флоппик, ай, щас придет опять @grigori и будет возбухать, что тут матерятся)

Активист · 25 Июн 2014

флоппик написал(а):
where ".($onlyPublic ? "`app_catalog_item`.`public` = '1' and (" : null)." `categoryId` IN ('".implode("', '", $array)."') ".($onlyPublic ? ")" : null)."
Вот это пиздец, конечно. Для этого и нужен орм.

Ни один ОРМ не "построит (build)" подобный SQL, который из 500K товаров и 6M параметров с доп выборками по нескольким категориям, учитывая не только вариации фильтра, но и признак общедоступности, который вытянет 2 строки за 0.01 сек

Код:

select sql_no_cache sql_calc_found_rows `app_catalog_item`.*, co ...
mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000193 |
| checking permissions | 0.000014 |
| checking permissions | 0.000009 |
| checking permissions | 0.000013 |
| Opening tables       | 0.000054 |
| System lock          | 0.000024 |
| init                 | 0.000107 |
| optimizing           | 0.000044 |
| statistics           | 0.000358 |
| preparing            | 0.000057 |
| Creating tmp table   | 0.000595 |
| executing            | 0.000017 |
| Copying to tmp table | 0.003237 |
| Sorting result       | 0.000332 |
| Sending data         | 0.000102 |
| end                  | 0.000014 |
| removing tmp table   | 0.000231 |
| end                  | 0.000016 |
| query end            | 0.000014 |
| closing tables       | 0.000032 |
| freeing items        | 0.000057 |
| logging slow query   | 0.000011 |
| cleaning up          | 0.000013 |
+----------------------+----------+
23 rows in set (0.00 sec)

mysql>

Фанат · 25 Июн 2014

Я считаю, что правы и те, и те, и эти.
- ORM для 99% задач,
- билдер там где нужен запрос чуть сложнее
- raw (но с обязательной поддержкой плейсхолдеров) - для исклюительных случаев

Фанат · 25 Июн 2014

Но вот за

PHP:

$db = (new app_db())->query_params("select sql_calc_found_rows * from `app_subscriber_base` where `user_id` = ? limit ?, ?", array("iii", $user_id, $this->getPagination()->getOffset(), $this->getPagination()->getLimit()));

убил бы.

А потом переписал бы на

PHP:

$sql = "select sql_calc_found_rows * from `app_subscriber_base` where `user_id` = ?i limit ?i, ?i";
$data = array(
    $user_id,
    $this->getPagination()->getOffset(),
    $this->getPagination()->getLimit()),
);
$db = db::query($sql, $data);

- нормальный raw запрос без новнокода

AmdY · 25 Июн 2014

при чём здесь orm, речь о qb, с помощью его твой запрос строится легко и читабельность будет лучше лапши из примера с инлайн ифами.

а sql_calc_found_rows ты используешь во многом из-за отсутствия метода $qb->count();

WMix · 25 Июн 2014

Активист,
метод ->get() вернет некую обложку для обьекта "запрос". до тех пор пока не обратишься к этой обложке как к массиву, запроса в базу не будет (lazy).
пагинатор знает об этом и подставит sql_calc_found_rows этот запрос перед тем как отдать его базе. и вот тут понимаешь разницу между голым sql-string и object типа query.
все остальное вроде ответили.

Фанат · 25 Июн 2014

AmdY написал(а):
при чём здесь orm, речь о qb, с помощью его твой запрос строится легко и читабельность будет

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

Вот к примеру, насколько я знаю, grigori и Redjik протолкнули в Йии патч, который позволяет добавлять индекс -хинтинг к запросу, построяемому квери билдером.

Давайте их попросим привести здесь запрос, где это используется.

И сравним - у кого говнокодистее

Lewik · 25 Июн 2014

Фанат написал(а):
Я считаю, что правы и те, и те, и эти.
- ORM для 99% задач,
- билдер там где нужен запрос чуть сложнее
- raw для исклюительных случаев

Полностью согласен.

Активист · 25 Июн 2014

Фанат написал(а):
Но вот за

PHP:

$db = (new app_db())->query_params("select sql_calc_found_rows * from `app_subscriber_base` where `user_id` = ? limit ?, ?", array("iii", $user_id, $this->getPagination()->getOffset(), $this->getPagination()->getLimit()));

убил бы.

А потом переписал бы на

PHP:

$sql = "select sql_calc_found_rows * from `app_subscriber_base` where `user_id` = ?i limit ?i, ?i"; $data = array( $user_id, $this->getPagination()->getOffset(), $this->getPagination()->getLimit()), ); $db = db::query($sql, $data);

- нормальный raw запрос без новнокода

Что говнокод?Передаче параметров в стиле mysqli bind_param или вызорва метода объекта в стиле (new app_db())->, а не костылем в виде вызова статического метода класса, с передачей области видимости объекта методу, который вообще не должен ничего знать об этом объекте?

Фанат · 25 Июн 2014

Активист написал(а):
Что говнокод?Передаче параметров в стиле mysqli bind_param или вызорва метода объекта в стиле (new app_db())->, а не костылем в виде вызова статичного метода класса из объекта?

Всего лишь однострочность. Остальное уже стилистические мелочи.

Query Builder vs конкатенация запроса ручками

На какой стороне ты?

QB / ORM / etc

Пилю все ручками, не обламываюсь.

ЭОС

Новичок

герр M:)ller

Активист

Новичок

Активист

герр M:)ller

Активист

web.dev 2002-...

promotor fidei

Пью пиво

web.dev 2002-...

Активист

oncle terrible

oncle terrible

Пью пиво

герр M:)ller

oncle terrible

Новичок

Активист

oncle terrible