CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-5
Crashtor
Crashtor

Reputation: 1279

How to sum parent and child record when grouped?

A Message has the attributes location, like_count, vote_count.

And a Comment belongs_to :message and has the attributes like_count, vote_count

I have managed to figure out how to hash out the location along with the number of votes the Message was given in that particular location.

@votes_by_place = Message.where(
:user_id => @user.id).select(
:location).group(:location).sum('like_count + vote_count')

# => "Total votes and likes of your Messages posted in New York, United States ": 192
# => "Total votes and likes of your Messages posted in Paris, France ": 93

I could just keep it like this and it'd be fine, but I'd really really love it if I could find a way to sum the vote_count and like_count of comments with user_id => @user.id and in a specific @message.location

So that it would become:

# => "Total votes and likes of your Messages and Comments posted in New York, United States ": 192
# => "Total votes and likes of your Messages and Comments posted in Paris, France ": 93

Perhaps it would be easier if I assigned location to Comments as well?

Let me know what you think and any suggestions would be much appreciated!

Upvotes: 1

Views: 115

Answers (2)

akbarbin
akbarbin

Reputation: 5105

You can do this query.

SELECT location, SUM(comments.vote_count + comments.like_count) as total_vote_and_like FROM messages
JOIN comments ON messages.id = comments.message_id
GROUP BY messages.location

For ActiveRecord:

@votes_by_place = Message.select("messages.location, SUM(comments.vote_count + comments.like_count) as total_vote_and_like")joins(:comments).where(user_id => @user.id).group("messages.location")

I hope this will help you.

You can go to this link to get detail http://sqlfiddle.com/#!9/65bb32/6.

Upvotes: 1

m. simon borg
m. simon borg

Reputation: 2575

I'm not sure how to do this in a single query, but with two queries and a little plain Ruby it can be done. Maybe someone else can find a more efficient way.

with_comments = Message.where(user_id: @user.id).
  left_outer_joins(:comments).
  group(:location).
  sum('messages.like_count + messages.vote_count + comments.like_count + comments.vote_count')

This first query adds all of the like_counts and vote_counts from both the messages and comments tables for messages that have associated comments. Using left_outer_joins ensures that the call to group adds hash keys for all message locations including those that don't have associated comments, so all the user's message locations are represented.

without_comments = Message.where(user_id: @user.id).
  left_outer_joins(:comments).
  where(comments: { message_id: nil }).
  group(:location).
  sum('messages.like_count + messages.vote_count')

This second query adds all of the like_counts and vote_counts from only the messages table for messages that don't have associated comments.

totals = with_comments.dup

totals.each_key do |location|
  totals[location] += without_comments[location].to_i
end

dup the first hash and iterate over it, adding up the values of both hashes and converting nil values to 0.

Upvotes: 1

Related Questions