Ruby on Rails Query: Joining tables and selecting multiple columns

Question

There are two models: microposts and users

"microposts" contains 4 columns: name, type, user_id and id

"users" contains 4 columns: name, type, address and id

Microposts belongs to users; users contains many microposts.

I want to find how many microposts each user has but for some reason, I cannot get the query to work properly.

The query I tried is:

User.joins(:microposts).group("users.id").select("users.*, count(microposts.id) as postcount")

This only pulls the attributes from the users table, but excludes the columns from the micropost table and also excludes the count column i created.

For some reason,

User.joins(:microposts).group("users.id").count

works but it only returns the user.id along with the count. I want to pull the other columns as well.

I also tried using "includes" instead of "joins", but that just returns all the columns of the users table and none from the microposts table.

User.includes(:microposts).group("users.id").select("users.*, count(microposts.id) as postcount")

Can anyone help? Thanks!

Grant Birchmeier · Accepted Answer · 2014-09-09 17:37:13Z

4

You need both joins and includes, I think.

User.joins(:microposts).
  includes(:microposts).
  group("users.id").
  select("users.*, count(microposts.id) as postcount")

answered Sep 9, 2014 at 17:37

Grant Birchmeier

18.7k12 gold badges67 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

cweston · Accepted Answer · 2015-09-22 18:13:56Z

0

How do you intend to use it?

If you want to loop over users and display both user attributes and it's microposts you can simply do this:

<% @users.each do |user| %>
  <%= user.microposts.size %>
  <%= other_user_attributes %>

  <%= user.microposts.each do |micropost| %>
    <%= micropost_attributes %>
  <% end %>
<% end %>

P.s. If you also want micropost attributes returned you can do so in your select query. If you add 'microposts.title as micropost_title' it will return the micropost title as well. However, that makes no sense if you group based on the user id since one user can have multiple microposts.

edited Sep 22, 2015 at 18:13

cweston

11.8k21 gold badges86 silver badges109 bronze badges

answered Apr 26, 2014 at 6:21

rails4guides.com

1,4412 gold badges11 silver badges8 bronze badges

1 Comment

user3575214 Over a year ago

Thank you for the response. I want to create an instance variable that has the count of microposts by user id. I need this to create several tables in the views file. Is there a way to modify the query to get the count as a column?

Collectives™ on Stack Overflow

Ruby on Rails Query: Joining tables and selecting multiple columns

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related