CODEWITHSUNDEEP
ruby-on-railsactiverecord
Choppen5
Choppen5

Reputation: 5

ActiveRecord vs SQL with sub select

I found this query easier if done with SQL

select Topics.subject, shortcode, (select count(*) from votes where Votes.Topic_Id = Topics.Id ) as votes from Topics
where url like 'http://test.com%'
ORDER BY votes desc;

Using ActiveRecord, I think there should be a more elegant.. or at least possible way to do it. Any suggestions?

I started with this, which worked, but didn't get to the next steps, instead used:

t = Topic.find(:all, :conditions => "url like 'http://test.com%'")

Upvotes: 0

Views: 679

Answers (1)

PinnyM
PinnyM

Reputation: 35533

To get topics with votes:

Topic.where('url like :url', :url => 'http://test.com%').
      joins(:votes).
      select('topics.*, count(votes.id) as votes')

Note that this will only work in MySql. For PostgreSQL, you need to specify the group clause:

Topic.where('url like :url', :url => 'http://test.com%').
      joins(:votes).
      group(Topic.column_names.map{|col| "topics.#{col}"}).
      select('topics.*, count(votes.id) as votes')

Upvotes: 1

Related Questions