How to Join a Table and Count Records in Rails 3

How to join a table and count records in Rails 3?

To answer my own question:

Collection.joins(:coins).group("coins.collection_id").having("count(coins.id) > 2")

Hat tip to KJF who asked this similar question and to krakover for answering it.

Count total number of records in Rails join table

The best way is to just create a model called UsersDepartment and do a nice and easy query on that.

count = UsersDepartment.count

You can query the table directly however with exec_query which gives you an ActiveRecord::Result object to play with.

result = ActiveRecord::Base.connection.exec_query('select count(*) as count from users_departments')
count = result[0]['count']

Rails: get count of association through join table

Fact.
  group(:id, :name).
  select(:id, name).
  left_joins(:feedbacks).
  select("COUNT(feedbacks.id) FILTER (WHERE score = 0) AS number_of_zero").
  select("COUNT(feedbacks.id) FILTER (WHERE score = 1) AS number_of_one").
  select("COUNT(feedbacks.id) FILTER (WHERE score = 2) AS number_of_two")

You need to left_joins so that facts with 0 feedback are also returned (with 0 for each count column).

As stated by Rohan, you need to add has_many :feedbacks in fact.rb

There are more complicated solutions that adapt to any number of possible scores, but in this case that would be overzealous.

Edit: For SQLite (I think it supports correlated subqueries ...)

Fact.
  select(:id, name).
  select("(SELECT COUNT(*) FROM feedbacks WHERE score = 0 AND fact_id = facts.id) AS number_of_zero").
  select("(SELECT COUNT(*) FROM feedbacks WHERE score = 1 AND fact_id = facts.id) AS number_of_one").
  select("(SELECT COUNT(*) FROM feedbacks WHERE score = 2 AND fact_id = facts.id) AS number_of_two")

Rails: Joining tables and grouping by count

Try this:

Tag.select('tags.*, COUNT(taggings.id) AS tagging_count').
    joins(:taggings).group('tags.id').
    order('tagging_count DESC').
    limit(10).pluck(:name)

Rails get count of association through join table

You can use chain select and group to aggregate the count of books for each category. Your books_per_category method may look like this:

def books_per_category
  categories.select('categories.id, categories.name, count(books.id) as count')
            .group('categories.id, categories.name').map do |c|
    {
      name: c.name,
      count: c.count
    }
  end
end

This will produce the following SQL query:

SELECT categories.id, categories.name, count(books.id) as count 
  FROM "categories" 
  INNER JOIN "books_categories" ON "categories"."id" = "books_categories"."category_id" 
  INNER JOIN "books" ON "books_categories"."book_id" = "books"."id" 
  WHERE "books"."store_id" = 1 
  GROUP BY categories.id, categories.name

Rails3: left join aggregate count - how to calculate?

Firstly as a couple of points on style and rails functions to help you with building DB queries.

1) You're better writing this as a scope rather than a method i.e.

scope attendance_counts, select("users.*, sum(attended) as attendance_count").joins(:registrations).group('registrations.user_id').order('attendance_count DESC')

2) It's better not to call all/find/first on the query you've built up until you actually need it (i.e. in the controller or view). That way if you decide to implement action / fragment caching later on the DB query won't get called if the cached action / fragment is served to the user.

3) Rails has a series of functions to help with aggregating db data. for example if you only wanted a user's id and the sum of attended you could use something like the following code:

Registrations.group(:user_id).sum(:attended)

Other functions include count, avg, minimum, maximum

Finally in answer to your question, rails will create an attribute for you to access the value of any custom fields you have in the select part of your query. e.g.

@users = User.attendance_counts
@users[0].attendance_count # The attendance count for the first user returned by the query

rails 3 getting the count of the records that have more than one associate records (has_many)

active record supports 'having' as a method.
So you could do your query this way:

Account.joins(:users).select('accounts.id').group('accounts.id').having('count(users.id) > 1')

Rails 3 Counting Records by Date

Use this as a template if you wish

def self.period_count_array(from = (Date.today-1.month).beginning_of_day,to = Date.today.end_of_day)
  where(created_at: from..to).group('date(created_at)').count
end

This will return you a hash with dates as key and the count as value. (Rails 3.2.x)

Rails ActiveRecord sort by count of join table associations

Try the following:

@resources = Resouce.select("resources.*, COUNT(votes.id) vote_count")
                    .joins(:votes)
                    .where(language_id: "ruby")
                    .group("resources.id")
                    .order("vote_count DESC")

@resources.each { |r| puts "#{r.whatever}  #{r.vote_count}" }

To include resources with 0 votes, use an outer join. If the example below doesn't work as is you'll have to alter the joins statement to join across the correct relations.

@resources = Resource.select("resources.*, COUNT(votes.id) vote_count")
                     .joins("LEFT OUTER JOIN votes ON votes.votable_id = resources.id AND votes.votable_type = 'Resource'")
                     .where(language_id: "ruby")
                     .group("resources.id")
                     .order("vote_count DESC")

How do I count the number of records that have one or more associated object?

Since all you want is the Propertys with Photos then an INNER JOIN is all you need.

Property.joins(:photos) 

That is it. If you want a scope then

class Property < ActiveRecord::Base
  scope :with_photos, -> {joins(:photos)} 
end 

To get the count using rails 3.2

Property.with_photos.count(distinct: true)  

You could also use: in rails 3.2

Property.count(joins: :photos, distinct: true) 

ActiveRecord::Calculations#count Doc

This will execute

SELECT 
  COUNT(DISTINCT properties.id) 
FROM 
  properties
  INNER JOIN photos ON photos.property_id = properties.id

---

Related Topics