Замена списка выбираемых полей на count(*) в SQL

Фанат

oncle terrible
Команда форума
Хочется сделать пагинатор для бедных, но при этом не особо запариваться с парсингом SQL
Код:
$test = [
    'SELECT id FROM table WHERE 1',
    'SELECT name, (SELECT price, (SELECT 1+1) FROM table2) var FROM table1',
    'SELeCT FROMAGE FrOM table join orders_from on to=`from`'
];
$pat = '~^(select)(.*)(\s+from\s+)~i';
foreach ($test as $sql) {
    preg_match($pat, $sql, $m);
    var_dump(preg_replace($pat, '$1 count(*)$3', $sql));
}
Конечно работает для пары очевидных случаев, но понятное дело что только до поры-до времени
главная проблема, конечно - это from в строковых литералах
можно это как-то улучшить? или нафиг забить?
 

AmdY

Пью пиво
Команда форума
Для бедных есть же SQL_CALC_FOUND_ROWS, хотя он вроде депрекейтид в последних версиях
 

fixxxer

К.О.
Партнер клуба
Для бедных есть же SQL_CALC_FOUND_ROWS, хотя он вроде депрекейтид в последних версиях
Там, где депрекейтед, есть window functions, которыми его можно изобразить: select *, count(*) over () as rows_count from ...

Но все, кроме варианта Флоппика, сломается на запросах с UNION. Но там не совсем исходный, надо лимит-оффсет еще убрать, что тоже не особо тривиальным может оказаться.

Короче, мне сразу Sad Spirit-овский парсер SQL в AST вспоминается. Но это уже явно не для бедных
 

Фанат

oncle terrible
Команда форума
Для бедных есть же SQL_CALC_FOUND_ROWS, хотя он вроде депрекейтид в последних версиях
не, он потому и депрекейтед, что на больших объёмах умирает
мне Света объясняла как-то давно, SQL_CALC_FOUND_ROWS работает со скоростью отдачи последней страницы. то есть мускуль тупо ползет по резалтсету и считает строки.
 

Фанат

oncle terrible
Команда форума
select count(*) from (исходный запрос) ?
вот да, я тоже об этом подумал, но как-то засомневался, будет там using temporary, или нет.
почему-то мне кажется, что мускуль не сообразит что мне нужен только каунт, и честно выберет все данные, а потом уже только их посчитает.
но надо конечно попробовать
 
Последнее редактирование:

флоппик

promotor fidei
Команда форума
Партнер клуба
почему-то мне кажется, что мускуль не сообразит что мне нужен только каунт, и честно выберет все данные, а потом уже только их посчитает.
В целом, в 8-ке оптимайзер умеет довольно много умных штук. Но если ты делаешь "для бедных", то на их объемах вероятно будет все равно пофиг.
 

ksnk

прохожий
Еще можно выкинуть order by из запроса-счетчика, кроме лимита и списка полей. Мелочь, а все равно чуть быстрее должно быть
 

grigori

( ͡° ͜ʖ ͡°)
Команда форума
order by вообще ни разу не мелочь, из-за него может быть filesort,
и еще надо выкидывать все join, которые не участвуют в where
короче, да, ast
 

Фанат

oncle terrible
Команда форума
и еще надо выкидывать все join, которые не участвуют в where
короче, да, ast
ну тут кроме аст еще и какой-то хитрый оналез понадобится.
и, кстати, если насчет магической оптимизации select count(*) from (исходный запрос) я сильно не уверен, то вот в этом вопросе (выкидывания лишних джойнов) я бы вполне ему доверился
 

флоппик

