How to use parameters when using raw SQL in Rails

Asked 2 months ago, Updated 2 months ago, 0 views

I wrote the following sql using raw sql in Rails.

 [Like table]
id
action_user_id
target_user_id

sql
@user=ActiveRecord::Base.connection.select('select target_user_id, count(id) from Like where action_user_id is ####group by target_user_id order by count(id)')

Pass the parameters to the above ### and

@id=User.find(#).id
@user=ActiveRecord::Base.connection.select('select target_user_id, count(id) from Like where action_user_id is @id group by target_user_id order by count(id)')

How do I do this?

Please give me some advice.

ruby-on-rails sql

2022-09-30 11:26

2 Answers

(Not a direct answer) If your goal is not to write raw SQL, but you want to do something equivalent to that SQL, you can do it by using ActiveRecord normally.

@user=Like.where(:action_user_id=>@id).group(:target_user_id).order("count(id)").select("target_user_id, count(id)AS count")
@ user.map { | u | [ u.target_user_id , u.count ] }
# = > [[1,4], [2,4], [0,5], [3,10], [4,12] It comes out like this.

Of course, you can also use placeholders as where("action_user_id=?",@id).


2022-09-30 11:26

As far as reading the current rails guide, #select_all seems to be recommended.I can use #find_by_sql to receive SQL processing results via ActiveRecord. What do you think?
find_by_sql
select_all
Also, it's a common idea to embed @id in SQL in the sample code because you can use the interposition (#{...}) style directly in the string, so if you don't mind coding reasons, I think it's enough to use that style.

"select target_user_id, count(id) from Like where action_user_id=#{@id} group by target_user_id order by count(id)"


2022-09-30 11:26

If you have any answers or tips


© 2022 OneMinuteCode. All rights reserved.