Pluck associated model's attribute in Rails query

Question

In my rails app, collections have many projects, and projects have many steps.

I'd like to grab all the ids of steps in a collection's projects, and I'm wondering if I can do it all in one query.

For example, I know I can do the following

step_ids = []
@collection.projects.each do |project|
    project.steps.each do |step|
       step_ids << step.id
    end
end

But is it possible to do something like the following:

@collection.projects.include(:steps).pluck("step.id") // syntax here is not correct

Inside `Collection` you can also add `has_many :steps, through: :projects` to simplify `collection.steps.pluck(:id)` — Blair Anderson, Mar 23 '18 at 16:50

John · Accepted Answer · 2014-10-24T00:00:30.170

48

Try this:

Step.joins(:project).where(projects: { collection_id: @collection.id }).pluck(:'steps.id')

Note the use of project for the joins, and then projects for the where clause. The first corresponds to the belongs_to relationship, and the latter is the name of the db table.

edit: in the case of a many-to-many relationship between projects and collections, and assuming a project belongs_to a project_collection (and then has_many :collections, through :project_collection)

Step.joins(:project => :project_collection)
    .where(project_collections: { collection_id: @collection.id })
    .pluck(:'steps.id')

edited Oct 24 '14 at 00:00

answered Oct 23 '14 at 23:06

John

2,788
2
18
13

thanks for your help. projects can actually belong to multiple collections, so there is no collection_id attribute for projects. any ideas for how to change your suggestion? – scientiffic Oct 23 '14 at 23:37
is there a `projects_collections` table then? You can extend the join to encompass it....I'll edit my answer with an example. – John Oct 23 '14 at 23:54
1

I know it's late but is it better to use symbol `:'steps.id'` like you do rather than as a string `'steps.id'`? – 244an Feb 26 '17 at 21:33
@244an it's a very minor optimization to create fewer objects. Nowadays, using the `# frozen_string_literal: true` pragma is the more common way to handle this. – x1a4 Mar 26 '18 at 19:18

score 1 · Answer 2 · edited Feb 14 '19 at 17:16

1

Unfortunately, I don't think that we could do it through AR in a single query. You could do a nested query below to retrieve it in two queries to the database:

Step.includes(:projects)
    .where(projects: { id: Projects.includes(:collections)
    .where(collections: { id: @collections.id }).pluck(:id) } )
    .pluck(:id)

edited Feb 14 '19 at 17:16

Kick Buttowski

6,709
13
37
58

answered Oct 24 '14 at 17:28

nikkon226

998
6
11

Pluck associated model's attribute in Rails query

2 Answers2

Linked