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

Question

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 ?

hoangdd · Accepted Answer · 2017-07-28 07:57:02Z

3

You can try:

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

Hope it helps.

answered Jul 28, 2017 at 7:57

hoangdd

5092 silver badges14 bronze badges

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

8 Comments

Sachin srinivasan Over a year ago

nope. getting PG::syntax error. it takes the whole thing as string

hoangdd Over a year ago

Oh, sorry, I think you use Mysql.

hoangdd Over a year ago

Did you forgot a ")" in query ?

Sachin srinivasan Over a year ago

no. syntax error was not because of ')' .. but for strings (" 1, 2, 3 ... ")

hoangdd Over a year ago

I don't know where " come from? It should be ... accounts.id IN (1, 2, 3).

|

thinkgeekguy · Accepted Answer · 2020-06-04 12:39:26Z

1

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)

answered Jun 4, 2020 at 12:39

thinkgeekguy

751 gold badge1 silver badge6 bronze badges

1 Comment

Michael Schmitz Over a year ago

Very helpful, the change from an id to an uuid based system are tricky.

Robert Pankowecki · Accepted Answer · 2017-07-28 08:14:26Z

0

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.

answered Jul 28, 2017 at 8:14

Robert Pankowecki

7356 silver badges16 bronze badges

1 Comment

Sachin srinivasan Over a year ago

yes. i know but i am trying to map another table records to get in specific format [[a, b] [c, d]]. it results in low performance since there are large number of records.

Collectives™ on Stack Overflow

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

3 Answers 3

8 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related