问题描述
所以，我已经得到了后续模型的ActiveRecord，我将使用 Discussion.user_replied_group_discussions（@user）返回最近回答讨论好了，但我怎么修改私有方法订购方法：最新回复时间返回的讨论？
So I've got the follow model in activerecord, I will use Discussion.user_replied_group_discussions(@user)it returns the recent replied discussions alright, but how do I modify the private method to order the returned discussions by latest reply time?
# Table name: discussions
#
# id :integer not null, primary key
# title :string(255) not null
# content :text(255) not null
# created_at :datetime
# updated_at :datetime
# user_id :integer
# discussable_id :integer
# discussable_type :string(255)
class Discussion < ActiveRecord::Base
has_many :comments, :as => :commentable, :dependent => :destroy
#
#this is the scope that I am having trouble with:
#
scope :user_replied_group_discussions, lambda {|user| replied_by(user) }
private
def self.replied_by(user)
discussion_ids = %(SELECT commentable_id FROM comments
WHERE commentable_type = 'Discussion' AND user_id = :user_id)
where("discussable_type = 'Group' AND id IN (#{discussion_ids}) ",
{ :user_id => user })
end
end
# Table name: comments
#
# id :integer not null, primary key
# content :text
# created_at :datetime
# updated_at :datetime
# commentable_id :integer
# commentable_type :string(255)
# user_id :integer
#
class Comment < ActiveRecord::Base
belongs_to :commentable, :polymorphic => true
belongs_to :user
end
更新：通过@ypercube提供的SQL查询的帮助下，我现在使用它的find_by_sql。但似乎尴尬将在控制器。
UPDATE:The sql query provided by @ypercube helped, I'm now using it with find_by_sql. But it seems awkward putting that in the controller.
有没有更好的解决办法？谢谢！
推荐答案
这会根据最新的 discussions.updated_at 时间顺序：
This will order according to latest discussions.updated_at time:
where("discussable_type = 'Group' AND id IN (#{discussion_ids})
order by updated_at DESC ",
如果您想通过 comment.created_at 来订购，使用此查询：
If you want to order by comment.created_at, use this query:
SELECT d.*
, MAX(c.created_at) AS lastCommentTime
FROM discussions d
JOIN comments c
ON d.id = c.commentable_id
WHERE c.commentable_type = 'Discussion'
AND c.user_id = userIDtoCheck
GROUP BY d.id
ORDER BY lastCommentTime DESC <--- latest first
这篇关于SQL查询订单问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！