Rails Custom SQL Query not working with find_by_sql

Question

I have three Tables users, user_groups and user_groups_users in my Rails Application. Now, I have written an SQL query

SELECT user_group_id FROM user_groups_users GROUP BY user_group_id HAVING SUM(CASE WHEN user_id IN (1,4) THEN 1 ELSE 0 END) = COUNT(*)

to extract those user_group_id, which exactly has the user_ids provided by the input. In this case 1,4 would be provided as an ruby array [1,4].

Now, i want to write this SQL query for my rails application. But when I execute this query using find_by_sql statement in rails,it is returning me the correct no. of user_groups in which the particular user_ids belong, but the result does not show the user_group_id.

=> [#<UserGroup >, #<UserGroup >, #<UserGroup >, #<UserGroup >, #<UserGroup >, #<UserGroup >]

so how can i extract User_group_id from it.

However, when I execute the query by connecting directly to the database. I am getting the desired results

sql = "SELECT user_group_id FROM user_groups_users WHERE user_id IN (1,2) GROUP BY user_group_id HAVING Count(DISTINCT user_id) = 2;"

records_array = ActiveRecord::Base.connection.execute(sql)

But, here the problem is, how would I use the String replacement technique, to use the user_ids given as input.

Salil · Accepted Answer · 2013-12-04 12:44:54Z

1

You are getting array of the object so you need to iterate loop over it

user_groups_users = [#<UserGroup >, #<UserGroup >, #<UserGroup >, #<UserGroup >, #<UserGroup >, #<UserGroup >]

user_groups_users.map do |user_groups_user|
  puts user_groups_user.user_group_id
end

answered Dec 4, 2013 at 12:44

Salil

47.7k22 gold badges127 silver badges161 bronze badges

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

1 Comment

Salil Over a year ago

Please upvote and accept the answer if it works for you.So, other can understand what is the correct answer.

Collectives™ on Stack Overflow

Rails Custom SQL Query not working with find_by_sql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related