{"action":"created","issue":{"url":"https://api.github.com/repos/go-pg/pg/issues/870","repository_url":"https://api.github.com/repos/go-pg/pg","labels_url":"https://api.github.com/repos/go-pg/pg/issues/870/labels{/name}","comments_url":"https://api.github.com/repos/go-pg/pg/issues/870/comments","events_url":"https://api.github.com/repos/go-pg/pg/issues/870/events","html_url":"https://github.com/go-pg/pg/issues/870","id":327070550,"number":870,"title":"Query with relation and ColumnExpr","user":{"login":"Tommy-42","id":6181866,"avatar_url":"https://avatars2.githubusercontent.com/u/6181866?v=4","gravatar_id":"","url":"https://api.github.com/users/Tommy-42","html_url":"https://github.com/Tommy-42","followers_url":"https://api.github.com/users/Tommy-42/followers","following_url":"https://api.github.com/users/Tommy-42/following{/other_user}","gists_url":"https://api.github.com/users/Tommy-42/gists{/gist_id}","starred_url":"https://api.github.com/users/Tommy-42/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Tommy-42/subscriptions","organizations_url":"https://api.github.com/users/Tommy-42/orgs","repos_url":"https://api.github.com/users/Tommy-42/repos","events_url":"https://api.github.com/users/Tommy-42/events{/privacy}","received_events_url":"https://api.github.com/users/Tommy-42/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":4,"created_at":"2018-05-28T16:08:15Z","updated_at":"2018-05-30T12:16:16Z","closed_at":"2018-05-30T11:31:56Z","author_association":"NONE","body":"Hi, \r\n\r\nI have this sql schema : \r\n\r\n\r\n```sql\r\nCREATE TABLE \"domains\" (\r\n    \"id\" text PRIMARY KEY,\r\n    \"translations\" hstore,\r\n);\r\n\r\nCREATE TABLE \"values\" (\r\n    \"id\" text PRIMARY KEY,\r\n    \"domain_id\" text references domains(id) ON DELETE CASCADE NOT NULL,\r\n    \"translations\" hstore,\r\n);\r\n```\r\n\r\nI have this genereted struct ( proto )\r\n\r\n```go\r\ntype Domain struct {\r\n\tId   string `protobuf:\"bytes,1,opt,name=id,proto3\" json:\"id,omitempty\"`\r\n\tValues []*Value `protobuf:\"bytes,2,rep,name=Values\" json:\"Values,omitempty\" pg:\",discard_unknown_columns\"`\r\n\t// @inject_tag: pg:\",hstore\"\r\n\tTranslations map[string]string `protobuf:\"bytes,3,rep,name=translations\" json:\"translations,omitempty\" protobuf_key:\"bytes,1,opt,name=key,proto3\" protobuf_val:\"bytes,2,opt,name=value,proto3\" pg:\",hstore\"`\r\n}\r\ntype Value struct {\r\n\tId       string `protobuf:\"bytes,1,opt,name=id,proto3\" json:\"id,omitempty\"`\r\n\tDomainId string `protobuf:\"bytes,2,opt,name=domain_id,json=domainId,proto3\" json:\"domain_id,omitempty\"`\r\n\t// @inject_tag: pg:\",hstore\"\r\n\tTranslations map[string]string `protobuf:\"bytes,3,rep,name=translations\" json:\"translations,omitempty\" protobuf_key:\"bytes,1,opt,name=key,proto3\" protobuf_val:\"bytes,2,opt,name=value,proto3\" pg:\",hstore\"`\r\n}\r\n```\r\n\r\nthis is the kind of sql query I will do without the ORM\r\n\r\n```sql\r\nSELECT domain.id, slice(domain.translations, ARRAY['en', 'fr']) as translations, v.id, v.domain_id, slice(v.translations, ARRAY['en', 'fr']) as translations,\r\nFROM \"domains\" AS \"domain\"\r\nLEFT JOIN values AS v ON (domain.id = v.domain_id)\r\n```\r\n\r\nwhere the `slice()` can filter key from the `hstore`, with a basic `left join`.\r\n\r\nI tried to do this without really success :\r\n\r\n```go\r\n\r\nvar domains []*api.Domain\r\n\r\nquery := s.DB.Model(&domains)\r\n\r\nquery = query.Column(\"domain.*\", \"Values\").\r\n    ColumnExpr(\"slice(domain.translations, ARRAY[?]) as translations\", pg.In(req.Cultures)).\r\n    ColumnExpr(\"slice(values.translations, ARRAY[?]) as translations\", pg.In(req.Cultures))\r\n```\r\n\r\nI have this : `ERROR #42P01 missing FROM-clause entry for table \"values\"` \r\n\r\nI am not sure on how to do it with the ORM.\r\n\r\nCan you help me out with this one please ? \r\n\r\nThanks, \r\nTommy"},"comment":{"url":"https://api.github.com/repos/go-pg/pg/issues/comments/393139359","html_url":"https://github.com/go-pg/pg/issues/870#issuecomment-393139359","issue_url":"https://api.github.com/repos/go-pg/pg/issues/870","id":393139359,"user":{"login":"Tommy-42","id":6181866,"avatar_url":"https://avatars2.githubusercontent.com/u/6181866?v=4","gravatar_id":"","url":"https://api.github.com/users/Tommy-42","html_url":"https://github.com/Tommy-42","followers_url":"https://api.github.com/users/Tommy-42/followers","following_url":"https://api.github.com/users/Tommy-42/following{/other_user}","gists_url":"https://api.github.com/users/Tommy-42/gists{/gist_id}","starred_url":"https://api.github.com/users/Tommy-42/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Tommy-42/subscriptions","organizations_url":"https://api.github.com/users/Tommy-42/orgs","repos_url":"https://api.github.com/users/Tommy-42/repos","events_url":"https://api.github.com/users/Tommy-42/events{/privacy}","received_events_url":"https://api.github.com/users/Tommy-42/received_events","type":"User","site_admin":false},"created_at":"2018-05-30T12:16:16Z","updated_at":"2018-05-30T12:16:16Z","author_association":"NONE","body":"@vmihailenco thanks for the update !\r\n\r\nI've updated my vendor to reflect your changes on master but I am now confronted to some error that just happend : \r\n\r\n```\r\nERROR #42703 column domain.notnull does not exist (addr=\"127.0.0.1:5432\")\r\n```\r\n\r\nor \r\n\r\n```\r\nERROR #42703 column \"notnull\" of relation \"categories\" does not exist (addr=\"127.0.0.1:5432\")\r\n```\r\n\r\n"}}
{"actor":{"display_login":"Tommy-42"}}