has_many , through: relationship count

Question

I was hoping someone could help me with this, been trying to figure it out for a week now, I found a lot of examples, but as I'm new to rails I guess I keep making a mistake somewhere and I just cant find a right solution for my case. So I have:

class Blog < ActiveRecord::Base
   attr_accessible :name, :subject_id, :created_at
   has_many :blogs_messages
   has_many :messages, through: :blogs_messages
end

class Message < ActiveRecord::Base
   attr_accessible :title, :body, :created_at
   has_many :blogs_messages
   has_many :blogs, through: :blogs_messages
end

class BlogsMessages < ActiveRecord::Base
  attr_accessible :message_id, :blog_id
  belongs_to :blog
  belongs_to :message
end

Messages live in different Blogs(like Pink Blog, Green Blog, Maroon Blog etc), and Blogs live in Subjects (Dark Colors, Bright Colors etc) Subjects have many Blogs, but Blogs can belong only to one Subject.

BlogsMessages is the connection between Messages and Blogs what im trying to do is to show: top 3 Blogs (by amount of messages in them) within one Subject

so e.g. when I want to choose Subject Dark Colors it will show me:

    1.Maroon Blog: 46 messages
    2.Grey Blog: 13 messages
    3.Purple Blog: 12 messages

(There are 8 Blogs altogether in Subject Dark Colors.)

Could someone please help me with this, or at least point me in the right direction how to make it all work?

Update:

in my Blogs_controller now i have:

@blogs = Blog.joins(:blogs_messages => :message).select('blogs.*, COUNT(messages.id) AS message_count').group('blog_id').order('COUNT(messages.id) DESC').limit(3)

in my blogs view:

    <% @blogs.each do |blog| %>
      <li><%= blog.name %>:  messages</li>
    <% end %>

MrYoshiji · Accepted Answer · 2013-01-21T16:38:19.173

1

I'm not sure this can work because I can't test it but it may help you:

 Blog.where(subject_id: subject.id)
      .joins(:blogs_messages => :message)
      .select('blogs.*, COUNT(messages.id) AS message_count')
      .group(:blog_id)
      .order('message_count DESC')
      .limit(3)

Also, in the view you could access to the new virtual attribute message_count:

 <% @blogs.each do |blog| %>
   <li><%= blog.name %>: <%= blog.message_count %> messages</li>
 <% end %>

edited Jan 21 '13 at 16:38

answered Jan 21 '13 at 14:36

MrYoshiji

54,334
13
124
117

Thank you,i tried what you suggested. i have that now in the blogs_controller as @blogs = Blog.includes(:messages_blogs => :message)....limit(3) and as you said <% @blogs.each do ..... %> in the view for blog and this is the error i get : BlogMysql2::Error: Unknown column 'message_count' in 'order clause': SELECT DISTINCT `blogs`.id FROM `blogs` LEFT OUTER JOIN `messages_blogs` ON `messages_blogs`.`blog_id` = `blogs`.`id` LEFT OUTER JOIN `messages` ON `messages`.`id` = `messages_blogs`.`message_id` GROUP BY blog.id ORDER BY message_count DESC LIMIT 3 – Rimma Jan 21 '13 at 15:40
Wow that's weird, are you sure you're using the `select` clause with the `COUNT(messages.id) AS message_count` in your query? – MrYoshiji Jan 21 '13 at 15:45
@user1834992 I just updated my answer (changed the order of the joins and select). Let me know if it works now – MrYoshiji Jan 21 '13 at 16:01
@user1834992 try removing the select option and modifying the order option like this: `order('COUNT(messages.id) DESC')` – MrYoshiji Jan 21 '13 at 16:14
tried the new way , when i put joins it gives this error : Association named 'messages' was not found; perhaps you misspelled it?and includes gives an error as before – Rimma Jan 21 '13 at 16:28
try with `.joins(:blogs_messages => :message)` in the join clause – MrYoshiji Jan 21 '13 at 16:38
It WORKS!!!!! the mistake was .group('blog.id') , now that ive changed it to .group('blog_id'), it works :) Thank you very very much MrYoshiji !!! – Rimma Jan 21 '13 at 16:50
You are welcome @user1834992, I was pretty sure to be able to do this kind of request before ;) – MrYoshiji Jan 21 '13 at 16:52

has_many , through: relationship count

1 Answers1

Linked