CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
NARKOZ
NARKOZ

Reputation: 27901

ActiveRecord: "WHERE IN" SQL statement

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

Upvotes: 3

Views: 2174

Answers (2)

ply
ply

Reputation: 1141

What do your models look like?

You'll need the associations set up correctly in your Article, Category, and Publication models.

It sounds like you have the relevant foreign keys set up in your tables, but your models don't know to look for the association

Upvotes: 0

Adrien Coquio
Adrien Coquio

Reputation: 4930

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

Upvotes: 3

Related Questions