Ruby + Sequel - Serialize Array into Query of Text Array

Question

I have an array of filters in a ruby application which makes a raw SQL query to a Postgres connection. I'm trying to figure out how I can serialize this array into something query-able,

i.e.: SELECT * FROM data WHERE strings IN #{strings_array}

I can't find any resources for this. What's the correct way to serialize this query?

Edit: I ended up figuring out:

query = ''
arr.each_with_index { |e, i|
  if i == arr.length - 1
    query += "#{e}"
  else
    query += "#{e},"
  end
}

then

" AND column @> ('{#{query}}')"

But there must be a less verbose way, no?

You want to serialize the array of data or the query over that data? — Jesper
– Jesper, Commented Feb 6, 2015 at 8:11
@Jesper - I want to query a variable array of data. For example, I have an array of strings like names = ['Jason', 'Bob', 'Chris'] and I want to embed names into my query string, so that I can match a column first_names where the values in the names array are in that row — jtmarmon
– jtmarmon, Commented Feb 6, 2015 at 8:16

Jesper · Accepted Answer · 2015-02-06 09:22:04Z

3

You say that you use Sequel. Sequel can perform this kind of query automatically:

DB[:data].where(strings: ["my", "array", "of", "strings"])
# => #<Sequel::Postgres::Dataset: "SELECT * FROM \"data\" WHERE (\"strings\" IN ('my', 'array', 'of', 'strings'))">

Constructing SQL queries "by hand" is risky, especially if you're dealing with data coming from users.

answered Feb 6, 2015 at 9:22

Jesper

4,5552 gold badges25 silver badges34 bronze badges

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

Comments

Yann VERY · Accepted Answer · 2015-02-06 09:47:30Z

2

This can be less verbose if you use join method like this :

# I presume arr contains an array that you want serialize
strings_array = "'#{arr.join("','")}'"
query = "SELECT * FROM data WHERE strings IN #{strings_array}"

edited Feb 6, 2015 at 9:47

answered Feb 6, 2015 at 9:21

Yann VERY

1,81914 silver badges11 bronze badges

2 Comments

splrs Over a year ago

This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post.

Yann VERY Over a year ago

@splrs I update my answer for a better comprehension

peter · Accepted Answer · 2015-02-06 12:11:47Z

Once you learn to know ORM (Activerecord in this case) you won't be constructing SQL manually or semi-automatic like you do. Take this example, It creates a connection to a database (Oracle in this case), maps the table to a Ruby class and gives all kind of manupulation methods to that class. In this example I use two where's, one with a hash as filter and one with a tiny bit of SQL. The result will be a SQL that is automatically generated and executed once you do something with it (use each in this case).

require 'active_record'

ActiveRecord::Base.establish_connection(
  :adapter => 'oracle_enhanced',
  :database => "(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = ORATCP.world)(PROTOCOL = TCP)(Host = server.domain)(Port = 9999)))(CONNECT_DATA = (SID = SID_ID)))",
  :username => 'user',
  :password => 'password'
)

class Member < ActiveRecord::Base
  self.table_name = 'my_table'
end

Member.where(country: 'Belgium').where("'gender = 'M'").each do |member|
  p member
end

Collectives™ on Stack Overflow

Ruby + Sequel - Serialize Array into Query of Text Array

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related