SQL查询订单问题 [英] sql query order problem

问题描述

所以，我已经得到了后续模型的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查询订单问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！