Избежать дублирования запроса SQL

30 июня 2021, 16:20

Доброго времени суток.
Не знаю, стоило ли сюда писать весь код, но я думаю, так будет нагляднее(а может и нет)
Есть такой код, он выполняет нужную функцию, но есть (как видно в середине) sql запрос, который практически копипастится, лишь для того, чтобы я мог получить количество всех записей до использования LIMIT можно ли избежать этого?

<?php 
    require __DIR__ . "/vendor/autoload.php";
    require __DIR__ . "/rb/rb.php";
    use RedBeanPHP\R as R;
 R::setup( 'mysql:host=localhost;dbname=dbforbraind',
 'root', '' );
if (isset($_GET['page=0']) || ($_GET['page'] )){
    $page = $_GET['page'];
}else{
    $page = 1;
}
 $notesOnPage = 3;
 $from = ($page - 1) * $notesOnPage;
 $binds = [
     'var1' => $from,
     'var2' => $notesOnPage
 ];

    $select = ('SELECT DISTINCT products.name AS "name",
    color.value AS "color", size.value AS "size" FROM products 
        JOIN `values` AS color ON color.products_id = products.id 
        JOIN `values` AS new ON new.products_id = products.id 
        JOIN `values` AS size ON size.products_id = products.id 
        JOIN `values` AS v ON v.products_id = products.id 
        WHERE 
        new.properties_id = 3 AND new.value = 1 
        AND 
        color.properties_id = 2 AND color.value = "GREEN" 
        AND 
        size.properties_id = 1 LIMIT :var1, :var2'); 
$result = R::getAll($select, $binds);
    // echo "<pre>";

     $select2 =R::getAll ('SELECT DISTINCT products.name AS "name",
     color.value AS "color", size.value AS "size" FROM products 
         JOIN `values` AS color ON color.products_id = products.id 
         JOIN `values` AS new ON new.products_id = products.id 
         JOIN `values` AS size ON size.products_id = products.id 
         JOIN `values` AS v ON v.products_id = products.id 
         WHERE 
         new.properties_id = 3 AND new.value = 1 
         AND 
         color.properties_id = 2 AND color.value = "GREEN" 
         AND 
         size.properties_id = 1');

$pages = 0;
        foreach ($select2 as $value) {
            $pages ++;
        }
$numbersOfPages = $pages/$notesOnPage;
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>Document</title>
</head>
<body>
<table>
       <thead>
           <tr>
               <th>name</th>
               <th>color</th>
               <th>size</th>
           </tr>
       </thead>
       <tbody>
           <?php foreach ($result as $value): ?>
           <tr>
               <td><?php echo $value['name'] ?></td>
               <td><?php echo $value['color']; ?></td>
               <td><?php echo $value['size'] ?></td>
           </tr>
           <?php endforeach; ?>
       </tbody>
   </table>
        <?php 
        for ($i=0; $i < ($numbersOfPages); $i++) { 
            ?>
            <a href="/?page=<?php echo $i+1 ?>"><?php echo $i+1 ?></a>
            <?php
        }
        ?>

</body>
</html>

Answer 1

Используйте COUNT() в SQL запросе - он не так сильно тормозит.

$select = ('COUNT(SELECT DISTINCT products.name AS "name",
    color.value AS "color", size.value AS "size" FROM products 
        JOIN `values` AS color ON color.products_id = products.id 
        JOIN `values` AS new ON new.products_id = products.id 
        JOIN `values` AS size ON size.products_id = products.id 
        JOIN `values` AS v ON v.products_id = products.id 
        WHERE 
        new.properties_id = 3 AND new.value = 1 
        AND 
        color.properties_id = 2 AND color.value = "GREEN" 
        AND 
        size.properties_id = 1)');