Web Majster - triki i rozwiązania: 2014

środa, 31 grudnia 2014

Pobranie poprzedniego i następnego rekordu dowolnego zapytania w MySQL

Poniższe funkcje są przykładem, jak pobrać sąsiadujące wiersze w dowolnym zapytaniu z uwzględnieniem sortowania:

public function getPrevItem($id, $onlyActive = true, $categoryId = null) {
    $where = ' WHERE 1 = 1 ';
    $sqlParams = array();

    if ($onlyActive) {
        $where .= ' AND a.`active` = 1 AND c.`active` = 1 ';
    }

    if ($categoryId !== null) {
        $where .= ' AND a.id_category = ? ';
        $sqlParams[] = $categoryId;
    }

    $this->getSql()->query("SET @item_offset = -1");
    $sql = "SELECT * FROM (
                SELECT @item_offset := @item_offset + 1 AS `item_offset`, id
                FROM (
                    SELECT a.id
                    FROM  `articles` a
                    JOIN `articles_category` c ON c.id = a.id_category
                    $where
                    ORDER BY a.`pos` DESC, a.`id` DESC
                ) AS x
            ) AS t WHERE t.id = ?";

    $itemOffset = (int) $this->getSql()->value($sql, array_merge($sqlParams, array($id)));
    if ($itemOffset == 0) {
        return false;
    }

    $prevOffset = $itemOffset - 1;
    $sql = "SELECT a.*, c.name AS category_name, c.slug AS category_slug
            FROM `articles` a
            JOIN `articles_category` c ON c.id = a.id_category
            $where
            ORDER BY a.`pos` DESC, a.`id` DESC
            LIMIT 1
            OFFSET $prevOffset";

    $prev = $this->getSql()->one($sql);
    return $prev;
}

public function getNextItem($id, $onlyActive = true, $categoryId = null) {
    $where = ' WHERE 1 = 1 ';
    $sqlParams = array();

    if ($onlyActive) {
        $where .= ' AND a.`active` = 1 AND c.`active` = 1 ';
    }

    if ($categoryId !== null) {
        $where .= ' AND a.id_category = ? ';
        $sqlParams[] = $categoryId;
    }

    $this->getSql()->query("SET @item_offset = -1");
    $sql = "SELECT * FROM (
                SELECT @item_offset := @item_offset + 1 AS `item_offset`, id
                FROM (
                    SELECT a.id
                    FROM  `articles` a
                    JOIN `articles_category` c ON c.id = a.id_category
                    $where
                    ORDER BY a.`pos` DESC, a.`id` DESC
                ) AS x
            ) AS t WHERE t.id = ?";

    $itemOffset = (int) $this->getSql()->value($sql, array_merge($sqlParams, array($id)));

    $nextOffset = $itemOffset + 1;
    $sql = "SELECT a.*, c.name AS category_name, c.slug AS category_slug
            FROM `articles` a
            JOIN `articles_category` c ON c.id = a.id_category
            $where
            ORDER BY a.`pos` DESC, a.`id` DESC
            LIMIT 1
            OFFSET $nextOffset";

    $next = $this->getSql()->one($sql);
    return $next;
}

Legenda:
$this->getSql()->query() - wykonuje zapytanie
$this->getSql()->value() - zwraca wartość pierwszej kolumny w pierwszym rekordzie wyniku zapytania
$this->getSql()->one() - zwraca jeden rekord zapytania