Rails 4 equivalent to SQL "select count"?

Question

I've got a query in SQL:

SELECT Foreign_id, Type, COUNT(Type) FROM events GROUP BY Type, Foreign_id

Foreign_id is, obviously, the foreign key (and primary key of another table), and Type is a descriptor of the event type. This query, when run in the SQL client I am using on my MYSQL server, returns exactly what I am looking for.

I am wondering the most efficient way to run this in a Rails controller (there is a massive number of records). Right now I have no idea how to turn this into a single command in Rails. Here is what I have in my controller:

@foreign = Foreign.all

I then use this in the view:

<% foreign.each do |foreign| %>
  <%= foreign.events.select{|event| event.Type == 'A'}.size %><br />
  <%= foreign.events.select{|event| event.Type == 'B'}.size %>
  ...
<% end %>

I know there is a way more efficient way to get that number. Can anybody help me out?

erapert · Accepted Answer · 2016-03-11 00:10:57Z

1

Rails docs say to do this:

Person.count
# => the total count of all people

Person.count(:age)
# => returns the total count of all people whose age is present in database

Person.count(:all)
# => performs a COUNT(*) (:all is an alias for '*')

Person.distinct.count(:age)
# => counts the number of different age values

Person.group(:city).count
# => { 'Rome' => 5, 'Paris' => 3 }

Article.group(:status, :category).count
# =>  {["draft", "business"]=>10, ["draft", "technology"]=>4, ["published", "business"]=>0, ["published", "technology"]=>2}

Person.select(:age).count
# => counts the number of different age values

So in your case I believe you'd do it like so:

@foreign = Foreign.events.select('Type','A').count()

answered Mar 11, 2016 at 0:10

erapert

1,42312 silver badges15 bronze badges

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

2 Comments

Clinton J Over a year ago

This is very close to exactly what I am looking for, thank you so much. In the example with Article.group(:status, :category).count, what if I only wanted published articles, but still grouped by status and category?

erapert Over a year ago

Then you'd add a scope to your Article model and do this: Article.published.group(:status, :category).count. See this article in the Rails docs about scopes

roob · Accepted Answer · 2016-03-11 01:08:40Z

0

If there are only 2 types, this would be appropriate:

<% @foreigns.each do |foreign| %>
  <%= foreign.events.where( type: 'A' ).count %><br />
  <%= foreign.events.where( type: 'B' ).count %>
<% end %>

answered Mar 11, 2016 at 1:08

roob

1,1367 silver badges11 bronze badges

1 Comment

siegy22 Over a year ago

Wow queries in your view.. MVC? hmm

Collectives™ on Stack Overflow

Rails 4 equivalent to SQL "select count"?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related