Search code examples
sqlrubysequel

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!

Solution

  • 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 r.*
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 = ?