CODEWITHSUNDEEP
rubypostgresqlsequel
dagda1
dagda1

Reputation: 28890

ruby sequel gem - how to query arrays with the pg_array extension

I am using the pg_array extension and sequel version 4.1.1.

I have added the extension like this:

Sequel::Database.extension :pg_array

I have created a column like this:

alter_table :emails do
  add_column :references, "text[]", null: true
end

I can load and retrieve arrays into a postgress array column, just like working with normal arrays.

What is not clear from the above link is how do I execute a query based on the values in this array column.

For example, if one row in the emails table contained these values in the references column:

                             references                             
--------------------------------------------------------------------
 {}
 {[email protected]}

How can I query the emails table to find a row that contains a references array value of the above value:

Email.where(references: ????)

Upvotes: 8

Views: 2373

Answers (2)

Малъ Скрылевъ
Малъ Скрылевъ

Reputation: 16515

You can try:

ref = '5363f773bccf9'
emails = Email.arel_table
Email.where( emails[ :references ].matches( "%#{ref}%" ))

Upvotes: -1

Jeremy Evans
Jeremy Evans

Reputation: 12149

Use the pg_array_ops extension:

Sequel.extension :pg_array_ops
Email.where(Sequel.pg_array_op(:references).contains('[email protected]'))

Upvotes: 9

Related Questions