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.
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