promotor fidei
Команда форума
Партнер клуба
PHP:
/**
     * Run a pagination count query.
     *
     * @param  array  $columns
     * @return array
     */
    protected function runPaginationCountQuery($columns = ['*'])
    {
        if ($this->groups || $this->havings) {
            $clone = $this->cloneForPaginationCount();

            if (is_null($clone->columns) && ! empty($this->joins)) {
                $clone->select($this->from.'.*');
            }

            return $this->newQuery()
                ->from(new Expression('('.$clone->toSql().') as '.$this->grammar->wrap('aggregate_table')))
                ->mergeBindings($clone)
                ->setAggregate('count', $this->withoutSelectAliases($columns))
                ->get()->all();
        }

        $without = $this->unions ? ['orders', 'limit', 'offset'] : ['columns', 'orders', 'limit', 'offset'];

        return $this->cloneWithout($without)
                    ->cloneWithoutBindings($this->unions ? ['order'] : ['select', 'order'])
                    ->setAggregate('count', $this->withoutSelectAliases($columns))
                    ->get()->all();
    }
Ларавель кстати делает как я и предложил.
 

WMix

герр M:)ller
Партнер клуба
и еще надо выкидывать все join, которые не участвуют в where
SQL:
select
    users.*, posts.post

from (
    select 1 as id, 'aaa' as name
    union
    select 2 as id, 'bbb' as name
    union
    select 3 as id, 'ccc' as name
) as users

join (
    select 1 as id, 1 as user_id, 'hello' as post
    union
    select 2 as id, 3 as user_id, 'world' as post
) as posts
on users.id = posts.user_id
попробуй посты выкинуть
 

Фанат

oncle terrible
Команда форума
Я тут подумал, что если у нас не стопроцентный автомат, то всегда можно сделать полуавтомат

Код:
class Pagination
{
    protected PDO $pdo;
    protected string $sql;
    protected array $params;
    protected string $countSql;
    protected int $limit = 10;

    public function __construct(PDO $pdo, string $sql, array $params = []) {
        $this->pdo = $pdo;
        $this->sql = $sql;
        $this->params = $params;
    }

    public function setCountQuery(string $sql) {
        $this->countSql = $sql;
        return $this;
    }

    public function setLimit(int $limit) {
        $this->limit = $limit;
        return $this;
    }
    
    public function getPageCount() {
        return (int)ceil($this->getNumRecords() / $this->limit);
    }
    
    public function getNumRecords() {
        $this->countSql = $this->countSql ?? $this->getAutoCountQuery();
        $stmt = $this->pdo->prepare($this->countSql);
        $stmt->execute($this->params);
        return $stmt->fetchColumn();
    }
    
    public function getPageData(int $page, $mode = null, ...$fetchAllParams) {
        $offset = ($page - 1) * $this->limit;
        $limit = (int)$this->limit;

        $mode = $mode ?? $this->pdo->getAttribute(PDO::ATTR_DEFAULT_FETCH_MODE);
    
        $sql = "$this->sql LIMIT $offset, $limit";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($this->params);
        return $stmt->fetchAll($mode, ...$fetchAllParams);
    }

    public function getAutoCountQuery() {
        $pat = '~^(select)(.*)(\s+from\s+)~i';
        return preg_replace($pat, '$1 count(*)$3', $this->sql);
    }
}
 

WMix

герр M:)ller
Партнер клуба
PHP:
$pagination = new Pagination($pdo, "select sum(i) as x from test group by i%2 having x > 40");
@флоппик дело говорит
 
Последнее редактирование:

Valick

Новичок
Фанат, мне getPageData() не нравится. По хорошему просто бы вернуть модифицированный запрос с лимитами и оффсетами.
Ну или хотя бы получать эти значения, что бы можно было дописать в запрос руками. А в идеале и то и другое.
 
Последнее редактирование:

grigori

( ͡° ͜ʖ ͡°)
Команда форума
WMix + with + case + over + udf :) я знаю

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

PHP:
$count_query = /*SELECT count(*) skipped */ "FROM T1 INNER JOIN bar on T1.f2!=bar.foo";
$data_query = 'WITH cte AS (SELECT foo FROM bar) SELECT f1, f2 from T1 where cte.foo != f2 ORDER BY f1' /* LIMIT :limit offset :offset will be added by agreement*/;
new Pagination($pdo, $count_query, $data_query);
 
Последнее редактирование:
Сверху