Includes and select for joined data

rubyonrails-talk
1

Hi

I have a view that does not really fit that great with my data model and have made longish join for it.

ProjectJob.includes(:job_type,
                                          :job_category,
                                          project_job_rollup: :project_currency, quotations: :quotation_rollups)
                              .select(:job_number,
                                      :header,
                                      :project_id,
                                      :job_type_id,
                                      :job_category_id,
                                      :quotation_group_id,
                                      :serial,
                                      "quotation_rollups.total_quote as quotation_job_total_quote"
                                      ).references(project_job_rollup: :project_currency)
                              .where(project_id: @project).where(quotations:{is_elected:true})
                              .order("job_types.order asc")
                              .order("job_categories.number asc")
                              .order(:serial)

Which gives me all that data that i need in only one query and no n+1 issues.

What I’m not super happy about is that the generated SQL pulls all 115 columns from all the involved tables. I thought that select was there to only give me the columns that I specified?

Is there something about includes and select that I have not understood correctly? Any points to either documentation or other help to understand this is much appreciated.

Thanks in advance Jens

2

I’ve also wanted to trim the SELECT list for #includes and #eager_load. (Both have this same kind of behaviour.)

Took a little time today to experiment with overriding ActiveRecord::Associations::JoinDependency.apply_column_aliases() in order to make this happen. Have not yet figured out the magic spell, but will get back with you when / if I do :slight_smile:

3

Have now figured out a viable solution, and included it in the latest release of a data-related gem I maintain, The Brick. Available in v1.0.95 of this gem.

In order to enable this more selective behaviour for eager loading, you need to add the special column name :_brick_eager_load to your .select(...). This acts as a flag, enabling the filtering of columns as the aliases are being built out. Instead of 115 columns being requested, there would be only the 8 that you want.

Note that because foreign keys are essential to have everything be properly associated, if you omit any of them then this routine automatically adds them, so in your query you would not need to specify :job_type_id or :job_category_id, and perhaps the two others. Ultimately it would be:

ProjectJob.includes(:job_type,
                    :job_category,
                    project_job_rollup: :project_currency,
                    quotations: :quotation_rollups)
          .select(:_brick_eager_load # Turn on filtering of t0_r3 style aliases
                  :job_number,
                  :header,
                  :project_id,
                  :quotation_group_id,
                  :serial,
                  "quotation_rollups.total_quote as quotation_job_total_quote"
                 )
          .references(project_job_rollup: :project_currency)
          .where(project_id: @project).where(quotations: { is_elected:true })
          .order("job_types.order asc",
                 "job_categories.number asc",
                 :serial)

Because foreign keys get added anyway, you may also be able to omit :project_id and :quotation_group_id if they are part of the associations mentioned in your .includes(...) list.

I don’t think that ActiveRecord has a good solution to reference the alias quotation_job_total_quote that you’re using, so you might need to put in just quotation_rollups.total_quote, and refer to it from that nested object. Just a nuance of how eager-loaded objects work.

Eager to know what you think!

4

Thanks for the reply. I will have a look at it start of next week when i rotate back to that project.

Best Jens

5

Sounds great! Looking forward to feedback.

The relevant lines that add this patch can be found here.

1 Like