entry_pagination_builder.go 4.3 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166
  1. // Copyright 2018 Frédéric Guillot. All rights reserved.
  2. // Use of this source code is governed by the Apache 2.0
  3. // license that can be found in the LICENSE file.
  4. package storage // import "miniflux.app/storage"
  5. import (
  6. "database/sql"
  7. "fmt"
  8. "strings"
  9. "time"
  10. "miniflux.app/model"
  11. "miniflux.app/timer"
  12. )
  13. // EntryPaginationBuilder is a builder for entry prev/next queries.
  14. type EntryPaginationBuilder struct {
  15. store *Storage
  16. conditions []string
  17. args []interface{}
  18. entryID int64
  19. direction string
  20. }
  21. // WithSearchQuery adds full-text search query to the condition.
  22. func (e *EntryPaginationBuilder) WithSearchQuery(query string) {
  23. if query != "" {
  24. e.conditions = append(e.conditions, fmt.Sprintf("e.document_vectors @@ plainto_tsquery($%d)", len(e.args)+1))
  25. e.args = append(e.args, query)
  26. }
  27. }
  28. // WithStarred adds starred to the condition.
  29. func (e *EntryPaginationBuilder) WithStarred() {
  30. e.conditions = append(e.conditions, "e.starred is true")
  31. }
  32. // WithFeedID adds feed_id to the condition.
  33. func (e *EntryPaginationBuilder) WithFeedID(feedID int64) {
  34. if feedID != 0 {
  35. e.conditions = append(e.conditions, fmt.Sprintf("e.feed_id = $%d", len(e.args)+1))
  36. e.args = append(e.args, feedID)
  37. }
  38. }
  39. // WithCategoryID adds category_id to the condition.
  40. func (e *EntryPaginationBuilder) WithCategoryID(categoryID int64) {
  41. if categoryID != 0 {
  42. e.conditions = append(e.conditions, fmt.Sprintf("f.category_id = $%d", len(e.args)+1))
  43. e.args = append(e.args, categoryID)
  44. }
  45. }
  46. // WithStatus adds status to the condition.
  47. func (e *EntryPaginationBuilder) WithStatus(status string) {
  48. if status != "" {
  49. e.conditions = append(e.conditions, fmt.Sprintf("e.status = $%d", len(e.args)+1))
  50. e.args = append(e.args, status)
  51. }
  52. }
  53. // Entries returns previous and next entries.
  54. func (e *EntryPaginationBuilder) Entries() (*model.Entry, *model.Entry, error) {
  55. tx, err := e.store.db.Begin()
  56. if err != nil {
  57. return nil, nil, fmt.Errorf("begin transaction for entry pagination: %v", err)
  58. }
  59. prevID, nextID, err := e.getPrevNextID(tx)
  60. if err != nil {
  61. tx.Rollback()
  62. return nil, nil, err
  63. }
  64. prevEntry, err := e.getEntry(tx, prevID)
  65. if err != nil {
  66. tx.Rollback()
  67. return nil, nil, err
  68. }
  69. nextEntry, err := e.getEntry(tx, nextID)
  70. if err != nil {
  71. tx.Rollback()
  72. return nil, nil, err
  73. }
  74. tx.Commit()
  75. if e.direction == "desc" {
  76. return nextEntry, prevEntry, nil
  77. }
  78. return prevEntry, nextEntry, nil
  79. }
  80. func (e *EntryPaginationBuilder) getPrevNextID(tx *sql.Tx) (prevID int64, nextID int64, err error) {
  81. defer timer.ExecutionTime(time.Now(), fmt.Sprintf("[EntryPaginationBuilder] %v, %v", e.conditions, e.args))
  82. cte := `
  83. WITH entry_pagination AS (
  84. SELECT
  85. e.id,
  86. lag(e.id) over (order by e.published_at asc, e.id desc) as prev_id,
  87. lead(e.id) over (order by e.published_at asc, e.id desc) as next_id
  88. FROM entries AS e
  89. LEFT JOIN feeds AS f ON f.id=e.feed_id
  90. WHERE %s
  91. ORDER BY e.published_at asc, e.id desc
  92. )
  93. SELECT prev_id, next_id FROM entry_pagination AS ep WHERE %s;
  94. `
  95. subCondition := strings.Join(e.conditions, " AND ")
  96. finalCondition := fmt.Sprintf("ep.id = $%d", len(e.args)+1)
  97. query := fmt.Sprintf(cte, subCondition, finalCondition)
  98. e.args = append(e.args, e.entryID)
  99. var pID, nID sql.NullInt64
  100. err = tx.QueryRow(query, e.args...).Scan(&pID, &nID)
  101. switch {
  102. case err == sql.ErrNoRows:
  103. return 0, 0, nil
  104. case err != nil:
  105. return 0, 0, fmt.Errorf("entry pagination: %v", err)
  106. }
  107. if pID.Valid {
  108. prevID = pID.Int64
  109. }
  110. if nID.Valid {
  111. nextID = nID.Int64
  112. }
  113. return prevID, nextID, nil
  114. }
  115. func (e *EntryPaginationBuilder) getEntry(tx *sql.Tx, entryID int64) (*model.Entry, error) {
  116. var entry model.Entry
  117. err := tx.QueryRow(`SELECT id, title FROM entries WHERE id = $1`, entryID).Scan(
  118. &entry.ID,
  119. &entry.Title,
  120. )
  121. switch {
  122. case err == sql.ErrNoRows:
  123. return nil, nil
  124. case err != nil:
  125. return nil, fmt.Errorf("fetching sibling entry: %v", err)
  126. }
  127. return &entry, nil
  128. }
  129. // NewEntryPaginationBuilder returns a new EntryPaginationBuilder.
  130. func NewEntryPaginationBuilder(store *Storage, userID, entryID int64, direction string) *EntryPaginationBuilder {
  131. return &EntryPaginationBuilder{
  132. store: store,
  133. args: []interface{}{userID, "removed"},
  134. conditions: []string{"e.user_id = $1", "e.status <> $2"},
  135. entryID: entryID,
  136. direction: direction,
  137. }
  138. }