Hi!
I'm trying to use the PGArray extension, but the Ruby arrays are returned as
strings, instead of Array instances. I'm using versions:
$ bundle show | grep -E "sequel|pg"
* pg (0.14.1)
* sequel (3.41.0)
* sequel_pg (1.6.1)
My setup code is (within a Padrino app):
Sequel::Model.plugin(:schema)
Sequel::Model.raise_on_save_failure = false # Do not throw exceptions on failure
Sequel::Model.db = Sequel.connect((ENV["PORTAL_DATABASE_URL"] ||
"postgres://localhost/portal_#{Padrino.env}").sub("jdbc:postgresql",
"postgres"), :loggers => [Padrino.logger])
require "sequel/extensions/pg_array"
Sequel::Model.db.extend Sequel::Postgres::PGArray::DatabaseMethods
Then I ask for active users with their permissions and associated accounts:
user0 = User.naked.
select(:users__email, :users__name, :users__default_ui_language, :users__id,
:users__user_id, :array_agg.sql_function(:domain).as(:domains),
:array_agg.sql_function(:permission).as(:products)).
inner_join(:user_accounts, [:email]).
inner_join(:account_product_permissions, [:domain]).
filter(users__id: authenticated_user_id, users__user_state: "active").
group(:users__email, :users__name, :users__default_ui_language, :users__id,
:users__user_id).
first
user1 = User.
select(:users__email, :users__name, :users__default_ui_language, :users__id,
:users__user_id, :array_agg.sql_function(:domain).as(:domains),
:array_agg.sql_function(:permission).as(:products)).
inner_join(:user_accounts, [:email]).
inner_join(:account_product_permissions, [:domain]).
filter(users__id: authenticated_user_id, users__user_state: "active").
group(:users__email, :users__name, :users__default_ui_language, :users__id,
:users__user_id).
first
I'm running the query twice to see if there are differences with the #naked
call, while debugging. The generated query is (identical between both cases, as
expected):
SELECT
"users"."email"
, "users"."name"
, "users"."default_ui_language"
, "users"."id", "users"."user_id"
, array_agg("domain") AS "domains"
, array_agg("permission") AS "products"
FROM "users"
INNER JOIN "user_accounts" USING ("email")
INNER JOIN "account_product_permissions" USING ("domain")
WHERE (("users"."id" = 2) AND ("users"."user_state" = 'active'))
GROUP BY "users"."email", "users"."name", "users"."default_ui_language",
"users"."id", "users"."user_id"
LIMIT 1
which is, as far as I'm concerned, exactly what I want. Running the above query
in psql returns correct values in domains and products, namely text::[] values.
Unfortunately, the resulting hash has unparsed values:
(rdb:1) pp user0
{:email=>"[email protected]",
:name=>"Customer",
:default_ui_language=>"fr",
:id=>2,
:user_id=>"aee3a8ff-6961-4700-87e5-d5eb0546c0ac",
:domains=>"{yourdomain,yourdomain}",
:products=>"{dashboard,\"weekly report\"}"}
(rdb:1) pp user1
#<User @values={:email=>"[email protected]", :name=>"Customer",
:default_ui_language=>"fr", :id=>2,
:user_id=>"aee3a8ff-6961-4700-87e5-d5eb0546c0ac",
:domains=>"{yourdomain,yourdomain}", :products=>"{dashboard,\"weekly
report\"}"}>
I expected user0.fetch(:products) to return the Ruby value ["dashboard",
"weekly report"].
Did I forget to load an extension? According to 3.34's[1] release notes, I only
need to load the PGArray extension.
Thanks!
François
[1]: http://sequel.rubyforge.org/rdoc/files/doc/release_notes/3_34_0_txt.html
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
