{"action":"created","comment":{"url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/pulls/comments/43559490","id":43559490,"diff_hunk":"@@ -0,0 +1,60 @@\n+class Store < ActiveRecord::Base\n+  validates_presence_of :name, :sells_beer, :zagat_rating\n+  validates_numericality_of :zagat_rating # 1-5 starts, I imagine\n+  belongs_to :city\n+\n+  has_many :store_tacos, table_name: \"stores_tacos\"\n+  has_many :tacos, through: :store_tacos\n+\n+  has_many :store_salsas, table_name: \"stores_salsas\"\n+  has_many :salsas, through: :store_salsas\n+\n+  def self.find_stores_with_taco_and_salsas selected_taco_ids = [], selected_salsa_ids = []\n+\n+    selected_salsa_ids.map!(&:to_i).sort\n+    selected_taco_ids.map!(&:to_i).sort\n+\n+    # Our data set isn't huge, so we can do a single query and just grab what we need.\n+    # Notes: 1. I'm not using .select(\"stores.name as store_name, cities.name as city_name\") anymore because as soon as the .references for eager loading happens, it takes precedence over a limited select.\n+    # 2. This query grabs our store records along with their salsas and tacos, and we will do a simple map/reduce on the small data set.\n+    #\n+    #  SQL (416.9ms)  SELECT DISTINCT \"stores\".\"id\" AS t0_r0, \"stores\".\"name\" AS t0_r1, \"stores\".\"city_id\" AS t0_r2, \"stores\".\"sells_beer\" AS t0_r3, \"stores\".\"zagat_rating\" AS t0_r4, \"stores_salsas\".\"id\" AS t1_r0, \"stores_salsas\".\"store_id\" AS t1_r1, \"stores_salsas\".\"salsa_id\" AS t1_r2, \"stores_salsas\".\"spiciness\" AS t1_r3, \"stores_tacos\".\"id\" AS t2_r0, \"stores_tacos\".\"store_id\" AS t2_r1, \"stores_tacos\".\"taco_id\" AS t2_r2, \"stores_tacos\".\"price\" AS t2_r3 FROM \"stores\" INNER JOIN \"cities\" ON \"cities\".\"id\" = \"stores\".\"city_id\" LEFT OUTER JOIN \"stores_salsas\" ON \"stores_salsas\".\"store_id\" = \"stores\".\"id\" LEFT OUTER JOIN \"stores_tacos\" ON \"stores_tacos\".\"store_id\" = \"stores\".\"id\"\n+    #\n+    stores = self.joins(:city).includes(:store_salsas, :store_tacos).references(:store_salsas, :store_tacos, :city).distinct(\"stores.name\").to_a","path":"app/models/store.rb","position":23,"original_position":23,"commit_id":"35c4e35e2afcdb3fcb50c9c01ddd68026db447ba","original_commit_id":"35c4e35e2afcdb3fcb50c9c01ddd68026db447ba","user":{"login":"bryanthompson","id":3762,"avatar_url":"https://avatars.githubusercontent.com/u/3762?v=3","gravatar_id":"","url":"https://api.github.com/users/bryanthompson","html_url":"https://github.com/bryanthompson","followers_url":"https://api.github.com/users/bryanthompson/followers","following_url":"https://api.github.com/users/bryanthompson/following{/other_user}","gists_url":"https://api.github.com/users/bryanthompson/gists{/gist_id}","starred_url":"https://api.github.com/users/bryanthompson/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/bryanthompson/subscriptions","organizations_url":"https://api.github.com/users/bryanthompson/orgs","repos_url":"https://api.github.com/users/bryanthompson/repos","events_url":"https://api.github.com/users/bryanthompson/events{/privacy}","received_events_url":"https://api.github.com/users/bryanthompson/received_events","type":"User","site_admin":false},"body":"Im going to keep playing with it. id like to demonstrate the answer i think you're  looking for no matter what happens.  \n\n\n\n—\nSent from Mailbox\n\nOn Fri, Oct 30, 2015 at 3:29 PM, Brad Urani <notifications@github.com>\nwrote:\n\n>> +\n>> +  has_many :store_salsas, table_name: \"stores_salsas\"\n>> +  has_many :salsas, through: :store_salsas\n>> +\n>> +  def self.find_stores_with_taco_and_salsas selected_taco_ids = [], selected_salsa_ids = []\n>> +\n>> +    selected_salsa_ids.map!(&:to_i).sort\n>> +    selected_taco_ids.map!(&:to_i).sort\n>> +\n>> +    # Our data set isn't huge, so we can do a single query and just grab what we need.\n>> +    # Notes: 1. I'm not using .select(\"stores.name as store_name, cities.name as city_name\") anymore because as soon as the .references for eager loading happens, it takes precedence over a limited select.\n>> +    # 2. This query grabs our store records along with their salsas and tacos, and we will do a simple map/reduce on the small data set.\n>> +    #\n>> +    #  SQL (416.9ms)  SELECT DISTINCT \"stores\".\"id\" AS t0_r0, \"stores\".\"name\" AS t0_r1, \"stores\".\"city_id\" AS t0_r2, \"stores\".\"sells_beer\" AS t0_r3, \"stores\".\"zagat_rating\" AS t0_r4, \"stores_salsas\".\"id\" AS t1_r0, \"stores_salsas\".\"store_id\" AS t1_r1, \"stores_salsas\".\"salsa_id\" AS t1_r2, \"stores_salsas\".\"spiciness\" AS t1_r3, \"stores_tacos\".\"id\" AS t2_r0, \"stores_tacos\".\"store_id\" AS t2_r1, \"stores_tacos\".\"taco_id\" AS t2_r2, \"stores_tacos\".\"price\" AS t2_r3 FROM \"stores\" INNER JOIN \"cities\" ON \"cities\".\"id\" = \"stores\".\"city_id\" LEFT OUTER JOIN \"stores_salsas\" ON \"stores_salsas\".\"store_id\" = \"stores\".\"id\" LEFT OUTER JOIN \"stores_tacos\" ON \"stores_tacos\".\"store_id\" = \"stores\".\"id\"\n>> +    #\n>> +    stores = self.joins(:city).includes(:store_salsas, :store_tacos).references(:store_salsas, :store_tacos, :city).distinct(\"stores.name\").to_a\n> As you probably realized, you're pulling in way more data than you need, which is probably why it takes 416 ms. That being said, you did get it in one query, which isn't easy. It's deceptively hard this one. It's not the best answer but you did a good job :smile: \n> ---\n> Reply to this email directly or view it on GitHub:\n> https://github.com/procore/tacocopter-sql-workshop/pull/2/files#r43559176","created_at":"2015-10-30T22:35:35Z","updated_at":"2015-10-30T22:35:35Z","html_url":"https://github.com/procore/tacocopter-sql-workshop/pull/2#discussion_r43559490","pull_request_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/pulls/2","_links":{"self":{"href":"https://api.github.com/repos/procore/tacocopter-sql-workshop/pulls/comments/43559490"},"html":{"href":"https://github.com/procore/tacocopter-sql-workshop/pull/2#discussion_r43559490"},"pull_request":{"href":"https://api.github.com/repos/procore/tacocopter-sql-workshop/pulls/2"}}},"pull_request":{"url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/pulls/2","id":49327402,"html_url":"https://github.com/procore/tacocopter-sql-workshop/pull/2","diff_url":"https://github.com/procore/tacocopter-sql-workshop/pull/2.diff","patch_url":"https://github.com/procore/tacocopter-sql-workshop/pull/2.patch","issue_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/issues/2","number":2,"state":"open","locked":false,"title":"Bryan's solution.  ","user":{"login":"bryanthompson","id":3762,"avatar_url":"https://avatars.githubusercontent.com/u/3762?v=3","gravatar_id":"","url":"https://api.github.com/users/bryanthompson","html_url":"https://github.com/bryanthompson","followers_url":"https://api.github.com/users/bryanthompson/followers","following_url":"https://api.github.com/users/bryanthompson/following{/other_user}","gists_url":"https://api.github.com/users/bryanthompson/gists{/gist_id}","starred_url":"https://api.github.com/users/bryanthompson/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/bryanthompson/subscriptions","organizations_url":"https://api.github.com/users/bryanthompson/orgs","repos_url":"https://api.github.com/users/bryanthompson/repos","events_url":"https://api.github.com/users/bryanthompson/events{/privacy}","received_events_url":"https://api.github.com/users/bryanthompson/received_events","type":"User","site_admin":false},"body":"Basically, we do a quick query to get our rather small data set and then reduce it using simple array methods.","created_at":"2015-10-30T21:59:32Z","updated_at":"2015-10-30T22:35:35Z","closed_at":null,"merged_at":null,"merge_commit_sha":"efddd56175a0a96b3968f30a18a6b6a5ab3e7747","assignee":null,"milestone":null,"commits_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/pulls/2/commits","review_comments_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/pulls/2/comments","review_comment_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/pulls/comments{/number}","comments_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/issues/2/comments","statuses_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/statuses/35c4e35e2afcdb3fcb50c9c01ddd68026db447ba","head":{"label":"bryanthompson:bryan-thompson","ref":"bryan-thompson","sha":"35c4e35e2afcdb3fcb50c9c01ddd68026db447ba","user":{"login":"bryanthompson","id":3762,"avatar_url":"https://avatars.githubusercontent.com/u/3762?v=3","gravatar_id":"","url":"https://api.github.com/users/bryanthompson","html_url":"https://github.com/bryanthompson","followers_url":"https://api.github.com/users/bryanthompson/followers","following_url":"https://api.github.com/users/bryanthompson/following{/other_user}","gists_url":"https://api.github.com/users/bryanthompson/gists{/gist_id}","starred_url":"https://api.github.com/users/bryanthompson/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/bryanthompson/subscriptions","organizations_url":"https://api.github.com/users/bryanthompson/orgs","repos_url":"https://api.github.com/users/bryanthompson/repos","events_url":"https://api.github.com/users/bryanthompson/events{/privacy}","received_events_url":"https://api.github.com/users/bryanthompson/received_events","type":"User","site_admin":false},"repo":{"id":45278619,"name":"tacocopter-sql-workshop","full_name":"bryanthompson/tacocopter-sql-workshop","owner":{"login":"bryanthompson","id":3762,"avatar_url":"https://avatars.githubusercontent.com/u/3762?v=3","gravatar_id":"","url":"https://api.github.com/users/bryanthompson","html_url":"https://github.com/bryanthompson","followers_url":"https://api.github.com/users/bryanthompson/followers","following_url":"https://api.github.com/users/bryanthompson/following{/other_user}","gists_url":"https://api.github.com/users/bryanthompson/gists{/gist_id}","starred_url":"https://api.github.com/users/bryanthompson/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/bryanthompson/subscriptions","organizations_url":"https://api.github.com/users/bryanthompson/orgs","repos_url":"https://api.github.com/users/bryanthompson/repos","events_url":"https://api.github.com/users/bryanthompson/events{/privacy}","received_events_url":"https://api.github.com/users/bryanthompson/received_events","type":"User","site_admin":false},"private":false,"html_url":"https://github.com/bryanthompson/tacocopter-sql-workshop","description":"Complete SQL querying workshop","fork":true,"url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop","forks_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/forks","keys_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/keys{/key_id}","collaborators_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/teams","hooks_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/hooks","issue_events_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/issues/events{/number}","events_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/events","assignees_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/assignees{/user}","branches_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/branches{/branch}","tags_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/tags","blobs_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/git/refs{/sha}","trees_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/git/trees{/sha}","statuses_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/statuses/{sha}","languages_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/languages","stargazers_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/stargazers","contributors_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/contributors","subscribers_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/subscribers","subscription_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/subscription","commits_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/commits{/sha}","git_commits_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/git/commits{/sha}","comments_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/comments{/number}","issue_comment_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/issues/comments{/number}","contents_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/contents/{+path}","compare_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/compare/{base}...{head}","merges_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/merges","archive_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/downloads","issues_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/issues{/number}","pulls_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/pulls{/number}","milestones_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/milestones{/number}","notifications_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/labels{/name}","releases_url":"https://api.github.com/repos/bryanthompson/tacocopter-sql-workshop/releases{/id}","created_at":"2015-10-30T21:57:23Z","updated_at":"2015-10-30T21:57:24Z","pushed_at":"2015-10-30T21:58:39Z","git_url":"git://github.com/bryanthompson/tacocopter-sql-workshop.git","ssh_url":"git@github.com:bryanthompson/tacocopter-sql-workshop.git","clone_url":"https://github.com/bryanthompson/tacocopter-sql-workshop.git","svn_url":"https://github.com/bryanthompson/tacocopter-sql-workshop","homepage":"http://tacocopter.herokuapp.com","size":1250,"stargazers_count":0,"watchers_count":0,"language":"Ruby","has_issues":false,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":0,"mirror_url":null,"open_issues_count":0,"forks":0,"open_issues":0,"watchers":0,"default_branch":"master"}},"base":{"label":"procore:master","ref":"master","sha":"6b83737312d0a77145dcf24f9e088a59ce7f6c85","user":{"login":"procore","id":145387,"avatar_url":"https://avatars.githubusercontent.com/u/145387?v=3","gravatar_id":"","url":"https://api.github.com/users/procore","html_url":"https://github.com/procore","followers_url":"https://api.github.com/users/procore/followers","following_url":"https://api.github.com/users/procore/following{/other_user}","gists_url":"https://api.github.com/users/procore/gists{/gist_id}","starred_url":"https://api.github.com/users/procore/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/procore/subscriptions","organizations_url":"https://api.github.com/users/procore/orgs","repos_url":"https://api.github.com/users/procore/repos","events_url":"https://api.github.com/users/procore/events{/privacy}","received_events_url":"https://api.github.com/users/procore/received_events","type":"Organization","site_admin":false},"repo":{"id":44413355,"name":"tacocopter-sql-workshop","full_name":"procore/tacocopter-sql-workshop","owner":{"login":"procore","id":145387,"avatar_url":"https://avatars.githubusercontent.com/u/145387?v=3","gravatar_id":"","url":"https://api.github.com/users/procore","html_url":"https://github.com/procore","followers_url":"https://api.github.com/users/procore/followers","following_url":"https://api.github.com/users/procore/following{/other_user}","gists_url":"https://api.github.com/users/procore/gists{/gist_id}","starred_url":"https://api.github.com/users/procore/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/procore/subscriptions","organizations_url":"https://api.github.com/users/procore/orgs","repos_url":"https://api.github.com/users/procore/repos","events_url":"https://api.github.com/users/procore/events{/privacy}","received_events_url":"https://api.github.com/users/procore/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/procore/tacocopter-sql-workshop","description":"Complete SQL querying workshop","fork":true,"url":"https://api.github.com/repos/procore/tacocopter-sql-workshop","forks_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/forks","keys_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/keys{/key_id}","collaborators_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/teams","hooks_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/hooks","issue_events_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/issues/events{/number}","events_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/events","assignees_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/assignees{/user}","branches_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/branches{/branch}","tags_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/tags","blobs_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/git/refs{/sha}","trees_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/git/trees{/sha}","statuses_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/statuses/{sha}","languages_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/languages","stargazers_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/stargazers","contributors_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/contributors","subscribers_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/subscribers","subscription_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/subscription","commits_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/commits{/sha}","git_commits_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/git/commits{/sha}","comments_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/comments{/number}","issue_comment_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/issues/comments{/number}","contents_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/contents/{+path}","compare_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/compare/{base}...{head}","merges_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/merges","archive_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/downloads","issues_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/issues{/number}","pulls_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/pulls{/number}","milestones_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/milestones{/number}","notifications_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/labels{/name}","releases_url":"https://api.github.com/repos/procore/tacocopter-sql-workshop/releases{/id}","created_at":"2015-10-16T22:13:45Z","updated_at":"2015-10-16T22:13:46Z","pushed_at":"2015-10-30T21:59:32Z","git_url":"git://github.com/procore/tacocopter-sql-workshop.git","ssh_url":"git@github.com:procore/tacocopter-sql-workshop.git","clone_url":"https://github.com/procore/tacocopter-sql-workshop.git","svn_url":"https://github.com/procore/tacocopter-sql-workshop","homepage":"http://tacocopter.herokuapp.com","size":1250,"stargazers_count":0,"watchers_count":0,"language":"Ruby","has_issues":false,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":1,"mirror_url":null,"open_issues_count":1,"forks":1,"open_issues":1,"watchers":0,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/procore/tacocopter-sql-workshop/pulls/2"},"html":{"href":"https://github.com/procore/tacocopter-sql-workshop/pull/2"},"issue":{"href":"https://api.github.com/repos/procore/tacocopter-sql-workshop/issues/2"},"comments":{"href":"https://api.github.com/repos/procore/tacocopter-sql-workshop/issues/2/comments"},"review_comments":{"href":"https://api.github.com/repos/procore/tacocopter-sql-workshop/pulls/2/comments"},"review_comment":{"href":"https://api.github.com/repos/procore/tacocopter-sql-workshop/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/procore/tacocopter-sql-workshop/pulls/2/commits"},"statuses":{"href":"https://api.github.com/repos/procore/tacocopter-sql-workshop/statuses/35c4e35e2afcdb3fcb50c9c01ddd68026db447ba"}}}}