Steve Abatangle
2006-May-20 22:13 UTC
[Rails] In a find, can''t you use both :include and :limit ?
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
--
Posted via http://www.ruby-forum.com/.
Jonathan Viney
2006-May-21 06:06 UTC
[Rails] In a find, can''t you use both :include and :limit ?
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 5/21/06, Steve Abatangle <abbatangelo@gmail.com> wrote:> 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 > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
Nauhaie
2006-May-21 08:32 UTC
[Rails] Re: In a find, can''t you use both :include and :limit ?
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 -- Posted via http://www.ruby-forum.com/.
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.)
On 5/20/06, Jonathan Viney <jonathan.viney@gmail.com>
wrote:>
> 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 5/21/06, Steve Abatangle <abbatangelo@gmail.com> wrote:
> > 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
> >
> > --
> > Posted via http://www.ruby-forum.com/.
> > _______________________________________________
> > Rails mailing list
> > Rails@lists.rubyonrails.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
--
===============================sabbat <sabbat@area51.org>
Wisdom is really just applied hindsight.
--
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060522/f382fb52/attachment.html