Queries using :join in a many to many relationship using :has_many :through

Queries using :join in a many to many relationship using :has_many :through

Date: 2011.09.29 | Tags: ruby,ruby on rails

Say you have have a model name Resource and one name Category and you want to specify a many-to-many relationship using Ruby on Rails. For this I’ve chosen the :has_many :trough directive, causing me to create a join model which I call Categorization and respective table.

The trouble I encountered was when I wanted to fetch resources or categories who only have values in the join table (using :include would do just that, include them in my query results), so I had to use :join. First, of all, I’ll show you my models and then present my problem.

Resource model
```
class Resource < ActiveRecord::Base
  has_many :categorizations, :dependent => :destroy
  has_many :categories, :through => :categorizations
end
```
Join model Categorization
```
class Categorization < ActiveRecord::Base
  belongs_to :resource
  belongs_to :category
end
```
Subjects model
```
class Category < ActiveRecord::Base
  has_many :categorizations
  has_many :resources, :through=>:categorizations
end
```
Ok, the issue I encountered is when I create a query where I want to fetch resources that are mentioned in the join table categorizations. The error was due to my confusion between the :joins and :include parameter in a find query, which I hope to clarify here and may be to use to others.

The follwing gives an error, “ActiveRecord::StatementInvalid: Mysql::Error: Unknown table ‘resources’: SELECT resources.* FROM resources categorizations WHERE (categorizations.category_id = 1).”
```
@resources = Resource.find(:all, :select=>"resources.*", :joins=>:categorizations, :conditions=>["categorizations.category_id = ?", 1])
```
This is because :joins expects actual text that defines the join relationship. In other words, it’s not smart enough of to know what kind of relationship you want as when using :include (which in this case would only work with :categories rather than :categorizations). You may want a LEFT JOIN or a RIGHT JOIN or OUTER JOIN, etc.

Therefore, to fix this, I simply had to define the relationship, which in this case is a simple JOIN (i.e. INNER JOIN).
```
@resources = Resource.find(:all, :select=>"resources.*", :joins=>"JOIN categorizations ON resources.id = categorizations.resource_id", :conditions=>["categorizations.resource_id = resources.id AND categorizations.category_id = ?", 1])
```