aboutsummaryrefslogtreecommitdiff
path: root/app/Models/EntryDAO.php
diff options
context:
space:
mode:
Diffstat (limited to 'app/Models/EntryDAO.php')
-rw-r--r--app/Models/EntryDAO.php99
1 files changed, 71 insertions, 28 deletions
diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php
index aea6e6f65..8713ba930 100644
--- a/app/Models/EntryDAO.php
+++ b/app/Models/EntryDAO.php
@@ -1184,13 +1184,15 @@ SQL;
/**
* @param numeric-string $id_min
* @param numeric-string $id_max
- * @param 'id'|'date'|'link'|'title'|'rand' $sort
+ * @param 'id'|'c.name'|'date'|'f.name'|'link'|'title'|'rand' $sort
* @param 'ASC'|'DESC' $order
+ * @param numeric-string $continuation_id
+ * @param list<string|int> $continuation_values
* @return array{0:list<int|string>,1:string}
*/
protected function sqlListEntriesWhere(string $alias = '', int $state = FreshRSS_Entry::STATE_ALL, ?FreshRSS_BooleanSearch $filters = null,
string $id_min = '0', string $id_max = '0', string $sort = 'id', string $order = 'DESC',
- string $continuation_id = '0', string|int $continuation_value = 0): array {
+ string $continuation_id = '0', array $continuation_values = []): array {
$search = ' ';
$values = [];
if ($state & FreshRSS_Entry::STATE_ANDS) {
@@ -1250,13 +1252,29 @@ SQL;
$values[] = $id_min;
}
- if ($continuation_id !== '0' && in_array($sort, ['date', 'link', 'title'], true)) {
+ if ($continuation_id !== '0' && in_array($sort, ['c.name', 'date', 'f.name', 'link', 'title'], true)) {
$sign = $order === 'ASC' ? '>' : '<';
+ $orderBy = match ($sort) {
+ 'c.name' => 'c.name',
+ 'f.name' => 'f.name',
+ default => $alias . $sort,
+ };
// Keyset pagination (Compatibility syntax due to poor performance of tuple syntax in MySQL https://bugs.mysql.com/bug.php?id=104128)
- $search .= "AND ({$alias}{$sort} {$sign} ? OR ({$alias}{$sort} = ? AND {$alias}id {$sign}= ?)) ";
- $values[] = $continuation_value;
- $values[] = $continuation_value;
- $values[] = $continuation_id;
+ if ($sort === 'c.name') {
+ // Includes a secondary sort by feed name
+ $search .= "AND ((c.name {$sign} ?) OR (c.name = ? AND f.name {$sign} ?) OR (c.name = ? AND f.name = ? AND {$alias}id {$sign}= ?)) ";
+ $values[] = $continuation_values[0];
+ $values[] = $continuation_values[0];
+ $values[] = $continuation_values[1];
+ $values[] = $continuation_values[0];
+ $values[] = $continuation_values[1];
+ $values[] = $continuation_id;
+ } else {
+ $search .= "AND ({$orderBy} {$sign} ? OR ({$orderBy} = ? AND {$alias}id {$sign}= ?)) ";
+ $values[] = $continuation_values[0];
+ $values[] = $continuation_values[0];
+ $values[] = $continuation_id;
+ }
}
if ($filters !== null && count($filters->searches()) > 0) {
@@ -1276,15 +1294,16 @@ SQL;
* @param int $id category/feed/tag ID
* @param numeric-string $id_min
* @param numeric-string $id_max
- * @param 'id'|'date'|'link'|'title'|'rand' $sort
+ * @param 'id'|'c.name'|'date'|'f.name'|'link'|'title'|'rand' $sort
* @param 'ASC'|'DESC' $order
* @param numeric-string $continuation_id
+ * @param list<string|int> $continuation_values
* @return array{0:list<int|string>,1:string}
* @throws FreshRSS_EntriesGetter_Exception
*/
private function sqlListWhere(string $type = 'a', int $id = 0, int $state = FreshRSS_Entry::STATE_ALL, ?FreshRSS_BooleanSearch $filters = null,
string $id_min = '0', string $id_max = '0', string $sort = 'id', string $order = 'DESC',
- string $continuation_id = '0', string|int $continuation_value = 0, int $limit = 1, int $offset = 0): array {
+ string $continuation_id = '0', array $continuation_values = [], int $limit = 1, int $offset = 0): array {
if (!$state) {
$state = FreshRSS_Entry::STATE_ALL;
}
@@ -1334,21 +1353,28 @@ SQL;
}
$order = in_array($order, ['ASC', 'DESC'], true) ? $order : 'DESC';
- $sort = in_array($sort, ['id', 'date', 'link', 'title', 'rand'], true) ? $sort : 'id';
- $orderBy = ($sort === 'rand' ? static::sqlRandom() : 'e.' . $sort);
+ $sort = in_array($sort, ['id', 'c.name', 'date', 'f.name', 'link', 'title', 'rand'], true) ? $sort : 'id';
+ $orderBy = match ($sort) {
+ 'c.name' => 'c.name',
+ 'f.name' => 'f.name',
+ 'rand' => static::sqlRandom(),
+ default => 'e.' . $sort,
+ };
[$searchValues, $search] = $this->sqlListEntriesWhere(alias: 'e.', state: $state, filters: $filters, id_min: $id_min, id_max: $id_max,
- sort: $sort, order: $order, continuation_id: $continuation_id, continuation_value: $continuation_value);
+ sort: $sort, order: $order, continuation_id: $continuation_id, continuation_values: $continuation_values);
return [array_merge($values, $searchValues), 'SELECT '
. ($type === 'T' ? 'DISTINCT ' : '')
. 'e.id'
- . ($type === 'T' && $orderBy !== 'e.id' ? ', ' . $orderBy : '') // SELECT DISTINCT, ORDER BY expressions must appear in SELECT
+ . ($type === 'T' && $sort !== 'id' ? ', ' . $orderBy : '') // SELECT DISTINCT, ORDER BY expressions must appear in SELECT
. ' FROM `_entry` e '
- . 'INNER JOIN `_feed` f ON e.id_feed = f.id '
+ . 'INNER JOIN `_feed` f ON f.id = e.id_feed '
+ . ($sort === 'c.name' ? 'INNER JOIN `_category` c ON c.id = f.category ' : '')
. ($type === 't' || $type === 'T' ? 'INNER JOIN `_entrytag` et ON et.id_entry = e.id ' : '')
. 'WHERE ' . $where
. $search
. 'ORDER BY ' . $orderBy . ' ' . $order
+ . ($sort === 'c.name' ? ', f.name ' . $order : '') // Secondary sort
. ($sort === 'id' ? '' : ', e.id ' . $order) // For keyset pagination
. ($limit > 0 ? ' LIMIT ' . $limit : '') // http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/
. ($offset > 0 ? ' OFFSET ' . $offset : '')
@@ -1360,29 +1386,44 @@ SQL;
* @param int $id category/feed/tag ID
* @param numeric-string $id_min
* @param numeric-string $id_max
- * @param 'id'|'date'|'link'|'title'|'rand' $sort
+ * @param 'id'|'c.name'|'date'|'f.name'|'link'|'title'|'rand' $sort
* @param 'ASC'|'DESC' $order
* @param numeric-string $continuation_id
+ * @param list<string|int> $continuation_values
* @throws FreshRSS_EntriesGetter_Exception
*/
private function listWhereRaw(string $type = 'a', int $id = 0, int $state = FreshRSS_Entry::STATE_ALL, ?FreshRSS_BooleanSearch $filters = null,
string $id_min = '0', string $id_max = '0', string $sort = 'id', string $order = 'DESC',
- string $continuation_id = '0', string|int $continuation_value = 0, int $limit = 1, int $offset = 0): PDOStatement|false {
+ string $continuation_id = '0', array $continuation_values = [], int $limit = 1, int $offset = 0): PDOStatement|false {
$order = in_array($order, ['ASC', 'DESC'], true) ? $order : 'DESC';
- $sort = in_array($sort, ['id', 'date', 'link', 'title', 'rand'], true) ? $sort : 'id';
+ $sort = in_array($sort, ['id', 'c.name', 'date', 'f.name', 'link', 'title', 'rand'], true) ? $sort : 'id';
[$values, $sql] = $this->sqlListWhere($type, $id, $state, $filters, id_min: $id_min, id_max: $id_max, sort: $sort, order: $order,
- continuation_id: $continuation_id, continuation_value: $continuation_value, limit: $limit, offset: $offset);
-
- $orderBy = ($sort === 'rand' ? static::sqlRandom() : 'e0.' . $sort);
+ continuation_id: $continuation_id, continuation_values: $continuation_values, limit: $limit, offset: $offset);
+
+ $orderBy = match ($sort) {
+ 'c.name' => 'c0.name',
+ 'f.name' => 'f0.name',
+ 'rand' => static::sqlRandom(),
+ default => 'e0.' . $sort,
+ };
$content = static::isCompressed() ? 'UNCOMPRESS(e0.content_bin) AS content' : 'e0.content';
$hash = static::sqlHexEncode('e0.hash');
$sql = <<<SQL
SELECT e0.id, e0.guid, e0.title, e0.author, {$content}, e0.link, e0.date, {$hash} AS hash, e0.is_read, e0.is_favorite, e0.id_feed, e0.tags, e0.attributes
FROM `_entry` e0
INNER JOIN ({$sql}) e2 ON e2.id=e0.id
-ORDER BY {$orderBy} {$order}
SQL;
+ if ($sort === 'f.name' || $sort === 'c.name') {
+ $sql .= ' INNER JOIN `_feed` f0 ON f0.id = e0.id_feed ';
+ }
+ if ($sort === 'c.name') {
+ $sql .= ' INNER JOIN `_category` c0 ON c0.id = f0.category ';
+ }
+ $sql .= ' ORDER BY ' . $orderBy . ' ' . $order;
+ if ($sort === 'c.name') {
+ $sql .= ', f0.name ' . $order; // Secondary sort
+ }
if ($sort !== 'id') {
// For keyset pagination
$sql .= ', e0.id ' . $order;
@@ -1395,7 +1436,7 @@ SQL;
/** @var array{0:string,1:int,2:string} $info */
if ($this->autoUpdateDb($info)) {
return $this->listWhereRaw($type, $id, $state, $filters, id_min: $id_min, id_max: $id_max, sort: $sort, order: $order,
- continuation_id: $continuation_id, continuation_value: $continuation_value, limit: $limit, offset: $offset);
+ continuation_id: $continuation_id, continuation_values: $continuation_values, limit: $limit, offset: $offset);
}
Minz_Log::error('SQL error ' . __METHOD__ . json_encode($info));
return false;
@@ -1407,17 +1448,18 @@ SQL;
* @param int $id category/feed/tag ID
* @param numeric-string $id_min
* @param numeric-string $id_max
- * @param 'id'|'date'|'link'|'title'|'rand' $sort
+ * @param 'id'|'c.name'|'date'|'f.name'|'link'|'title'|'rand' $sort
* @param 'ASC'|'DESC' $order
* @param numeric-string $continuation_id
+ * @param list<string|int> $continuation_values
* @return Traversable<FreshRSS_Entry>
* @throws FreshRSS_EntriesGetter_Exception
*/
public function listWhere(string $type = 'a', int $id = 0, int $state = FreshRSS_Entry::STATE_ALL, ?FreshRSS_BooleanSearch $filters = null,
string $id_min = '0', string $id_max = '0', string $sort = 'id', string $order = 'DESC',
- string $continuation_id = '0', string|int $continuation_value = 0, int $limit = 1, int $offset = 0): Traversable {
+ string $continuation_id = '0', array $continuation_values = [], int $limit = 1, int $offset = 0): Traversable {
$stm = $this->listWhereRaw($type, $id, $state, $filters, id_min: $id_min, id_max: $id_max, sort: $sort, order: $order,
- continuation_id: $continuation_id, continuation_value: $continuation_value, limit: $limit, offset: $offset);
+ continuation_id: $continuation_id, continuation_values: $continuation_values, limit: $limit, offset: $offset);
if ($stm !== false) {
while (is_array($row = $stm->fetch(PDO::FETCH_ASSOC))) {
/** @var array{'id':string,'id_feed':int,'guid':string,'title':string,'author':string,'content':string,'link':string,'date':int,
@@ -1473,16 +1515,17 @@ SQL;
* @param int $id category/feed/tag ID
* @param numeric-string $id_min
* @param numeric-string $id_max
- * @param numeric-string $continuation_id
* @param 'ASC'|'DESC' $order
+ * @param numeric-string $continuation_id
+ * @param list<string|int> $continuation_values
* @return list<numeric-string>|null
* @throws FreshRSS_EntriesGetter_Exception
*/
public function listIdsWhere(string $type = 'a', int $id = 0, int $state = FreshRSS_Entry::STATE_ALL, ?FreshRSS_BooleanSearch $filters = null,
string $id_min = '0', string $id_max = '0', string $order = 'DESC',
- string $continuation_id = '0', string|int $continuation_value = 0, int $limit = 1, int $offset = 0): ?array {
+ string $continuation_id = '0', array $continuation_values = [], int $limit = 1, int $offset = 0): ?array {
[$values, $sql] = $this->sqlListWhere($type, $id, $state, $filters, id_min: $id_min, id_max: $id_max, order: $order,
- continuation_id: $continuation_id, continuation_value: $continuation_value, limit: $limit, offset: $offset);
+ continuation_id: $continuation_id, continuation_values: $continuation_values, limit: $limit, offset: $offset);
$stm = $this->pdo->prepare($sql);
if ($stm !== false && $stm->execute($values)) {
/** @var list<int|numeric-string> $res */
generated by cgit v1.2.3 (git 2.39.1) at 2026-02-10 13:22:44 +0000