ActiveRecord-从每个组中选择第一条记录 [英] ActiveRecord - select first record from each group
问题描述
我希望每个用户发送最新消息。这是示例数据
I want most recent message sent by each users. Here is sample data
表:对话
sender receiver message date
============================================================
1 2 "hi" "2013-03-04 09:55:01.122074"
2 1 "hello" "2013-03-04 09:55:32.903975"
1 2 "have you done with the taks?" "2013-03-04 09:57:46.383007"
2 1 "almost..." "2013-03-04 09:58:55.783219"
2 1 "should be able to finish 2day" "2013-03-04 09:59:28.950705"
2 3 "shall we start?" "2013-03-04 10:01:16.842725"
3 2 "give me a minute" "2013-03-04 10:01:41.994589"
3 2 "let us start" "2013-03-04 10:02:14.04551"
对于ID为2的用户,我应该能够得到以下两个记录
Where for user with id 2, I should be able to get following two record
1 2 "have you done with the taks?" "2013-03-04 09:57:46.383007"
3 2 "let us start" "2013-03-04 10:02:14.04551"
这是我的解决方案
型号:用户
class User < ActiveRecord::Base
has_many :chats_received, class_name: 'Conversation', foreign_key: 'receiver_id',order: "created_at DESC"
end
模型:对话
class Conversation < ActiveRecord::Base
attr_accessible :message, :read
belongs_to :sender, class_name: 'User'
belongs_to :receiver, class_name: 'User'
def most_recent_chat_received_from_connected_users
connected_users_chats = . . . # Get all conversations which are sent to current user. e.g., user with id 2
chats_grouped_by_senders = connected_users_chats.group_by { |conversation| conversation.sender_id }
chats_grouped_by_senders.inject([]){|memo , (sender_id, conversations)| memo << conversations.first; memo}
end
end
从连接中获取最新消息用户:
user = User.find 2
user.most_recent_chat_received_from_connected_users
尽管此解决方案有效,但它会为两个用户之间的每次转换选择并创建模型。我也觉得这不是获取必需行的方法。
Though this solution works, it select and create models for each conversion between two user. Also I feel it is not rails way of getting required rows.
我一直在使用postgresql。尝试在该模式下使用组方法时,出现以下错误。
I have been using postgresql. I have been getting following error when I try to use group method on the mode.
ActiveRecord::StatementInvalid: PG::Error: ERROR: column "conversations.id" must appear in the GROUP BY clause or be used in an aggregate function
是否有更好的方法来获得相同的结果?
Is there any better way to get the same results?
推荐答案
我不确定如何调用 most_recent_chat_received_from_connected_users
,它是 Conversation
类的实例方法,位于 User $ c的实例上$ c>不会出错,但是我会向会话模型添加自定义查找器:
I'm not sure how you can call most_recent_chat_received_from_connected_users
, which is an instance method of your Conversation
class, on an instance of User
without getting an error, but i would add a custom finder to the Conversation model:
class Conversation < ActiveRecord::Base
# ...
def self.most_recent_for(user_id)
select('DISTINCT ON (sender_id) *').where(reciever_id: user_id).order("sender_id, created_at DESC")
end
# For MySQL you could have used:
#
# def self.most_recent_for(user_id)
# where(reciever_id: user_id).group("sender_id").order("created_at DESC")
# end
# ...
end
现在,您可以在控制器中通过以下方式获得所需的对话:
Now you can get the desired conversations in your controller with:
@conversations = Conversation.most_recent_for(current_user.id)
这篇关于ActiveRecord-从每个组中选择第一条记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!