CODEWITHSUNDEEP
arraysruby-on-railspostgresqlhash
daveasdf
daveasdf

Reputation: 81

How do I search an array of hashes based on multiple keys in Rails Postgres?

I'd like to have an array of hases that I can input and search.

add_column :drinkers, :habits, :text, array: true, default: []

drinker.habits << { year: DateTime.now.year, month: DateTime.now.month, drinker_id: drinker_id, napped_at_8am: true }

How do I search for drinkers based on multiple drinker.habits key values? 

Drinker.select {|drinker| drinker[habits][:year] === 2020 && drinker[habits][:drinker_id] === 1 }

That would be my guess, but I can't find anything anywhere about multiple key value searches.

Upvotes: 1

Views: 887

Answers (1)

max
max

Reputation: 102036

This is really just a classic example of shooting yourself in the foot with bad database modeling.

Postgres arrays are a good tool for really specialized tasks where you have an array of simple scalar values such as integers or strings. They are not a good idea if you want to store hashes. If you shove a Ruby hash inside of an array column you get an non-queryable mess.

The JSON/JSONB type can store more complex structures like an array of objects and is a valid choice if the data defies any attempt to confirm to a schema. While you can query an array of objects with the includes operator:

SELECT *
FROM "drinkers"
WHERE habits @> '[{"year": 2020}]'

You are really not doing yourself any favors here if the data is structured. Once you step up the complexity the queries will be hideous and you won't have any indices that will speed them up either.

Instead you want to just face the facts that relational databases are tabular and the way to model data in a relational database is - you guessed it - tables.

class Drinker < ApplicationRecord
  has_many :habits
end

class Habit < ApplicationRecord
  belongs_to :drinker
end

Drinker.joins(:habits)
       .where(habits: { year: 2020 })

TLDR; array, JSON/JSONB and hstore are decent tools for really specialized jobs. But should never be your first choice for anything.

Upvotes: 1

Related Questions