In a find, can't you use both :include and :limit?

Rails
1

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

2

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

3

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.

4

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.)