{"action":"created","issue":{"url":"https://api.github.com/repos/camertron/scuttle-rb/issues/9","repository_url":"https://api.github.com/repos/camertron/scuttle-rb","labels_url":"https://api.github.com/repos/camertron/scuttle-rb/issues/9/labels{/name}","comments_url":"https://api.github.com/repos/camertron/scuttle-rb/issues/9/comments","events_url":"https://api.github.com/repos/camertron/scuttle-rb/issues/9/events","html_url":"https://github.com/camertron/scuttle-rb/issues/9","id":344198095,"node_id":"MDU6SXNzdWUzNDQxOTgwOTU=","number":9,"title":"SQL JOIN with nested subquery/ies","user":{"login":"atrost","id":7213588,"node_id":"MDQ6VXNlcjcyMTM1ODg=","avatar_url":"https://avatars2.githubusercontent.com/u/7213588?v=4","gravatar_id":"","url":"https://api.github.com/users/atrost","html_url":"https://github.com/atrost","followers_url":"https://api.github.com/users/atrost/followers","following_url":"https://api.github.com/users/atrost/following{/other_user}","gists_url":"https://api.github.com/users/atrost/gists{/gist_id}","starred_url":"https://api.github.com/users/atrost/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/atrost/subscriptions","organizations_url":"https://api.github.com/users/atrost/orgs","repos_url":"https://api.github.com/users/atrost/repos","events_url":"https://api.github.com/users/atrost/events{/privacy}","received_events_url":"https://api.github.com/users/atrost/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":1,"created_at":"2018-07-24T20:49:00Z","updated_at":"2018-08-04T05:15:09Z","closed_at":null,"author_association":"NONE","body":"It seems Scuttle cannot handle queries with a subquery inside a JOIN. The following example uses only one level of nesting:   \r\n\r\n```sql\r\nSELECT c.id, c.name, t.status \r\nFROM campaigns c\t\t\t\t\t\t\t\t\t\t\t\t\t\t \r\nLEFT OUTER JOIN (\t\t\t\t\t\t\t\t\t\t\t\t\t        \r\n    SELECT campaigns.id, campaign_approvals.status as status                \t\t\t\t \r\n    FROM campaigns\t\t\t\t\t\t\t\t\t\t\t\t\t \r\n    LEFT OUTER JOIN  campaign_approvals on campaign_approvals.campaign_id = campaigns.id\t\t\t\t\t \r\n    LEFT OUTER JOIN  users on  campaign_approvals.user_id = users.id\t\t\t\t\t\t\t \r\n    WHERE (approval_type = 'publisher_approval' AND (status = 'approved' OR status = 'declined') AND archived IS FALSE)\t\t\r\n ) t on c.id=t.id\t\t\t\t\t\t\t\t\t\t\t\t\t\t \r\nGROUP BY c.id, t.status  \r\n```\r\n\r\nScuttle turns this to:\r\n\r\n```ruby\r\nCampaign.select(\r\n  [\r\n    C.arel_table[:id], C.arel_table[:name], T.arel_table[:status]\r\n  ]\r\n).joins(\r\n  Campaign.arel_table.join(User.arel_table, Arel::Nodes::OuterJoin).on(\r\n    CampaignApproval.arel_table[:user_id].eq(User.arel_table[:id])\r\n  ).join_sources\r\n).group(C.arel_table[:id], T.arel_table[:status])\r\n```\r\nThe nested `SELECT` inside the LEFT OUTER JOIN statement gets ignored..\r\n\r\nScuttle does recognize subquery patterns like this:\r\n\r\n```SQL\r\nSELECT  t.id, t.name, ...  \r\n   FROM ( a.id, a.name,...\r\n\tSELECT ...\r\n        FROM sometable a\r\n  ) t\r\n``` \r\n "},"comment":{"url":"https://api.github.com/repos/camertron/scuttle-rb/issues/comments/410424556","html_url":"https://github.com/camertron/scuttle-rb/issues/9#issuecomment-410424556","issue_url":"https://api.github.com/repos/camertron/scuttle-rb/issues/9","id":410424556,"node_id":"MDEyOklzc3VlQ29tbWVudDQxMDQyNDU1Ng==","user":{"login":"atrost","id":7213588,"node_id":"MDQ6VXNlcjcyMTM1ODg=","avatar_url":"https://avatars2.githubusercontent.com/u/7213588?v=4","gravatar_id":"","url":"https://api.github.com/users/atrost","html_url":"https://github.com/atrost","followers_url":"https://api.github.com/users/atrost/followers","following_url":"https://api.github.com/users/atrost/following{/other_user}","gists_url":"https://api.github.com/users/atrost/gists{/gist_id}","starred_url":"https://api.github.com/users/atrost/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/atrost/subscriptions","organizations_url":"https://api.github.com/users/atrost/orgs","repos_url":"https://api.github.com/users/atrost/repos","events_url":"https://api.github.com/users/atrost/events{/privacy}","received_events_url":"https://api.github.com/users/atrost/received_events","type":"User","site_admin":false},"created_at":"2018-08-04T05:15:09Z","updated_at":"2018-08-04T05:15:09Z","author_association":"NONE","body":"Hey @camertron  \r\nIf you add this functionality to Scuttle I will be happy to try it out.\r\nMy actual query is much more complicated than the one I posted above so I ended up going the   'find_by_sql' route in this case. Not ideal, but it works. Thanks! "}}
{"actor":{"display_login":"atrost"}}