{"action":"created","issue":{"url":"https://api.github.com/repos/elixir-ecto/ecto/issues/1010","repository_url":"https://api.github.com/repos/elixir-ecto/ecto","labels_url":"https://api.github.com/repos/elixir-ecto/ecto/issues/1010/labels{/name}","comments_url":"https://api.github.com/repos/elixir-ecto/ecto/issues/1010/comments","events_url":"https://api.github.com/repos/elixir-ecto/ecto/issues/1010/events","html_url":"https://github.com/elixir-ecto/ecto/issues/1010","id":111063048,"number":1010,"title":"Support Non-Correlated Subqueries in Joins","user":{"login":"geofflane","id":6378,"avatar_url":"https://avatars.githubusercontent.com/u/6378?v=3","gravatar_id":"","url":"https://api.github.com/users/geofflane","html_url":"https://github.com/geofflane","followers_url":"https://api.github.com/users/geofflane/followers","following_url":"https://api.github.com/users/geofflane/following{/other_user}","gists_url":"https://api.github.com/users/geofflane/gists{/gist_id}","starred_url":"https://api.github.com/users/geofflane/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/geofflane/subscriptions","organizations_url":"https://api.github.com/users/geofflane/orgs","repos_url":"https://api.github.com/users/geofflane/repos","events_url":"https://api.github.com/users/geofflane/events{/privacy}","received_events_url":"https://api.github.com/users/geofflane/received_events","type":"User","site_admin":false},"labels":[{"id":43220257,"url":"https://api.github.com/repos/elixir-ecto/ecto/labels/Kind:Bug","name":"Kind:Bug","color":"fc2929","default":false},{"id":169894591,"url":"https://api.github.com/repos/elixir-ecto/ecto/labels/Level:Advanced","name":"Level:Advanced","color":"f7c6c7","default":false}],"state":"closed","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":9,"created_at":"2015-10-12T21:54:01Z","updated_at":"2016-11-09T19:40:49Z","closed_at":"2015-12-08T19:02:55Z","body":"The current support for joining to a fragment is limited and generates SQL that does something like\n`INNER JOIN (SELECT * FROM Foo WHERE something = outer.something) AS f1 ON TRUE`\nThat limits the sub-selected to correlated subqueries where you can reference the join condition in the WHERE clause of the subquery to something in the outer query scope. This _might_ always be possible, but it's not the only way to write a synthetic join.\n\nI expected to be able to generate a query like:\n`INNER JOIN (SELECT * FROM Foo) AS f1 ON f1.something = outer.something`\n\nThe failing test (in Postgres) for that is something like the following:\n\n```\n  test \"join with fragment subquery\" do\n    query = Model\n            |> join(:inner, [p], q in fragment(\"SELECT * FROM model2 AS m2 WHERE m2.field = ?\", ^10), p.id == q.id)\n            |> select([p], {p.id, ^0})\n            |> where([p], p.id > 0 and p.id < ^100)\n            |> normalize\n    assert SQL.all(query) ==\n           ~s{SELECT m0.\"id\", $1 FROM \"model\" AS m0 INNER JOIN } <>\n           ~s{(SELECT * FROM model2 AS m2 WHERE m2.id = m0.\"x\" AND m2.field = $2) AS f1 ON m0.\"id\" = f1.\"id\" } <>\n           ~s{WHERE ((m0.\"id\" > 0) AND (m0.\"id\" < $3))}\n  end\n```\n"},"comment":{"url":"https://api.github.com/repos/elixir-ecto/ecto/issues/comments/259506160","html_url":"https://github.com/elixir-ecto/ecto/issues/1010#issuecomment-259506160","issue_url":"https://api.github.com/repos/elixir-ecto/ecto/issues/1010","id":259506160,"user":{"login":"geofflane","id":6378,"avatar_url":"https://avatars.githubusercontent.com/u/6378?v=3","gravatar_id":"","url":"https://api.github.com/users/geofflane","html_url":"https://github.com/geofflane","followers_url":"https://api.github.com/users/geofflane/followers","following_url":"https://api.github.com/users/geofflane/following{/other_user}","gists_url":"https://api.github.com/users/geofflane/gists{/gist_id}","starred_url":"https://api.github.com/users/geofflane/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/geofflane/subscriptions","organizations_url":"https://api.github.com/users/geofflane/orgs","repos_url":"https://api.github.com/users/geofflane/repos","events_url":"https://api.github.com/users/geofflane/events{/privacy}","received_events_url":"https://api.github.com/users/geofflane/received_events","type":"User","site_admin":false},"created_at":"2016-11-09T19:40:49Z","updated_at":"2016-11-09T19:40:49Z","body":"@madshargreave Something like:\r\n\r\n```\r\n from st in query,\r\n join: a in assoc(st, :agency),\r\n join: t in assoc(st, :trip),\r\n join: es in fragment(\"SELECT * FROM effective_services(?, ?)\", ^start_date, ^end_date), on: es.service_id == t.service_id,\r\n```"}}
{"actor":{"display_login":"geofflane"}}