reprogl
536 строк · 10.0 Кб
1package repositories2
3import (4"database/sql"5"errors"6"fmt"7"time"8
9"github.com/doug-martin/goqu/v9"10"xelbot.com/reprogl/models"11)
12
13const RecentPostsCount = 614
15type ArticleRepository struct {16DB *sql.DB17}
18
19func (ar *ArticleRepository) GetBySlug(slug string, isAdmin bool) (*models.Article, error) {20ds := goqu.Dialect("mysql8").From(goqu.T("posts").As("p")).Select(21"p.id",22"p.title",23"p.url",24"p.text_post",25"p.description",26"p.time_created",27"p.last_update",28"p.comments_count",29"p.views_count",30"p.disable_comments",31goqu.I("mf.path").As("image_path"),32goqu.I("mf.width").As("image_width"),33"mf.src_set",34goqu.I("mf.description").As("image_alt"),35goqu.I("c.name").As("cat_name"),36goqu.I("c.url").As("cat_url"),37).InnerJoin(38goqu.T("category").As("c"),39goqu.On(goqu.Ex{40"c.id": goqu.I("p.category_id"),41}),42).LeftJoin(43goqu.T("media_file").As("mf"),44goqu.On(goqu.Ex{45"mf.post_id": goqu.I("p.id"),46"mf.default_image": goqu.L("1"),47}),48).Where(49goqu.Ex{50"p.url": slug,51},52)53
54if !isAdmin {55ds = ds.Where(goqu.Ex{56"p.hide": goqu.L("0"),57})58}59
60query, params, _ := ds.Prepared(true).ToSQL()61
62article := &models.Article{}63
64err := ar.DB.QueryRow(query, params...).Scan(65&article.ID,66&article.Title,67&article.Slug,68&article.Text,69&article.Description,70&article.CreatedAt,71&article.UpdatedAt,72&article.CommentsCount,73&article.Views,74&article.DisabledComments,75&article.ImagePath,76&article.Width,77&article.SrcSet,78&article.Alt,79&article.CategoryName,80&article.CategorySlug)81
82if err != nil {83if errors.Is(err, sql.ErrNoRows) {84return nil, models.RecordNotFound85} else {86return nil, err87}88}89
90return article, nil91}
92
93func (ar *ArticleRepository) GetIdBySlug(slug string) int {94var id int95
96err := ar.DB.QueryRow(97`SELECT id FROM posts WHERE url = ?`,98slug,99).Scan(&id)100
101if err != nil {102return 0103}104
105return id106}
107
108func (ar *ArticleRepository) GetCollection(page int, isAdmin bool) (*models.ArticlesPaginator, error) {109countQuery := `110SELECT
111COUNT(p.id) AS cnt
112FROM posts AS p
113WHERE p.hide = 0`
114
115ds := goqu.Dialect("mysql8").From(goqu.T("posts").As("p")).Select(116"p.id",117"p.title",118"p.url",119"p.text_post",120"p.preview",121"p.time_created",122"p.comments_count",123"p.hide",124"mf.picture_tag",125goqu.I("c.name").As("cat_name"),126goqu.I("c.url").As("cat_url"),127).InnerJoin(128goqu.T("category").As("c"),129goqu.On(goqu.Ex{130"c.id": goqu.I("p.category_id"),131}),132).LeftJoin(133goqu.T("media_file").As("mf"),134goqu.On(goqu.Ex{135"mf.post_id": goqu.I("p.id"),136"mf.default_image": goqu.L("1"),137}),138).Order(goqu.I("p.time_created").Desc())139
140if !isAdmin {141ds = ds.Where(goqu.Ex{142"p.hide": goqu.L("0"),143})144}145
146query, _, _ := ds.ToSQL()147query += " LIMIT 10 OFFSET ?"148
149params := make([]interface{}, 0)150
151return ar.newPaginator(countQuery, query, page, params...)152}
153
154func (ar *ArticleRepository) GetCollectionByCategory(category *models.Category, page int) (*models.ArticlesPaginator, error) {155countQuery := `156SELECT
157COUNT(p.id) AS cnt
158FROM posts AS p
159INNER JOIN category AS c ON c.id = p.category_id
160WHERE p.hide = 0
161AND c.tree_left_key >= ?
162AND c.tree_right_key <= ?`
163
164query := `165SELECT
166p.id,
167p.title,
168p.url,
169p.text_post,
170p.preview,
171p.time_created,
172p.comments_count,
173p.hide,
174mf.picture_tag,
175c.name AS cat_name,
176c.url AS cat_url
177FROM posts AS p
178INNER JOIN category AS c ON c.id = p.category_id
179LEFT JOIN media_file mf ON (p.id = mf.post_id AND mf.default_image = 1)
180WHERE p.hide = 0
181AND c.tree_left_key >= ?
182AND c.tree_right_key <= ?
183ORDER BY time_created DESC
184LIMIT 10 OFFSET ?`
185
186params := make([]interface{}, 0)187params = append(params, category.LeftKey, category.RightKey)188
189return ar.newPaginator(countQuery, query, page, params...)190}
191
192func (ar *ArticleRepository) GetCollectionByTag(tag *models.Tag, page int) (*models.ArticlesPaginator, error) {193countQuery := `194SELECT
195COUNT(p.id) AS cnt
196FROM posts AS p
197INNER JOIN relation_topictag AS at ON p.id = at.post_id
198WHERE p.hide = 0
199AND at.tag_id = ?`
200
201query := `202SELECT
203p.id,
204p.title,
205p.url,
206p.text_post,
207p.preview,
208p.time_created,
209p.comments_count,
210p.hide,
211mf.picture_tag,
212c.name AS cat_name,
213c.url AS cat_url
214FROM posts AS p
215INNER JOIN category AS c ON c.id = p.category_id
216LEFT JOIN media_file mf ON (p.id = mf.post_id AND mf.default_image = 1)
217INNER JOIN relation_topictag AS at ON p.id = at.post_id
218WHERE p.hide = 0
219AND at.tag_id = ?
220ORDER BY time_created DESC
221LIMIT 10 OFFSET ?`
222
223params := make([]interface{}, 0)224params = append(params, tag.ID)225
226return ar.newPaginator(countQuery, query, page, params...)227}
228
229func (ar *ArticleRepository) GetSitemapCollection() (models.SitemapItemList, error) {230query := `231SELECT
232url,
233updated_at
234FROM posts
235WHERE
236hide = 0
237ORDER BY time_created DESC
238`
239
240rows, err := ar.DB.Query(query)241if err != nil {242return nil, err243}244
245defer rows.Close()246
247articles := models.SitemapItemList{}248
249for rows.Next() {250item := models.SitemapItem{}251err = rows.Scan(252&item.Slug,253&item.UpdatedAt)254
255if err != nil {256return nil, err257}258
259articles = append(articles, &item)260}261
262return articles, nil263}
264
265func (ar *ArticleRepository) GetFeedCollection() (models.FeedItemList, error) {266query := `267SELECT
268p.id,
269p.title,
270p.url,
271p.text_post,
272mf.src_set,
273p.updated_at,
274p.time_created
275FROM posts AS p
276LEFT JOIN media_file AS mf ON (p.id = mf.post_id AND mf.default_image = 1)
277WHERE
278hide = 0
279ORDER BY time_created DESC
280LIMIT 25
281`
282
283rows, err := ar.DB.Query(query)284if err != nil {285return nil, err286}287
288defer rows.Close()289
290articles := models.FeedItemList{}291
292for rows.Next() {293item := models.FeedItem{}294err = rows.Scan(295&item.ID,296&item.Title,297&item.Slug,298&item.Text,299&item.SrcSet,300&item.UpdatedAt,301&item.CreatedAt)302
303if err != nil {304return nil, err305}306
307articles = append(articles, &item)308}309
310return articles, nil311}
312
313func (ar *ArticleRepository) GetRecentPostsCollection(articleId int) (*models.RecentPostList, error) {314query := fmt.Sprintf(`315SELECT
316title,
317url
318FROM posts
319WHERE
320hide = 0
321AND id != ?
322ORDER BY time_created DESC
323LIMIT %d`, RecentPostsCount)324
325rows, err := ar.DB.Query(query, articleId)326if err != nil {327return nil, err328}329
330defer rows.Close()331
332articles := models.RecentPostList{}333
334for rows.Next() {335item := models.RecentPost{}336err = rows.Scan(337&item.Title,338&item.Slug)339
340if err != nil {341return nil, err342}343
344articles = append(articles, &item)345}346
347return &articles, nil348}
349
350func (ar *ArticleRepository) GetLastRecentPostsID() (int, error) {351query := fmt.Sprintf(`352SELECT
353MIN(src.id) AS id
354FROM (
355SELECT id
356FROM posts
357WHERE hide = 0
358ORDER BY time_created DESC
359LIMIT %d) AS src`, RecentPostsCount)360
361var id int362err := ar.DB.QueryRow(query).Scan(&id)363if err != nil {364return 0, err365}366
367return id, nil368}
369
370func (ar *ArticleRepository) GetByIdForComment(id int) (*models.ArticleForComment, error) {371query := `372SELECT
373p.id,
374p.url,
375p.hide
376FROM posts AS p
377WHERE (p.id = ?)`
378
379article := &models.ArticleForComment{}380
381err := ar.DB.QueryRow(query, id).Scan(382&article.ID,383&article.Slug,384&article.Hidden)385
386if err != nil {387if errors.Is(err, sql.ErrNoRows) {388return nil, models.RecordNotFound389} else {390return nil, err391}392}393
394return article, nil395}
396
397func (ar *ArticleRepository) newPaginator(countQuery, query string, page int, params ...interface{}) (*models.ArticlesPaginator, error) {398var articleCount int399
400err := ar.DB.QueryRow(countQuery, params...).Scan(&articleCount)401if err != nil {402return nil, err403}404
405pageCount := articleCount / 10406if articleCount%10 != 0 {407pageCount += 1408}409
410if page > pageCount {411return nil, models.RecordNotFound412}413
414offset := 10 * (page - 1)415params = append(params, offset)416rows, err := ar.DB.Query(query, params...)417if err != nil {418return nil, err419}420
421defer rows.Close()422
423articles, err := populateArticles(rows)424if err != nil {425return nil, err426}427
428return &models.ArticlesPaginator{Items: articles, CurrentPage: page, PageCount: pageCount}, nil429}
430
431func (ar *ArticleRepository) GetMostVisitedArticlesOfMonth() ([]models.ArticleStatItem, error) {432query := `433SELECT
434p.title,
435p.url,
436COUNT(t.id) AS cnt
437FROM posts AS p
438INNER JOIN tracking AS t ON t.post_id = p.id
439INNER JOIN tracking_agent AS ta ON t.user_agent_id = ta.id
440WHERE t.time_created > ?
441AND ta.is_bot = 0
442GROUP BY p.id
443ORDER BY cnt DESC
444LIMIT 6`
445
446from := time.Now().Add(-30 * 24 * time.Hour).Format(time.DateTime)447
448rows, err := ar.DB.Query(query, from)449if err != nil {450return nil, err451}452
453defer rows.Close()454
455data := make([]models.ArticleStatItem, 0, 6)456for rows.Next() {457item := models.ArticleStatItem{}458err = rows.Scan(459&item.Title,460&item.Slug,461&item.Views)462
463if err != nil {464return nil, err465}466
467data = append(data, item)468}469
470return data, nil471}
472
473func (ar *ArticleRepository) GetMostVisitedArticles() ([]models.ArticleStatItem, error) {474query := `475SELECT
476p.title,
477p.url,
478p.views_count AS cnt
479FROM posts AS p
480ORDER BY cnt DESC
481LIMIT 6`
482
483rows, err := ar.DB.Query(query)484if err != nil {485return nil, err486}487
488defer rows.Close()489
490data := make([]models.ArticleStatItem, 0, 6)491for rows.Next() {492item := models.ArticleStatItem{}493err = rows.Scan(494&item.Title,495&item.Slug,496&item.Views)497
498if err != nil {499return nil, err500}501
502data = append(data, item)503}504
505return data, nil506}
507
508func populateArticles(rows *sql.Rows) (models.ArticleList, error) {509var err error510
511articles := models.ArticleList{}512
513for rows.Next() {514article := &models.ArticleListItem{}515err = rows.Scan(516&article.ID,517&article.Title,518&article.Slug,519&article.Text,520&article.Preview,521&article.CreatedAt,522&article.CommentsCount,523&article.Hidden,524&article.PictureTag,525&article.CategoryName,526&article.CategorySlug)527
528if err != nil {529return nil, err530}531
532articles = append(articles, article)533}534
535return articles, nil536}
537