{"action":"created","issue":{"url":"https://api.github.com/repos/discourse/discourse/issues/4405","repository_url":"https://api.github.com/repos/discourse/discourse","labels_url":"https://api.github.com/repos/discourse/discourse/issues/4405/labels{/name}","comments_url":"https://api.github.com/repos/discourse/discourse/issues/4405/comments","events_url":"https://api.github.com/repos/discourse/discourse/issues/4405/events","html_url":"https://github.com/discourse/discourse/pull/4405","id":172163965,"number":4405,"title":"Don't join on tags unnecessarily when matching all tags","user":{"login":"gdpelican","id":750477,"avatar_url":"https://avatars.githubusercontent.com/u/750477?v=3","gravatar_id":"","url":"https://api.github.com/users/gdpelican","html_url":"https://github.com/gdpelican","followers_url":"https://api.github.com/users/gdpelican/followers","following_url":"https://api.github.com/users/gdpelican/following{/other_user}","gists_url":"https://api.github.com/users/gdpelican/gists{/gist_id}","starred_url":"https://api.github.com/users/gdpelican/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/gdpelican/subscriptions","organizations_url":"https://api.github.com/users/gdpelican/orgs","repos_url":"https://api.github.com/users/gdpelican/repos","events_url":"https://api.github.com/users/gdpelican/events{/privacy}","received_events_url":"https://api.github.com/users/gdpelican/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":1,"created_at":"2016-08-19T15:58:03Z","updated_at":"2016-08-19T15:58:09Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/discourse/discourse/pulls/4405","html_url":"https://github.com/discourse/discourse/pull/4405","diff_url":"https://github.com/discourse/discourse/pull/4405.diff","patch_url":"https://github.com/discourse/discourse/pull/4405.patch"},"body":"This is... a bit of a weird one.\r\n\r\nWhen we join on tags before doing the sequential join for matching all tags, then this call in `topic_query#311:latest_topics`:\r\n\r\n```\r\nresult = remove_muted_categories(result, @user, exclude: options[:category])\r\n```\r\n\r\nreturns 15 results instead of the proper 30.\r\n\r\nHere's the SQL for the existing behaviour:\r\n```\r\nSELECT <stuff> FROM \"topics\"\r\nINNER JOIN \"topic_tags\" ON \"topic_tags\".\"topic_id\" = \"topics\".\"id\"\r\nINNER JOIN \"tags\" ON \"tags\".\"id\" = \"topic_tags\".\"tag_id\"\r\nLEFT OUTER JOIN \"categories\" ON \"categories\".\"id\" = \"topics\".\"category_id\"\r\nLEFT OUTER JOIN topic_users AS tu ON (topics.id = tu.topic_id AND tu.user_id = 1)\r\nINNER JOIN topic_tags t0 ON t0.topic_id = topics.id AND t0.tag_id = 2\r\nINNER JOIN topic_tags t1 ON t1.topic_id = topics.id AND t1.tag_id = 1\r\nWHERE (topics.archetype <> 'private_message') \r\n  AND (topics.deleted_at IS NULL)\r\n  AND (COALESCE(tu.notification_level,1) > 0)\r\n  AND (\r\n          NOT EXISTS (\r\n            SELECT 1\r\n              FROM category_users cu\r\n             WHERE cu.user_id = 1\r\n               AND cu.category_id = topics.category_id\r\n               AND cu.notification_level = 0\r\n               AND cu.category_id <> -1\r\n               AND (tu.notification_level IS NULL OR tu.notification_level < 2)\r\n        ))\r\nLIMIT 30\r\n```\r\n\r\nAnd for the new (working) behaviour\r\n```\r\nSELECT <stuff> FROM \"topics\"\r\nLEFT OUTER JOIN \"categories\" ON \"categories\".\"id\" = \"topics\".\"category_id\"\r\nLEFT OUTER JOIN topic_users AS tu ON (topics.id = tu.topic_id AND tu.user_id = 1)\r\nINNER JOIN topic_tags t0 ON t0.topic_id = topics.id AND t0.tag_id = 2\r\nINNER JOIN topic_tags t1 ON t1.topic_id = topics.id AND t1.tag_id = 1\r\nWHERE (topics.archetype <> 'private_message') \r\n  AND (topics.deleted_at IS NULL) AND (COALESCE(tu.notification_level,1) > 0)\r\n  AND (\r\n          NOT EXISTS (\r\n            SELECT 1\r\n              FROM category_users cu\r\n             WHERE cu.user_id = 1\r\n               AND cu.category_id = topics.category_id\r\n               AND cu.notification_level = 0\r\n               AND cu.category_id <> -1\r\n               AND (tu.notification_level IS NULL OR tu.notification_level < 2)\r\n          ))\r\nLIMIT 30\r\n```\r\n\r\nThe only difference being that double inner join at the beginning. @nlalonde Any idea why this would be the case? "},"comment":{"url":"https://api.github.com/repos/discourse/discourse/issues/comments/241058219","html_url":"https://github.com/discourse/discourse/pull/4405#issuecomment-241058219","issue_url":"https://api.github.com/repos/discourse/discourse/issues/4405","id":241058219,"user":{"login":"discoursebot","id":3698800,"avatar_url":"https://avatars.githubusercontent.com/u/3698800?v=3","gravatar_id":"","url":"https://api.github.com/users/discoursebot","html_url":"https://github.com/discoursebot","followers_url":"https://api.github.com/users/discoursebot/followers","following_url":"https://api.github.com/users/discoursebot/following{/other_user}","gists_url":"https://api.github.com/users/discoursebot/gists{/gist_id}","starred_url":"https://api.github.com/users/discoursebot/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/discoursebot/subscriptions","organizations_url":"https://api.github.com/users/discoursebot/orgs","repos_url":"https://api.github.com/users/discoursebot/repos","events_url":"https://api.github.com/users/discoursebot/events{/privacy}","received_events_url":"https://api.github.com/users/discoursebot/received_events","type":"User","site_admin":false},"created_at":"2016-08-19T15:58:09Z","updated_at":"2016-08-19T15:58:09Z","body":"You've signed the CLA, gdpelican. Thank you! This pull request is ready for review."}}
{"actor":{"display_login":"discoursebot"}}