title: Grouping and Ordering through multiple associations in Rails
body: <p>Models:</p>
  
  <pre><code>class BacklinkReport
    has_many :backlinks, :dependent =&gt; :destroy
    has_many :domains, through: :backlinks
    has_many :pages, through: :backlinks
    has_many :domain_scores, through: :domains
    has_many :page_scores, through: :pages
  end
  
  class Backlink
    belongs_to :domain
    has_many :domain_scores, through: :domains
  end
  
  class Domain 
    has_many :pages
    has_many :domain_scores
  end
  
  class DomainScores
    belongs_to :domain
  end
  </code></pre>
  
  <p>Through ActiveRecord, I need a list of the unique domains for a given backlink_report, sorted by their MAX(domain_score.date) and ultimately ordered by their domain_score.pa score.</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>I'm currently breaking the query up into two parts. The first joins the backlinks from a particular report on all Domains and groups by ID to receive a list of unique domains for this particular report. Then I map the list by most recent DomainScore for each domain and sort by domain_score.da. This second query takes quite some time as it runs a DomainScore lookup on each of the 15k or so domains associated with the report. With the associations above, is there a more efficient way to achieve my outcome? I'd also like to be able to limit the number of highest ranking domains I'm requesting vs. fetching the entire site.</p>
  
  <pre><code>domains = Domain.joins(:backlinks).where(:backlinks =&gt; {:backlink_report_id =&gt; report_id}).group('domains.id')
  domains = domains.map{|d| d.domain_scores.sort_by{|x| x['date']}.last}.compact.sort_by(&amp;:da).reverse!
  </code></pre>