ActiveRecord select add count

Question

In my ActiveRecord query, I need to provide this info in the select method:

(SELECT count(*) from likes where likes.spentit_id = spentits.id) as like_count,
(SELECT count(*) from comments where comments.spentit_id = spentits.id) as comment_count

Of course, I pass pass these two as string to the .select() part, but I am wondering what's the proper/alternative way of doing this?

Here's the complete query I am trying to call:

SELECT DISTINCT
    spentits.*,
    username,
    (SELECT count(*) from likes where likes.spentit_id = spentits.id) as like_count,
    (SELECT count(*) from comments where comments.spentit_id = spentits.id) as comment_count,
    (SELECT count(*) from wishlist_items where wishlist_items.spentit_id = spentits.id) as wishlist_count,
    (case when likes.id is null then 0 else 1 end) as is_liked_by_me,
    (case when wishlist_items.id is null then 0 else 1 end) as is_wishlisted_by_me,
    (case when comments.id is null then 0 else 1 end) as is_commented_by_me
FROM spentits
LEFT JOIN users ON users.id = spentits.user_id
LEFT JOIN likes ON likes.user_id = 9 AND likes.spentit_id = spentits.id
LEFT JOIN wishlist_items ON wishlist_items.user_id = 9 AND wishlist_items.spentit_id = spentits.id
LEFT JOIN comments ON comments.user_id = 9 AND comments.spentit_id = spentits.id
WHERE spentits.user_id IN
    (SELECT follows.following_id
     FROM follows
     WHERE follows.follower_id = 9 AND follows.accepted = 1)
ORDER BY id DESC LIMIT 15 OFFSET 0;

All the tables here have their respective ActiveRecord object. Just really confused how to convert this query into 'activerecord'/rails way with writing least amount of SQL. The '9' user_id is suppose to be a parameter.

Update: Ok so here's what I did inmean time, it's much better than raw SQL statement, but it still looks ugly to me:

class Spentit < ActiveRecord::Base
  belongs_to :user
  has_many :likes
  has_many :wishlist_items
  has_many :comments

  scope :include_author_info, lambda {
    joins([:user]).
    select("username").
    select("users.photo_uri as user_photo_uri").
    select("spentits.*")
  }

  scope :include_counts, lambda {
    select("(SELECT count(*) from likes where likes.spentit_id = spentits.id) as like_count").
    select("(SELECT count(*) from comments where comments.spentit_id = spentits.id) as comment_count").
    select("(SELECT count(*) from wishlist_items where wishlist_items.spentit_id = spentits.id) as wishlist_items_count").
    select("spentits.*")
  }
end

Using these scope methods, I can do:

Spentit.where(:id => 7520).include_counts.include_author_info.customize_for_user(45)

A bit about the classes. A User has many Spentits. A Spentit has many comments, likes and comments.

Answer 1

Ok, you're "doing it wrong", a little bit. Rather than

  scope :include_counts, lambda {
    select("(SELECT count(*) from likes where likes.spentit_id = spentits.id) as like_count").
    select("(SELECT count(*) from comments where comments.spentit_id = spentits.id) as comment_count").
    select("(SELECT count(*) from wishlist_items where wishlist_items.spentit_id = spentits.id) as wishlist_items_count").
    select("spentits.*")
  }

do

Spentit.find(7520).likes.count
Spentit.find(7520).wishlist_items.count
Spentit.find(7520).comments.count

Instead of

  scope :include_author_info, lambda {
    joins([:user]).
    select("username").
    select("users.photo_uri as user_photo_uri").
    select("spentits.*")
  }

do

Spentit.find(7520).user.username
Spentit.find(7520).user.photo_uri

Also, you can define scopes within the referenced models, and use those:

class Follow < ActiveRecord::Base
  belongs_to :follower, :class_name => "User"
  belongs_to :following, :class_name => "User"

  scope :accepted, lambda{ where(:accepted => 1) }
end

Spentits.where(:user => Follow.where(:follower => User.find(9)).accepted)

Now, maybe you also do:

class Spentit
  def to_hash
    hash = self.attributes
    hash[:like_count] = self.like.count
    # ...
  end
end

but you don't need to do anything fancy to get those counts "under normal circumstances", you already have them.

Note, however, you'll probably also want to do eager loading, which you can apparently make as part of the default scope, or you'll do a lot more queries than you need.