{"action":"created","issue":{"url":"https://api.github.com/repos/morphismtech/squeal/issues/31","repository_url":"https://api.github.com/repos/morphismtech/squeal","labels_url":"https://api.github.com/repos/morphismtech/squeal/issues/31/labels{/name}","comments_url":"https://api.github.com/repos/morphismtech/squeal/issues/31/comments","events_url":"https://api.github.com/repos/morphismtech/squeal/issues/31/events","html_url":"https://github.com/morphismtech/squeal/issues/31","id":327491668,"node_id":"MDU6SXNzdWUzMjc0OTE2Njg=","number":31,"title":"Express join with potential null values?","user":{"login":"prasmussen","id":720405,"node_id":"MDQ6VXNlcjcyMDQwNQ==","avatar_url":"https://avatars3.githubusercontent.com/u/720405?v=4","gravatar_id":"","url":"https://api.github.com/users/prasmussen","html_url":"https://github.com/prasmussen","followers_url":"https://api.github.com/users/prasmussen/followers","following_url":"https://api.github.com/users/prasmussen/following{/other_user}","gists_url":"https://api.github.com/users/prasmussen/gists{/gist_id}","starred_url":"https://api.github.com/users/prasmussen/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/prasmussen/subscriptions","organizations_url":"https://api.github.com/users/prasmussen/orgs","repos_url":"https://api.github.com/users/prasmussen/repos","events_url":"https://api.github.com/users/prasmussen/events{/privacy}","received_events_url":"https://api.github.com/users/prasmussen/received_events","type":"User","site_admin":false},"labels":[{"id":678278511,"node_id":"MDU6TGFiZWw2NzgyNzg1MTE=","url":"https://api.github.com/repos/morphismtech/squeal/labels/question","name":"question","color":"cc317c","default":true}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":12,"created_at":"2018-05-29T21:19:41Z","updated_at":"2018-08-03T13:58:42Z","closed_at":null,"author_association":"CONTRIBUTOR","body":"Sorry to use this as a support forum, but I'm struggling to express a join.\r\n\r\nThe query i'm trying to do is:\r\n```\r\nselect snippet.snippet_id from snippets as snippet\r\nleft outer join authors as author on snippet.snippet_id = author.snippet_id\r\nleft outer join \"users\" as \"user\" on author.user_id = \"user\".user_id;\r\n```\r\n\r\nThe tricky part is that second join depends on values in the first join (and the first join might produce null values).\r\n\r\n\r\nI have the following (simplified) schema:\r\n```\r\ntype Schema =\r\n  '[ \"snippets\" :::\r\n       '[ \"pk_snippets\" ::: 'PrimaryKey '[\"snippet_id\"]\r\n        ] :=>\r\n       '[ \"snippet_id\" ::: 'Def :=> 'NotNull 'PGtext\r\n        ]\r\n   , \"authors\" :::\r\n       '[ \"pk_authors\" ::: 'PrimaryKey '[\"author_id\"]\r\n        , \"fk_snippet_id\" ::: 'ForeignKey '[\"snippet_id\"] \"snippets\" '[\"snippet_id\"]\r\n        , \"fk_user_id\" ::: 'ForeignKey '[\"user_id\"] \"users\" '[\"user_id\"]\r\n        ] :=>\r\n       '[ \"author_id\" ::: 'Def :=> 'NotNull 'PGtext\r\n        , \"snippet_id\" ::: 'Def :=> 'NotNull 'PGtext\r\n        , \"user_id\" ::: 'Def :=> 'NotNull 'PGtext\r\n        ]\r\n   , \"users\" :::\r\n       '[ \"pk_users\" ::: 'PrimaryKey '[\"user_id\"]\r\n        ] :=>\r\n       '[ \"user_id\" ::: 'Def :=> 'NotNull 'PGtext\r\n        ]\r\n   ]\r\n```\r\n\r\nAnd this is one of the variants I have tried to build the query:\r\n```\r\nlist :: Query Schema '[] '[ \"snippetId\" ::: 'NotNull 'PGtext ]\r\nlist =\r\n    select\r\n        (  #snippet ! #snippet_id `As` #snippetId\r\n        :* Nil\r\n        )\r\n        ( from (table (#snippets `As` #snippet)\r\n        & leftOuterJoin (table (#authors `As` #author)) (#snippet ! #snippet_id .== #author ! #snippet_id)\r\n        & leftOuterJoin (table (#users `As` #user)) (#author ! #user_id .== #user ! #user_id)\r\n        ))\r\n```\r\n\r\nWhich results in:\r\n```\r\n    • No instance for (Has \"user_id\" '[] ('NotNull 'PGtext))\r\n        arising from a use of ‘!’\r\n    • In the first argument of ‘(.==)’, namely ‘#author ! #user_id’\r\n      In the second argument of ‘leftOuterJoin’, namely\r\n        ‘(#author ! #user_id .== #user ! #user_id)’\r\n      In the second argument of ‘(&)’, namely\r\n        ‘leftOuterJoin\r\n           (table (#users `As` #user))\r\n           (#author ! #user_id .== #user ! #user_id)’\r\n   |\r\n70 |         & leftOuterJoin (table (#users `As` #user)) (#author ! #user_id .== #user ! #user_id)\r\n   |                                                      ^^^^^^^^^^^^^^^^^^\r\n```\r\n\r\nIs it possible to solve this with some coalesce magic?\r\n"},"comment":{"url":"https://api.github.com/repos/morphismtech/squeal/issues/comments/410262353","html_url":"https://github.com/morphismtech/squeal/issues/31#issuecomment-410262353","issue_url":"https://api.github.com/repos/morphismtech/squeal/issues/31","id":410262353,"node_id":"MDEyOklzc3VlQ29tbWVudDQxMDI2MjM1Mw==","user":{"login":"adfretlink","id":31063055,"node_id":"MDQ6VXNlcjMxMDYzMDU1","avatar_url":"https://avatars2.githubusercontent.com/u/31063055?v=4","gravatar_id":"","url":"https://api.github.com/users/adfretlink","html_url":"https://github.com/adfretlink","followers_url":"https://api.github.com/users/adfretlink/followers","following_url":"https://api.github.com/users/adfretlink/following{/other_user}","gists_url":"https://api.github.com/users/adfretlink/gists{/gist_id}","starred_url":"https://api.github.com/users/adfretlink/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/adfretlink/subscriptions","organizations_url":"https://api.github.com/users/adfretlink/orgs","repos_url":"https://api.github.com/users/adfretlink/repos","events_url":"https://api.github.com/users/adfretlink/events{/privacy}","received_events_url":"https://api.github.com/users/adfretlink/received_events","type":"User","site_admin":false},"created_at":"2018-08-03T13:58:42Z","updated_at":"2018-08-03T13:58:42Z","author_association":"NONE","body":"> Does the above make sense?\r\n\r\nYes, though it took me a bit of time to understand.\r\n\r\nI agree with you on your principled solutions - and I think that forcing users to think very carefull about nulls and join is probably the best way to proceed, particularly if you manage to expand the documentation on this particular issue.\r\n\r\nOn my side, I will try to fiddle with my model to see why my right joins didn't seem to work, and come back with a full example if I really can't make sense of it !"}}
{"actor":{"display_login":"adfretlink"}}