{"action":"created","issue":{"url":"https://api.github.com/repos/rails/rails/issues/19165","labels_url":"https://api.github.com/repos/rails/rails/issues/19165/labels{/name}","comments_url":"https://api.github.com/repos/rails/rails/issues/19165/comments","events_url":"https://api.github.com/repos/rails/rails/issues/19165/events","html_url":"https://github.com/rails/rails/pull/19165","id":59448318,"number":19165,"title":"Store queries for PostgresQL","user":{"login":"palkan","id":1516722,"avatar_url":"https://avatars.githubusercontent.com/u/1516722?v=3","gravatar_id":"","url":"https://api.github.com/users/palkan","html_url":"https://github.com/palkan","followers_url":"https://api.github.com/users/palkan/followers","following_url":"https://api.github.com/users/palkan/following{/other_user}","gists_url":"https://api.github.com/users/palkan/gists{/gist_id}","starred_url":"https://api.github.com/users/palkan/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/palkan/subscriptions","organizations_url":"https://api.github.com/users/palkan/orgs","repos_url":"https://api.github.com/users/palkan/repos","events_url":"https://api.github.com/users/palkan/events{/privacy}","received_events_url":"https://api.github.com/users/palkan/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/rails/rails/labels/activerecord","name":"activerecord","color":"0b02e1"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":3,"created_at":"2015-03-02T07:42:05Z","updated_at":"2015-03-02T16:14:51Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/rails/rails/pulls/19165","html_url":"https://github.com/rails/rails/pull/19165","diff_url":"https://github.com/rails/rails/pull/19165.diff","patch_url":"https://github.com/rails/rails/pull/19165.patch"},"body":"PostgresQL has a great support for querying store-like data (hstore, jsonb). But AR don't, so you have to write plain old SQL. Sometimes it leads to [confusion](https://github.com/rails/rails/issues/17589).\r\n\r\nWe can enhance query methods with store-specific ones and write more _ORM-friendly_ code.\r\n\r\nExamples:\r\n```ruby\r\n\r\n# Key exists\r\n# before\r\nModel.where(\"json ? :something\", something: \"some_key\") \r\n# after\r\nModel.where.store(:json).key(:some_key)\r\n\r\n# Keys exist\r\n# before\r\nModel.where(\"h ?& ARRAY[:keys]\", keys: vals.map(&:to_s))\r\n# after\r\nModel.where.store(:h).keys(vals)\r\n\r\n# Query by key value\r\n# before\r\nModel.where(\"hstore->'a' = :v\", v: val.to_s)\r\n# after\r\nModel.where.store(:hstore, a: val) # we don't care here about the value type\r\n\r\n# and so on, see more in the provided test case\r\n```\r\n\r\nThe above (and proposed) implementation uses `WhereChain` as an entry point and translate store operators to usual where clauses. Thus there is no need of extending Arel with new types of nodes. \r\n\r\nI think, it's also possible to avoid using additional `store` method:\r\n\r\n```ruby\r\nModel.where(:store).keys(:a,:b)\r\n\r\nModel.where(:store, a: val)\r\n```\r\n\r\nIt is shorter, but, maybe, a little bit more confusing.\r\n\r\nCurrently, it's not working with complex values, such as arrays or ranges, when querying by store key value.\r\nTo handle different value types it's reasonable to pass Hash to `WhereClauseFactory#build`.\r\n```ruby\r\nModel.where.store(:hstore, a: [1,2]) \r\n# it calls\r\nwhere_clause_factory.build {\"hstore->'a'\" => ['1','2']}, {}\r\n# which produces\r\n%q(\r\nselect ... where \"models\".\"hstore->'a'\" in ('1',\\ '2')' \r\n)\r\n# which is incorrect due to quotes around hstore->'a'\r\n```\r\n\r\nBut there is a possible fix: use `Arel::Nodes::SqlLiteral` instead of simple `String` as a key.\r\nIn order it to work we should add `Arel::Nodes::SqlLiteral#to_s` to return `self`, because now it returns `String`. \r\n\r\nWhat do you think about this feature, does it make sense? (I think yes)\r\n\r\n/cc @rafaelfranca @sgrif "},"comment":{"url":"https://api.github.com/repos/rails/rails/issues/comments/76741352","html_url":"https://github.com/rails/rails/pull/19165#issuecomment-76741352","issue_url":"https://api.github.com/repos/rails/rails/issues/19165","id":76741352,"user":{"login":"palkan","id":1516722,"avatar_url":"https://avatars.githubusercontent.com/u/1516722?v=3","gravatar_id":"","url":"https://api.github.com/users/palkan","html_url":"https://github.com/palkan","followers_url":"https://api.github.com/users/palkan/followers","following_url":"https://api.github.com/users/palkan/following{/other_user}","gists_url":"https://api.github.com/users/palkan/gists{/gist_id}","starred_url":"https://api.github.com/users/palkan/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/palkan/subscriptions","organizations_url":"https://api.github.com/users/palkan/orgs","repos_url":"https://api.github.com/users/palkan/repos","events_url":"https://api.github.com/users/palkan/events{/privacy}","received_events_url":"https://api.github.com/users/palkan/received_events","type":"User","site_admin":false},"created_at":"2015-03-02T16:14:51Z","updated_at":"2015-03-02T16:14:51Z","body":"First, we put PG specific logic into `Relation` only if we are using PG. What's the problem?\r\n\r\nSecond, store accessors would not solve the problem, they could only deal with querying by store key, but not with existence and containment operators. \r\n\r\nFinally, I think this is feature is very useful, especially due to the recent release of PG 9.4 with JSONB. But it can be easily extracted as a separate gem, rather than part of AR."}}