Rails activerecord: sum, max and join

Question

I have two models usersand posts. User can vote and view message

#users
  id
  name

#posts
  id
  count_votes
  count_views
  users_id
  created_at
  updated_at

I want the user who received the most votes and views on their posts in the last 24 hours. The largest amount of views and votes.

WHAT I SAID I have this SQL query, it’s good, but I would like to have a user with the maximum number of votes, this gives me all the 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 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>]> 

How can I combine the amount and maximum for these two amounts? Is there a way to have activerecord code or just raw SQL?

Answer 1

. , . 1 + , .

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: , Post, .

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)

Answer 2

ActiveRecord , , User, :

  sql = %Q(SELECT * FROM users 
  WHERE id = (SELECT users_id FROM posts
    WHERE DATE(created_at) = CURDATE()
    ORDER BY count_votes DESC, count_views DESC
    LIMIT 1
  ) 
  LIMIT 1)

  ActiveRecord::Base.connection.execute(sql)