Sorting by virtual attribute in Rails

Question

I'm developing with Rails 3.2.8 I want to convert this sorting to Rails scope.

class Doc < ActiveRecord::Base
   has_many :denotations

   def same_sourceid_denotations_count
      denotation_doc_ids = Doc.where(:sourceid => self.sourceid).collect{|doc| doc.id}
      Denotation.select('doc_id').where('doc_id IN (?)', denotation_doc_ids).size
   end
end

class Denotation < ActiveRecord::Base
   belongs_to :doc
end

ans sorting is like below

 @docs = docs.sort{|a, b| b.same_sourceid_relations_count <=> a.same_sourceid_relations_count}

I've wrote scope below, but doesn't work.

scope :same_sourceid_docs_denotations_count
  select('docs.*, docs.sourceid = docs.sourceid AS same_sourceid_doc_ids, COUNT(denotations.doc_id IN (same_sourceid_doc_ids)) AS same_sourceid_docs_denotations_count')
  .group('docs.id')
  .order('same_sourceid_docs_denotations_count DESC')

Got an error.

PG::Error: ERROR:  missing FROM-clause entry for table "denotations"
LINE 1: ...d = docs.sourceid AS same_sourceid_doc_ids, COUNT(denotation...
                                                         ^
: SELECT  docs.*, docs.sourceid = docs.sourceid AS same_sourceid_doc_ids, COUNT(denotations.doc_id IN (same_sourceid_doc_ids)) AS same_sourceid_docs_denotations_count FROM "docs"  WHERE "docs"."sourcedb" = 'PubMed' GROUP BY docs.id ORDER BY same_sourceid_docs_denotations_count DESC LIMIT 10 OFFSET 0
      .group('docs.id')
      .order('same_sourceid_docs_denotations_count DESC')

This sorting step is below

• Find all docs which has same sourceid and get those ids.
• Count denotations which has doc_id in ids above.

• Sort docs by count of denotations above.

  <Doc id: 1, sourceid: 111>
  <Doc id: 2, sourceid: 111>
  <Denotation id: 1, doc_id: 1 >
  <Denotation id: 2, doc_id: 2 >
  <Denotation id: 3, doc_id: 2 >
  => count of denotations related to sourceid 111 should be 3

  <Doc id: 3, sourceid: 222>
  <Denotation id: 4, doc_id: 3 >
  => count of denotations related to sourceid 222 should be 1

Any ideas?

Answer 1

Looks like you're missing a JOIN clause to bring in the denotations table. Try something like this (noting the use of joins(...)):

scope :same_sourceid_docs_denotations_count,
  select("docs.*, count(denotations.id) AS denotations_count")
  .joins("INNER JOIN denotations ON denotations.sourceid = docs.sourceid")
  .group("docs.id")
  .order("denotations_count DESC")

The ActiveRecord Query Interface guide also has some good details on how to use joins(...).