Forum: Ruby on Rails In a find, can't you use both :include and :limit ?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Steve Abatangle (Guest)
on 2006-05-21 02:13
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
Jonathan V. (Guest)
on 2006-05-21 10:07
(Received via mailing list)
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.
Nauhaie (Guest)
on 2006-05-21 12: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
sabbat (Guest)
on 2006-05-23 01:47
(Received via mailing list)
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.)
This topic is locked and can not be replied to.