CODEWITHSUNDEEP
ruby-on-railspostgresql
Sachin srinivasan
Sachin srinivasan

Reputation: 828

How to use dynamic array of ids in raw query (rails + postgres)?

I have this code 

Language.all.map{|l|[l.language, l.accounts.joins(:children).where("accounts.id IN (?)", @accounts.ids).uniq.count]}

I am trying to get this as output

[["eng", 5] ["span", 3] ["ital", 4] ... ]

I want to write it as a raw query.

I tried this,

ActiveRecord::Base.connection.execute("select languages.language, (SELECT COUNT(*) FROM accounts where accounts.language_id = languages.id) from languages").values

but i need to pass accounts.ids dynamic.. like this

select languages.language, (SELECT COUNT(*) FROM accounts where accounts.language_id = languages.id AND (accounts.id IN (#{@accounts.ids})) from languages"

when i tried to pass accounts.id IN #{@accounts.ids} i am getting error (accounts.id IN ([2839, 284.. .. this should have been IN (2839, 284..) instead, it is taking array.

How to pass it dynamically ?

Upvotes: 2

Views: 1818

Answers (3)

thinkgeekguy
thinkgeekguy

Reputation: 75

The above 2 answers won't work if you are attempting to use an array of strings in a raw sql query. For a raw sql statement, you can use ActiveRecord's sanitize_sql_array method.

languages = ['eng', 'span', 'chin', 'ital']
base_query = "SELECT * FROM languages WHERE id IN (?)"
sanitized_query = ActiveRecord::Base.send :sanitize_sql_array, [base_query, languages]
ActiveRecord::Base.connection.execute(sanitized_query)

Upvotes: 1

Robert Pankowecki
Robert Pankowecki

Reputation: 735

You can use where(id: @accounts.ids) or where("account.ids": @accounts.ids) or where("account.ids IN ?, @accounts.ids). I believe ActiveRecord should understand all of them.

Upvotes: 0

hoangdd
hoangdd

Reputation: 509

You can try:

"... accounts.id IN (#{@accounts.ids.join(',')})"

Hope it helps.

Upvotes: 3

Related Questions