Rails - Mar 2006 - Newbie: using find like a sql join query

I have a working Rails app with several related tables, but can''t find 
an answer to this question in the Dave Thomas Rails book.  Imagine for 
example:

table departments with columns: id, department_name
table employees with columns: id, department_id, employee_name

and of course the employees table has a constraint foreign key 
(department_id) references departments(id)

So this is really basic stuff, I have this working fine in my app.  I 
have in the model: departments has_many :employees, and employee 
belongs_to :department

What I want to do is use the find method to do a query like: "find all 
the employees that belong to the department with department_name = 
''Engineering''.

How do I do this with find?

Thanks!
G

-- 
Posted via http://www.ruby-forum.com/.

Hi,

If everything is set up ok, then you can get find department object,
then get the array of employee objects from the department object.

engineering = Department.find_by_name("Engineering")
engineers = engineering.employees

I had problems in more complex queries though, but I use find_by_sql
instead.

Gaudi Mi wrote:
> I have a working Rails app with several related tables, but can''t
find
> an answer to this question in the Dave Thomas Rails book.  Imagine for 
> example:
>
> table departments with columns: id, department_name
> table employees with columns: id, department_id, employee_name
>
> and of course the employees table has a constraint foreign key 
> (department_id) references departments(id)
>
> So this is really basic stuff, I have this working fine in my app.  I 
> have in the model: departments has_many :employees, and employee 
> belongs_to :department
>
> What I want to do is use the find method to do a query like: "find all
> the employees that belong to the department with department_name = 
> ''Engineering''.
>
> How do I do this with find?
>
> Thanks!
> G
>
>   

-- 
Sau Sheong

http://www.saush.com
http://read.saush.com
http://jaccal.sourceforge.net

<-- Begin overly-long response to simple problem -->
Hey, I was playing around with the example above, and found, at least in 
my case, that you could shorten that to just one line and it works fine 
as well, as so:

engineers = Department.find_by_name("Engineering").employees

That should work just as well, let us know. I just tested it with a 
similar setup

complaints = Operators.find_by_name("John Q. Public").opcomplaints

where opcomplaints belongs_to operators and operators has_many 
opcomplaints (hopefully, in the real world, our operators will have 
*few* complaints, but the boss wants to keep track of it anyway). Worked 
like a charm, in any case. Good luck!

Keep in mind, also, that the find_by_whatever methods are dynamically 
generated, so if you have a field in your table that keeps track of 
which employees are going bald, and you call it ''balding'',
then you
could get your list of employees that are going bald by using:

Employees.find_by_balding("Yes")

You can even search multiple fields.. let''s say you''re keeping
track of
which employees are getting a little big around the midle, and you call 
that field ''pudgy''. Then you can use:

Employees.find_by_balding_and_pudgy("Yes")

It goes without saying that the first record returned here would, of 
course, be my own. :(

In any case, I''m sure you get the idea. :) Dynamic Finders are one of 
the coolest Ruby features, I think. And of course, there''s little or 
nothing I could find about them in the official API doc, I had to go 
here to find out about them: http://www.billkatz.com/agile_web_rails

Anyway, enjoy your coding!

-Daniel

Chang Sau Sheong wrote:
> Hi,
> 
> If everything is set up ok, then you can get find department object,
> then get the array of employee objects from the department object.
> 
> engineering = Department.find_by_name("Engineering")
> engineers = engineering.employees
> 
-- 
Posted via http://www.ruby-forum.com/.

Bugfix to my post above, ''cause I''m a doofus:

The examples above will, of course, only find the one record. Everything 
shown above also works with ''find_all_by_whatever'', so to
actually get
the *list* of balding, pudgy employees, it would be:

Employees.find_all_by_balding_and_pudgy("Yes")

In fact, just go look at the ''Dynamic attribute-based finders''
section
on this page: http://railsmanual.org/class/ActiveRecord::Base

It will make you happy. :)

-Daniel

-- 
Posted via http://www.ruby-forum.com/.