ActiveRecord: "WHERE IN" SQL statement

Question

I have the following SQL statement: SELECT article_id FROM publications WHERE category_id IN (SELECT id FROM categories WHERE parent_id = 3)

How to convert it to Rails ActiveRecord?

I have tried: Article.find(:all, :conditions => ["publications.category_id IN(?)", 3], :include => [:publications]), it returns empty array.

Models:

class Article < ActiveRecord::Base
  has_many :publications
  has_many :categories, :through => :publications

class Category < ActiveRecord::Base
  belongs_to :parent, :class_name => 'Category'
  has_many :children, :class_name => 'Category', :foreign_key => :parent_id
  has_many :articles, :through => :publications
  has_many :publications


class Publication < ActiveRecord::Base
  belongs_to :article
  belongs_to :category

Answer 1

Is this working ?

Article.find(:all, 
             :conditions => ["publications.category_id IN(SELECT id FROM categories WHERE parent_id = ?)", 3], 
             :include => [:publications])

I would suggest you to use ancestry to represent tree structure in database, there is also a railcast about ancestry