Topic: Foreign Key issue... Need help once again!

I am currently attempting to implement the AJAX-driven table listing as described in http://dev.nozav.org/rails_ajax_table.html .

I am having a bit of an issue, however. I am trying to list jobs. One of the parameters of a job, however, is a customer. Now a customer is part of a location, which is related to a job in the following way:

class Job < ActiveRecord::Base
    belongs_to :location
    has_and_belongs_to_many :crewmembers
    has_many :items
end

class Location < ActiveRecord::Base
    has_many :jobs
end

customer is a column in the location table.

Now I am running into a problem:

In the list action in the job_controller, there is a section:

jobs_per_page = 20
      
      sort = case @params['sort']
          when "customer" then "customer"
          when "customer_reverse" then "customer DESC"
          when "date" then "date"
          when "date_reverse" then "date DESC"
          end

conditions = ["customer LIKE ?", "%#{@params[:query]}%"] unless @params[:query].nil?
@total = Job.count(:conditions => conditions)
@jobs_pages, @jobs = paginate :jobs, :order => sort, :conditions => conditions, :per_page => jobs_per_page


Conditions is fetching a customer name from a query box.
Now, this would work ok, if the customer was part of the job table. Problem is, a customer is part of a location. So, how can I modify the count arguments to fetch from the location table, via a foreign key? I need a final query like, "SELECT COUNT(*) AS count_all FROM jobs, locations WHERE locations.id = jobs.location_id"

In addition, the paginate command fails as well, based on this foreign key problem.

Now, I would assume I need to use a joins argument somewhere. But where, and how? I am going bald from all the hair I've pulled out in the last couple of hours...

P.S. Sorry for always begging for help here...

Last edited by Caydel (2006-07-26 10:27:30)

Check out my Blog.

Re: Foreign Key issue... Need help once again!

Try this:

sort = params[:sort].sub '_reverse', ' DESC' unless params[:sort].nil?
conditions = ["customer LIKE ?", "%#{params[:query]}%"] unless params[:query].nil?
@jobs_pages, @jobs = paginate :jobs, :order => sort, :conditions => conditions, :per_page => 20, :include => :location
@total = @jobs_pages.item_count

The key here is the :include parameter. This actually adds a JOIN. You may need to prefix the sort/condition columns with their table name if both tables have a column with that same name.

Last edited by ryanb (2006-07-26 12:01:29)

Railscasts - Free Ruby on Rails Screencasts

Re: Foreign Key issue... Need help once again!

Thanks, RyanB - Once again you saved my a$$...

Check out my Blog.