How to query Postgresql HSTORE in ruby on rails

Question

I need to find all the notifications of a user of type interview.

app/models/notification.rb:

class Notification < ActiveRecord::Base
  belongs_to :user

  hstore_accessor(
    :data,
    path: :string,
    message: :string,
    type: :string
  )
end

Searching in google there's not too much information, so I followed this tutorial: but was unable to query the DB as stated there in the example:

User.where("preferences -> newsletter = :value", value: 'true')

I tried

user.notifications.where('data -> type = :key', key: Interview::NOTIFICATION_TYPE)

But I got the error:

*** ActiveRecord::StatementInvalid Exception: PG::UndefinedColumn: ERROR:  column "type" does not exist
LINE 1: ...WHERE "notifications"."user_id" = $1 AND (data -> type = 'IN...
                                                             ^
: SELECT COUNT(*) FROM "notifications" WHERE "notifications"."user_id" = $1 AND (data -> type = 'INTERVIEW')`

Dorian · Accepted Answer · 2021-02-17 09:03:01Z

2

The correct way to query the HSTORE is being careful of the single quotes around the key, like this:

user.notifications.where(
  "data -> 'type' = :key",
  key: Interview::NOTIFICATION_TYPE
).count

edited Feb 17, 2021 at 9:03

Dorian

9,4276 gold badges51 silver badges74 bronze badges

answered Apr 3, 2017 at 21:30

juliangonzalez

4,4012 gold badges42 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to query Postgresql HSTORE in ruby on rails

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related