Need help with a SQL join in Rails

Question

I have a table Blog belongs to User through user_id.

I'm using thinking sphinx to index the blog, but I only want it to index blogs where the user is currently active (user.status = User::ACTIVE).

I have the code below for creating the index, but I know the 'where' clause is wrong. What should it be?

define_index do
    indexes title
    indexes body

    where "user.status = '#{User::ACTIVE}'"
end

Update: As far as I can see the where method is simply passing SQL code to the database engine. It looks like this should be do-able by passing in the code for a JOIN, but I don't know what enough SQL to create a JOIN statement myself.

Second update: Messing around with SQL it appears the JOIn has to go before the WHERE, so it won't be possible to do this using SQL code, unless anyone knowns better.

Answer 1

I don't know much about sphinx, but just a shot in the dark try..

users.status = '#{User::ACTIVE}'

Answer 2

Perhaps you should try users (table names are plural)

define_index do
    indexes title
    indexes body

    # Use this line, if status is numeric
    where "users.status = #{User::ACTIVE}"

    # ... and this one, if it's a string
    where "users.status = '#{User::ACTIVE}'"
end

After all, you may want to take a look at the manual.

Answer 3

You probably just need a dummy attribute to ensure thinking sphinx joins the blog and user tables. Without knowing the specifics of your schema, try something like:

define_index do
    indexes title
    indexes body
    has user(:id), :as => :user_id

    where "users.status = '#{User::ACTIVE}'"
end

Answer 4

I don't believe the where clause supports this. It's adding to the SQL behind the scenes, but it doesn't have access to the Rails associations. An alternative is to give your blog record a status field as well, that is dependent on the user.

First, add a status field to your blogs table. Then, add this to your user model:

before_save :update_blog_status

protected

def update_blog_status
  self.blog.update_attribute(:status, self.status)
end

That will automatically keep your blog status updated. I didn't know if one user has_many blogs, so if that's the case just adjust this code accordingly.

Then update your blog indexer with the appropriate where clause:

define_index do
  indexes title
  indexes body

  where "status = '#{User::ACTIVE}'"
end

That should be all you need :)