{"action":"created","issue":{"url":"https://api.github.com/repos/go-pg/pg/issues/352","repository_url":"https://api.github.com/repos/go-pg/pg","labels_url":"https://api.github.com/repos/go-pg/pg/issues/352/labels{/name}","comments_url":"https://api.github.com/repos/go-pg/pg/issues/352/comments","events_url":"https://api.github.com/repos/go-pg/pg/issues/352/events","html_url":"https://github.com/go-pg/pg/issues/352","id":181513247,"number":352,"title":"Problem with recognizing joins","user":{"login":"ababo","id":503292,"avatar_url":"https://avatars.githubusercontent.com/u/503292?v=3","gravatar_id":"","url":"https://api.github.com/users/ababo","html_url":"https://github.com/ababo","followers_url":"https://api.github.com/users/ababo/followers","following_url":"https://api.github.com/users/ababo/following{/other_user}","gists_url":"https://api.github.com/users/ababo/gists{/gist_id}","starred_url":"https://api.github.com/users/ababo/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ababo/subscriptions","organizations_url":"https://api.github.com/users/ababo/orgs","repos_url":"https://api.github.com/users/ababo/repos","events_url":"https://api.github.com/users/ababo/events{/privacy}","received_events_url":"https://api.github.com/users/ababo/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":5,"created_at":"2016-10-06T20:10:09Z","updated_at":"2016-10-10T10:20:32Z","closed_at":null,"body":"I try to get collect all nodes which have access records with a given UserID and compatible permission mask.\r\n\r\nThis is my data schema:\r\n\r\n```go\r\ntype node struct {\r\n\tID     string\r\n\tUsers  []userNodeAccess\r\n}\r\n\r\ntype userNodeAccess struct {\r\n\tUserID      string\r\n\tNodeID      string\r\n\tNode        *node\r\n\tPermissions uint\r\n}\r\n\r\nfunc (s *server) findAccessibleNodes(\r\n\tuserID string, permissions uint) ([]node, error) {\r\n\tnodes := []node{}\r\n\terr := s.db.Model(&[]node{}).\r\n\t\tColumn(\"node.*\", \"Users\").\r\n\t\tWhere(\"user_id = ?\", userID).\r\n\t\tWhere(\"permissions & ? = ?\", permissions, permissions).\r\n\t\tSelect(&nodes)\r\n\treturn nodes, err\r\n}\r\n```\r\nAs result `pg` generates the following joinless  query:\r\n\r\n```sql\r\n SELECT \"node\".*, \"Users\" FROM \"nodes\" AS \"node\" WHERE (user_id = '94787eb0-e32c-4b7d-b88c-b231247178e8') AND (permissions & 1 = 1)\r\n```\r\nWhy the join recognition is so unreliable? Or maybe I got it wrong?"},"comment":{"url":"https://api.github.com/repos/go-pg/pg/issues/comments/252580429","html_url":"https://github.com/go-pg/pg/issues/352#issuecomment-252580429","issue_url":"https://api.github.com/repos/go-pg/pg/issues/352","id":252580429,"user":{"login":"vmihailenco","id":290976,"avatar_url":"https://avatars.githubusercontent.com/u/290976?v=3","gravatar_id":"","url":"https://api.github.com/users/vmihailenco","html_url":"https://github.com/vmihailenco","followers_url":"https://api.github.com/users/vmihailenco/followers","following_url":"https://api.github.com/users/vmihailenco/following{/other_user}","gists_url":"https://api.github.com/users/vmihailenco/gists{/gist_id}","starred_url":"https://api.github.com/users/vmihailenco/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/vmihailenco/subscriptions","organizations_url":"https://api.github.com/users/vmihailenco/orgs","repos_url":"https://api.github.com/users/vmihailenco/repos","events_url":"https://api.github.com/users/vmihailenco/events{/privacy}","received_events_url":"https://api.github.com/users/vmihailenco/received_events","type":"User","site_admin":false},"created_at":"2016-10-10T10:20:32Z","updated_at":"2016-10-10T10:20:32Z","body":"go-pg here generates 2 queries that\r\n1. select all nodes\r\n2. select all users for every node selected at step 1)\r\n\r\nBut apparently you expect a `JOIN` so you can filter nodes by user_id and permissions. You can do it manually:\r\n\r\n```\r\ndb.Column(...).Join(\"JOIN user_node_accesses ON ...\").Where(...)\r\n```\r\n\r\nProbably at some point go-pg will support something like this `db.Column(...).JoinRelation(\"Users\").Column(...)`"}}
{"actor":{"display_login":"vmihailenco"}}