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
Brak komentarzy:
Prześlij komentarz