CODEWITHSUNDEEP
ruby-on-railsactiverecord
Jeff Rossi
Jeff Rossi

Reputation: 189

has_one :through => :belongs_to produces bad query

Existing associations

class Job < ActiveRecord::Base
  belongs_to :client
  belongs_to :contact
  belongs_to :location
  has_one :geofence, :through => :location
end

class Client < ActiveRecord::Base
  has_many :contacts
  has_many :locations
end

class Contact < ActiveRecord::Base
  belongs_to :client
end

class Location < ActiveRecord::Base
  belongs_to :client
  belongs_to :geofence
end

class Geofence < ActiveRecord::Base
  has_many :locations
end

In trying to find jobs searching fields from various tables i do something like this

@jobs = Job.find(:all, :joins => [:client,:contact,:location,:geofence], :conditions => ['geofences.address like ?',"%#{params[:term]}%"])

which produces the following error

ActiveRecord::StatementInvalid (Mysql::Error: Unknown column 'geofences_jobs_join.location_id'

from the following query

SELECT `jobs`.* FROM `jobs`   INNER JOIN `clients` ON `clients`.id = `jobs`.client_id  INNER JOIN `contacts` ON `contacts`.id = `jobs`.contact_id  INNER JOIN `locations` ON `locations`.id = `jobs`.location_id  INNER JOIN `locations` geofences_jobs_join ON (`jobs`.`id` = `geofences_jobs_join`.`location_id`)  INNER JOIN `geofences` ON (`geofences`.`id` = `geofences_jobs_join`.`geofence_id`)  WHERE ...

Is there a way to fix this with either a different association in the jobs model or in the joins portion of my query?

I could get what I need using find_by_sql but i'd like to avoid that if possible.

Upvotes: 3

Views: 3106

Answers (1)

HargrimmTheBleak
HargrimmTheBleak

Reputation: 2167

I think that in order to use a has_one :through association, your Job model should declare a has_one :location association, not the other way around, see this example.

That said, you can either try to redefine this association and move the location_id foreign key from the jobs table to job_id in locations, or try this query:

@jobs = Job.find(:all, :joins => [:client, :contact, { :location => :geofence }], :conditions => ['geofences.address like ?',"%#{params[:term]}%"])

In the latter case you'll have to remove the has_one :through.

Upvotes: 2

Related Questions