基于帖子数和频率构建趋势算法 [英] Building a trending algorithm based on post count and frequency

问题描述

说我有一个董事会模型。董事会有很多职位。我只是想查找在（x）天的时间内具有最高职位数的董事会。下面是我极其幼稚的方法。使用提供的代码，我得到了错误：

Say I have a Board model. Boards have many Posts. I simply want to find the boards that have the highest post count within the span of (x) days. Below is my extremely naive approach to this. With the code provided I get the error:

ActiveRecord::StatementInvalid (PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "posts")
LINE 1: SELECT  "boards".* FROM "boards" WHERE (board.posts.created_...
                                                ^
: SELECT  "boards".* FROM "boards" WHERE (board.posts.created_at >= '2019-06-05 12:14:30.661233') LIMIT $1

---

请让我知道，除了我收到的错误外，还有其他更好的方法。

---

Please let me know if there's a better way to do this in addition the error I'm receiving.

class Board < ApplicationRecord
  has_many :posts

  scope :trending, -> { includes(:posts).where('board.posts.created_at >= ?', Time.now-7.days).order(posts_count: :desc) }
end

class Post < ApplicationRecord
  belongs_to :board, counter_cache: true
end

更新：
所以我设法提出了一个可行的标准pe，但不能100％肯定它是否是最佳选择。您的想法将不胜感激：

Update: So I managed to come up with a working scope but not 100% sure if it's the most optimal. Your thoughts would be appreciated:

scope :trending, -> { includes(:posts).where(posts: { created_at: Time.now - 7.days }).order(posts_count: :desc) }

推荐答案

更新：

Board.joins(:posts)
     .select("boards.*, count(posts.id) as latest_posts_count")
     .where('posts.created_at >= ?', 7.days.ago)
     .order('latest_posts_count desc')
     .group('boards.id')

尝试一下，您将需要加入并按 board_id

Try this, you will need to join it and group them by board_id

Board.joins(:posts)
     .select("boards.*, count(posts.id) as posts_count")
     .where('posts.created_at >= ?', 7.days.ago)
     .order('posts_count desc')
     .group('boards.id')

说明：

• 我们加入（内部加入）表格，因此默认情况下，您只获得至少有一个与之相关的帖子


• 我们根据帖子数对其进行了排序


• 我们根据board.id

这篇关于基于帖子数和频率构建趋势算法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！