Activerecord Association Select Counts for Included Records

ActiveRecord Association select counts for included records

Try this:

class User

  has_one :tickets_count, :class_name => 'Ticket', 
    :select => "user_id, tickets_count",
    :finder_sql =>   '
        SELECT b.user_id, COUNT(*) tickets_count
        FROM   tickets b
        WHERE  b.user_id = #{id}
        GROUP BY b.user_id
     '
end

Edit:

It looks like the has_one association does not support the finder_sql option.

You can easily achieve what you want by using a combination of scope/class methods

class User < ActiveRecord::Base

  def self.include_ticket_counts
    joins(
     %{
       LEFT OUTER JOIN (
         SELECT b.user_id, COUNT(*) tickets_count
         FROM   tickets b
         GROUP BY b.user_id
       ) a ON a.user_id = users.id
     }
    ).select("users.*, COALESCE(a.tickets_count, 0) AS tickets_count")
  end    
end

Now

User.include_ticket_counts.where(:id => [1,2,3]).each do |user|
  p user.tickets_count 
end

This solution has performance implications if you have millions of rows in the tickets table. You should consider filtering the JOIN result set by providing WHERE to the inner query.

Activerecord query to include count of associations

Rails has a built in method, that has to be set to true when declaring associations like has_many, and also, you must add a column to the database.

set counter_cache: true when you declare the has_many association

add #{table_name}_count to the database and it will be incremented and decremented automatically, so you can select this column directly when you query for the first five users.

More info on:

http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html

http://railscasts.com/episodes/23-counter-cache-column

Find all records which have a count of an association greater than zero

joins uses an inner join by default so using Project.joins(:vacancies) will in effect only return projects that have an associated vacancy.

UPDATE:

As pointed out by @mackskatz in the comment, without a group clause, the code above will return duplicate projects for projects with more than one vacancies. To remove the duplicates, use

Project.joins(:vacancies).group('projects.id')

UPDATE:

As pointed out by @Tolsee, you can also use distinct.

Project.joins(:vacancies).distinct

As an example

[10] pry(main)> Comment.distinct.pluck :article_id
=> [43, 34, 45, 55, 17, 19, 1, 3, 4, 18, 44, 5, 13, 22, 16, 6, 53]
[11] pry(main)> _.size
=> 17
[12] pry(main)> Article.joins(:comments).size
=> 45
[13] pry(main)> Article.joins(:comments).distinct.size
=> 17
[14] pry(main)> Article.joins(:comments).distinct.to_sql
=> "SELECT DISTINCT \"articles\".* FROM \"articles\" INNER JOIN \"comments\" ON \"comments\".\"article_id\" = \"articles\".\"id\""

ActiveRecord - find records that its association count is 0

You're using joins, which is INNER JOIN, whereas what you need is an OUTER JOIN -
includes:

SlideGroup.includes(:surveys).group("slide_groups.id, surveys.id").having("count(surveys.id) = ?",0)

A bit cleaner query:

SlideGroup.includes(:surveys).where(surveys: { id: nil })

Rails Active Record count associations

I went ahead and added the column using this: ryan.mcgeary.org/2016/02/05/… and it worked really well and I can call: Post.unscoped.order('posts.favorites_count desc').limit(10)

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

How to count a has_many relationship using active record?

I assume you are having an has_many :through association between User and Item through UserItem and you need to show the list in a view of all users with the related item count.

This is one option.

In controller (ordered by item_count):

@users = User.joins(:user_items).group('user_items.user_id').select('users.*, COUNT(*) as item_count').order('item_count DESC')

In view (very basic):

<% @users.each do |user| %>
  <p><%= user.name %> | <%= user.item_count %></p>
<% end %>

In my comment there is a syntax error, for counting the items of @user, if id = @user.id:

UserItem.where(user_id: id).count

---

**Edit1:** To show also users with no items.

Option one, add to the above code the following, to fetch users with no items:

@users_without_items = User.includes(:items).where(items: {id: nil})

<% @users_without_items.each do |user| %>
  <p><%= user.name %> | 0</p>
<% end %>

Or fetch all at once (not ordered, fires a lot of queries):

@user = User.all

<% @user.each do |user| %>
  <p><%= user %> | <%= user.categories.count %></p>
<% end %>

---

**Edit2:** Fetching a hash of `{user_id => # of items per user}`

One option can be list all user and get the count from the hash by it's keys:

@user_items_count = UserItem.group(:user_id).count
@users = User.all

<% @users.each do |user| %>
  <p><%= user.name %> | <%= @user_items_count[user.id] || 0 %></p>
<% end %>

---

**Tested in `Rails` which uses `ActiveRecord` and `erb`.**

Counting associated records in Rails

The includes method will not do a join in all cases but rather batch-fetch the association for performance reasons (see Rails :include vs. :joins).

What you need to do is a joins and you where almost on the correct path but got the group clause a bit wrong:

Post.select("posts.*, COUNT(comments.id) as comment_count").joins("LEFT OUTER JOIN comments ON (comments.post_id = posts.id)").group("posts.id")

Note that this solution has the benefit or actually returning Post objects (using .count() returns a Hash on my Rails 3.2) so you can loop through actual post objects in your view and also access the property comment_count.

Rails - Active Record: Find all records which have a count on has_many association with certain attributes

One solution is to use rails nested queries

User.joins(:identities).where(id: Identity.select(:user_id).unconfirmed).group("users.id").having( 'count(user_id) = 1')

And here's the SQL generated by the query

SELECT "users".* FROM "users"
INNER JOIN "identities" ON "identities"."user_id" = "users"."id"
WHERE "users"."id" IN (SELECT "identities"."user_id" FROM "identities"  WHERE "identities"."confirmed" = 'f')
GROUP BY users.id HAVING count(user_id) = 1

I still don't think this is the most efficient way. While I'm able to generate only one SQL query (meaning only one network call to the db), I'm still have to do two scans: one scan on the USERS table and one scan on the IDENTITIES table. This can be optimized by indexing the identities.confirmed column but this still doesn't solve the two full scans problem.

For those who understand the query plan here it is:

     QUERY PLAN
-------------------------------------------------------------------------------------------
 HashAggregate  (cost=32.96..33.09 rows=10 width=3149)
   Filter: (count(identities.user_id) = 1)
   ->  Hash Semi Join  (cost=21.59..32.91 rows=10 width=3149)
         Hash Cond: (identities.user_id = identities_1.user_id)
         ->  Hash Join  (cost=10.45..21.61 rows=20 width=3149)
               Hash Cond: (identities.user_id = users.id)
               ->  Seq Scan on identities  (cost=0.00..10.70 rows=70 width=4)
               ->  Hash  (cost=10.20..10.20 rows=20 width=3145)
                     ->  Seq Scan on users  (cost=0.00..10.20 rows=20 width=3145)
         ->  Hash  (cost=10.70..10.70 rows=35 width=4)
               ->  Seq Scan on identities identities_1  (cost=0.00..10.70 rows=35 width=4)
                     Filter: (NOT confirmed)
(12 rows)

---

Related Topics