mysql - Rails 3 限制搞乱了数据库查询中的顺序

Question

我正在使用 will_paginate gem 和 rails 3.2.3。我试图显示所有具有最新帖子排序的特定标签的帖子，所以我使用这个:

  @posts = Post.find_by_post_type_label(session[:current_post_type],@selected_labels)
             .paginate(:page => params[:page], :order => "comments.created_at DESC").all

但它返回的帖子乱序。每个页面都将被排序，但第二页将包含本应位于第一页的帖子。

例子:

Post.find_by_post_type_label(1,[])

返回帖子 [1,2,3,4,5,6,7,8,9,10]，每个帖子的最新评论都是几个月前的。它生成的sql是:

 SELECT `posts`.* FROM `posts` WHERE `post`.`post_type_id` = 1

和

 SELECT `comments`.* FROM `comments` WHERE `comments`.`post_id` IN ( 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 ) ORDER BY created_at DESC

如果我订购它，并限制它，例如:

Post.find_by_post_type_label(1,[]).order(comments.created_at DESC).limit(5)

will_paginate 就是这样做的，它返回帖子 2,3,4,5,7

生成后:

SELECT DISTINCT `post`.id FROM `posts` LEFT OUTER JOIN
`comments` ON `comments`.`post_id` = `post`.`id` WHERE 
`post`.`post_type_id` = 1 ORDER BY posts.created_at DESC LIMIT 5

和

 SELECT `post`.`id` AS t0_r0, `post`.`user_id` AS t0_r1, `post`.`name` AS 
 t0_r2, `post`.`image_id` AS t0_r3, `post`.`post_type_id` AS t0_r4, 
`post`.`description` AS t0_r5, `post`.`featured` AS t0_r6, `post`.`approved`
AS t0_r7, `comment`.`id` AS t1_r0, `comments`.`post_id` AS t1_r1, 
`comments`.`image_id` AS t1_r2, `comments`.`edits` AS t1_r3, 
`comments`.`color` AS t1_r4, `comments`.`min_reputation` AS t1_r5, 
`comments`.`created_at` AS t1_r6, `comments`.`updated_at` AS t1_r7 FROM 
`post` LEFT OUTER JOIN `comments` ON `comments`.`post_id` = 
`post`.`id` WHERE `post`.`post_type_id` = 1 AND `post`.`id` IN (2, 3, 
4, 5, 7) ORDER BY comments.created_at DESC

它在订购之前显然是有限制的，但我不知道如何改变它。任何帮助将不胜感激

编辑 1

关系是这样的:

Class Post
  belongs_to :user
  belongs_to :post_type
  has_many :comments, dependent: :destroy, order: 'created_at DESC'
  has_and_belongs_to_many :labels

Class Comment
  belongs_to :post
  belongs_to :user

评论是数据库中唯一带有时间戳的评论。第一个评论包含帖子的创建时间。

Answer 1

最终在 find_by_sql 中使用此 sql

SELECT  p1.*, iv.created_at FROM posts p1
INNER JOIN ( SELECT p2.id id, max(c.created_at) created_at FROM posts p2
            INNER JOIN comments c ON c.post_id = p2.id
            GROUP BY p2.id)
iv ON p1.id=iv.id ORDER BY iv.created_at DESC LIMIT 5