David McGee David McGee - 1 year ago 46
Ruby Question

Querying between two tables that share an association

New to seqeul and sql in general. I have two tables,

groups
and
resources
, that are associated
many_to_many
and therefore have a
groups_resources
join table. I also have a
task
table that has a
foreign_key :group_id, :groups
and is associated
many_to_one
with groups.

I'm trying to figure out what query to use that will allow my to get the resources that are able to do a task, based on a task's group. Do I have to do a complicated query via the `groups_resources' join table, or is there a more straightforward query/ way of setting up my associations?

Thanks!

Answer Source

I would structure the SQL statement as below. Which would provide you the resources objects that are associated with a specific task id through the join table.

SELECT *
FROM resources r
JOIN groups_resources gr ON gr.resources_id = r.id
JOIN groups g ON gr.group_id = g.id
JOIN task t ON t.id = g.id
WHERE t.id = ?
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download