# In a find, can't you use both :include and :limit? **URL:** \ **Category:** Rails\ **Created:** [May 21, 2006, 12:13am UTC](https://www.ruby-forum.com/t/in-a-find-cant-you-use-both-include-and-limit/61142 "2006-05-21T00:13:36Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![Steve\_Abatangle](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/5e9695/32.png) [@Steve\_Abatangle](https://www.ruby-forum.com/u/Steve_Abatangle)\ **Post date:** [May 21, 2006, 12:13am UTC](https://www.ruby-forum.com/t/in-a-find-cant-you-use-both-include-and-limit/61142/1 "2006-05-21T00:13:36Z")
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 ---
**Author:** ![Steve\_Abatangle](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/5e9695/32.png) [@Steve\_Abatangle](https://www.ruby-forum.com/u/Steve_Abatangle)\ **Post date:** [May 21, 2006, 10:32am UTC](https://www.ruby-forum.com/t/in-a-find-cant-you-use-both-include-and-limit/61142/2 "2006-05-21T10:32:12Z")
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 ---
**Author:** ![Steve\_Abatangle](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/5e9695/32.png) [@Steve\_Abatangle](https://www.ruby-forum.com/u/Steve_Abatangle)\ **Post date:** [May 21, 2006, 8:07am UTC](https://www.ruby-forum.com/t/in-a-find-cant-you-use-both-include-and-limit/61142/3 "2006-05-21T08:07:58Z")
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. ---
**Author:** ![Steve\_Abatangle](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/5e9695/32.png) [@Steve\_Abatangle](https://www.ruby-forum.com/u/Steve_Abatangle)\ **Post date:** [May 22, 2006, 11:47pm UTC](https://www.ruby-forum.com/t/in-a-find-cant-you-use-both-include-and-limit/61142/4 "2006-05-22T23:47:16Z")
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.)