Created
September 14, 2018 12:43
-
-
Save tonyfg/26eda4ca1a24e0598bb8adb03b8c6850 to your computer and use it in GitHub Desktop.
Org list #index optimized with .select and .joins
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # | |
| # app/controllers/organizations_controller.rb | |
| # | |
| # old action | |
| def index | |
| @organizations = Organization.all | |
| end | |
| # new action | |
| def index | |
| @organizations = if request.format.html? | |
| Organization.with_hq_names_and_store_count.all | |
| else | |
| Organization.with_hq_and_store_names.all | |
| end | |
| end | |
| # | |
| # app/models/organization.rb | |
| # | |
| scope :with_hq_names_and_store_count, -> do | |
| select(%( | |
| organizations.*, | |
| (SELECT array_agg( | |
| hqs.name || | |
| ':' || | |
| countries.name || | |
| '(' || | |
| (SELECT count(id) FROM stores WHERE stores.hq_id = hqs.id) || | |
| ')' | |
| ) | |
| FROM hqs | |
| JOIN countries ON countries.id = hqs.country_id | |
| WHERE hqs.organization_id = organizations.id | |
| ) AS hq_data | |
| )) | |
| end | |
| scope :with_hq_and_store_names, -> do | |
| select(%( | |
| organizations.*, | |
| (SELECT array_agg(json_build_object( | |
| 'id', hqs.id, | |
| 'name', hqs.name, | |
| 'country_name', countries.name, | |
| 'store_count', (SELECT count(id) FROM stores WHERE stores.hq_id = hqs.id), | |
| 'store_list', (SELECT array_agg(json_build_object('id', id, 'name', name)) FROM stores WHERE stores.hq_id = hqs.id) | |
| )) | |
| FROM hqs | |
| JOIN countries ON countries.id = hqs.country_id | |
| WHERE hqs.organization_id = organizations.id | |
| ) AS hq_data | |
| )) | |
| end | |
| # | |
| # app/views/organizations/organization.json.jbuilder | |
| # | |
| # old view | |
| json.hqs organization.hqs do |hq| | |
| json.extract! hq, :id, :name | |
| json.country hq.country.name | |
| json.store_count hq.stores.count | |
| json.stores hq.stores do |store| | |
| json.extract! store, :id, :name | |
| end | |
| end | |
| # new view | |
| json.hqs organization.hq_data do |hq| | |
| json.extract! hq, 'id', 'name' | |
| json.country hq['country_name'] | |
| json.store_count hq['store_count'] | |
| json.stores hq['store_list'] | |
| end | |
| # | |
| # app/views/organizations/hq_list.html.erb | |
| # | |
| #old view | |
| <% organization.hqs.each do |hq| %> | |
| <%= hq.name %>: <%= hq.country.name %> (<%= hq.stores.count %> stores) <br> | |
| <% end %> | |
| # new view | |
| <% organization.hq_data.each do |hq| %> | |
| <%= hq %> <br> | |
| <% end %> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment