Rails的ActiveRecord的：总和，最大值和加入 [英] Rails activerecord : sum, max and joins

问题描述

我有两个型号用户和职位。一个用户可以投票和意见后

I have two models users and posts. An user can votes and views a post

#users
  id
  name

#posts
  id
  count_votes
  count_views
  users_id
  created_at
  updated_at

我想谁在过去24小时内获得最多选票和观点在他的职位用户。意见和表决最大的一笔赢了。

I want the user who received the most votes and views on his posts from the last 24 hours. The biggest sum of views and votes win.

我的尝试 我有这个SQL查询，这是很好的，但我想有带票的最大用户，这个人给我的所有用户，我不知道如何添加count_views

WHAT I TRIED I have this SQL query, it's good but I would like to have the user with the max of votes, this one give me all users and I don't know how to add count_views

select u.name as "Name", sum(p.count_votes)
from posts p 
inner join users u on p.user_id = u.id 
where p.created_at >= DATE_SUB(NOW(), INTERVAL 1 day)
group by user_id;

ActiveRecord的版本

ActiveRecord version

Post.select("users.id, sum(posts.count_votes) as nb_votes")
.joins(:user).where("posts.created_at >= ?", 1.day.ago.beginning_of_day)
.group("posts.user_id")

# Results on IRB
=> #<ActiveRecord::Relation [#<Post id: 1>, #<Post id: 3>]> 

我如何能结合这两个数额的款项和最大？有没有办法有一个ActiveRecord code或只有原始的SQL？

How can I combine a sum and a max on these two sums ? Is there a way to have an activerecord code or only raw SQL ?

推荐答案

您当前的查询确实对用户分组。所以，你会得到一个记录输出每个用户。通过限制产量以增票+意见总数仅1记录和排序，你可以得到最高的用户。

You current query does a grouping on user. So you would get one record for each user in the output. By limiting the output to just 1 record and ordering by votes+views total count, you can get the top user.

原始的SQL：

select u.id, sum(p.count_votes + p.count_views) total
from posts p 
inner join users u on p.user_id = u.id 
where p.created_at >= DATE_SUB(NOW(), INTERVAL 1 day)
group by u.id
order by total DESC
limit 1 ;

ActiveRecord的版本：从用户模式启动，所以你会得到的输出，而不是帖子对象的用户对象，就像你刚才提到的问题

ActiveRecord version: Start from your User model, so you will get the user object in the output instead of Post object, like you have mentioned in the question.

User.select("users.id, sum(posts.count_votes + posts.count_views) as nb_votes")
.joins(:post).where("posts.created_at >= ?", 1.day.ago.beginning_of_day)
.group("posts.user_id").order("nb_votes DESC").limit(1)

这篇关于Rails的ActiveRecord的：总和，最大值和加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！