What associations should I use to be able to sort search results by an associated model in a rails app?

Question

Hi all,

I'm currently writing an app that pulls all the tweets from twitter with links in them and puts them into our searchable database.

Our database tables look like this:

Tweets
  content
  tweet_id

Links
  url
  title
  count

User
  username
  user_image

We'd like to be able to search through the tweet.content column for a search term and have the results ordered by the link.count

What is the best way to set up the model associations to accomplish this? And what is the best way to perform the search? I've looked into acts_as_ferret, but I don't know how I would sort the results.

Thanks, Daniel Erickson

Answer 1

Maybe something like this

class Tweets < ActiveRecord::Base
  named_scope :order_by_number_of_links,
    :joins => "LEFT OUTER JOIN links ON(tweets.id = links.tweet_id)"
    :group => "tweets.id"
    :order => "count(links.id) DESC"
end

This has not been tested, but hopefully you can now do

Tweets.all(
  :conditions => ["content LIKE ?", params[:query]]
).order_by_number_of_links

A quite possible better (faster) way of doing this is to implement a counter_cache. Rails allows you to have a count in the tweets-table which reflects how many associated objects of type X it has. So, you you add a links_count to your tweets-table Rails should keep that counter up to date on how many links that tweet has. This will make the query easier and faster. Take a look at this Railcast for more info.