I'm trying to do a find that includes a join. It has to be a find because I'm using the results for a Pagination, so I need to limit my query to the number of results I want to display per page. Here's the type of thing I want to do: Employee table <-> Skills_Employees table <-> Skills table Let's say I want to find all the employees who have skill with ID = 3 ('axemanship'). The actual SQL might be: SELECT * FROM employee, employees_skills es WHERE es.employee_id = employee.id AND es.skill_id = 3 In Rails, I figured out that the right way to do that *may* be: employees = Employee.find( :all, :include => :skills, :conditions => ["skill_id = ?",3] ) -- and it works. The SQL in development.log looks correct (if verbose): SELECT employees.`id` AS t0_r0, employees.`title` AS t0_r1, employees.`desc_short` AS t0_r2, employees.`picture_url` AS t0_r3, employees.`contract` AS t0_r4, employees.`available` AS t0_r5, employees.`address` AS t0_r6, employees.`comments` AS t0_r7, employees.`creation_date` AS t0_r9, employees.`last_update` AS t0_r10, employees.`is_featured` AS t0_r11, employees.`is_deleted` AS t0_r12, employees.`is_hidden` AS t0_r13, skills.`id` AS t1_r0, skills.`parent_id` AS t1_r1, skills.`name` AS t1_r2, skills.`desc_short` AS t1_r3 FROM employees LEFT OUTER JOIN skills_employees ON skills_employees.recipe_id = employees.id LEFT OUTER JOIN skills ON skills.id = skills_employees.skill_id WHERE (skill_id = 3) But remember, I want to use this in a Pagination scenario, and that'll require :offset and :limit. Let's add them in as a test. employees = Employee.find( :all, :limit => 10, :offset => 1 :include => :skills, :conditions => ["skill_id = ?",3] ) WHAMMO! I get hit with this: ActiveRecord::StatementInvalid: Mysql::Error: #42S22Unknown column 'skill_id' in 'where clause': SELECT id FROM employees WHERE (skill_id = 3) LIMIT 1, 10 What gives? I'm obviously a newb and may very well have misunderstood the purpose of :include, but the fact that it does exactly what I want it to do (without :limit and :offset) implies otherwise. The moment I add in a :limit, then ActiveRecord seems to forget about the relationship. Am I approaching this the wrong way? TIA! Steve
on 2006-05-21 00:13
on 2006-05-21 08:07
Because of the way SQL joins work, AR has to do a two queries when using :include and :limit/:offset. One to determine which employee ids to select, and then one to select them with the appropriate includes. This has probably already been fully explained somewhere... just google it. The simplest way to achieve what you want is to do the limit/offset of the result set in Ruby: employees = Employee.find( :all, :include => :skills, :conditions => ["skill_id = ?",3] )[1..10] Another option is to use a subselect in the :conditions sql. Perhaps something like: employees = Employee.find( :all, :limit => 1, :offset => 10, :conditions => ['exists (select * from skills_employees left join skills on skills.id = skills_employees.skill_id where skills_employees.employee_id = employees.id and skills.skill_id = 3)'] ) Completely untested, but something like that should work. -Jonathan.
on 2006-05-21 10:32
Well, I would use :condition=>"employees.id IN (select employees_skills.employee_id FROM employees_skills WHERE employees_skills.skill_id = 2", which should allow you to use both includes and limits... I think it is the easiest solution, because it will allow you to use paginate and everything without worries... Tell me if it works! Nauhaie
on 2006-05-22 23:47
Interesting answer! Here is the okey-rig I came up with -- it's probably pretty bad but it avoids selecting more rows than I want (I fear the potential performance hit), and avoids writing very much actual SQL (which still seems So Wrong to me even though DHH says it can be ok). Feel free to flame me for being a Bad Rails Developer for the following: @employee = Employee.find( :all, :conditions => ["se.skill_id = ? AND se.employee_id = employee.id", @ skill.id], :joins => ", skills_employees se", :offset => @employee_pages.current.offset, :limit => @employee_pages.items_per_page, :order => "title" ) The big sin here, of course, is the fact that I'm shimming in the phrase ", skills_employees se" using the :joins option. I'm fooling around with the SQL generation, which I don't think is a very good thing to do. But if there was only a way to do this in ActiveRecord, legitimately, without over-selecting or writing real SQL... (I'd investigate doing a patch for this kind of feature, but I doubt my full understanding of the Ruby Way and the Rails Way, at least at this point.)