returning specific values extracted from an hstore (Postgres + hstore_translate gem)

Question

I am using hstore_translate within a Rails4 project to handle my I18n needs.

Assume I have the following model:

class Thingy < ActiveRecord::Base
  translates :name
end

with table defined in a migration as

create_table :thingies do |t|
  t.hstore name_translations
end
add_index ::thingies, :name_translations, using: :gin

In my Ruby code I wish to retrieve a list of the all the names and ids for Thingies with a name in a specific locale.

Previously, before my Thingy had a localised name, I could just do

thingies = Thingy.order(:name).pluck(:id, :name)

Now I am doing

thingies = Thingy.where("name_translations ? 'en'").order("name_translations -> 'en'").to_a.map do |t|
  {id: t.id, name: t.name}
end

But I can't help feeling there's a way I can better leverage Postgres to do this all in one line of code without invoking a loop in Ruby.

Answer 1

I've worked it out with a bit of trial and error.

thingies = Thingy.where("name_translations ? 'en'")
                 .order("name_translations -> 'en'")
                 .pluck(:id, ("name_translations -> 'en'"))

does the job.

It's not very DRY but it works.