2
\$\begingroup\$

Is there a cleaner way to do the following?

friend_ids = [1,2,3,4,5]
friendIDsQuery = ""
friend_ids.each_with_index do |friend_id, index|
 friendIDsQuery += "SELECT id FROM test WHERE user_id = #{friend_id}"
 if index != friend_ids.size - 1
 friendIDsQuery += " INTERSECT "
 end
end

Basically I'm passing in an array of IDs, and building a custom INTERSECT query.

asked Oct 10, 2012 at 20:29
\$\endgroup\$
1
  • 2
    \$\begingroup\$ you wrote Array#join by hand... \$\endgroup\$ Commented Oct 11, 2012 at 10:42

2 Answers 2

4
\$\begingroup\$
query = friend_ids.map {|id| "SELECT id FROM test WHERE user_id=#{id}"}.join " INTERSECT "

Bam.

Edit: Ok, well, not just bam. First of all, if you're using Rails, go with something like NewAlexandria's answer, because it doesn't involve raw string interpolation (which quickly leads to SQL injection vulnerabilities). To make the above a little more safe (and assuming the IDs are integers), you should probably do

query = friend_ids.map { |id|
 "SELECT id FROM test WHERE user_id = #{id.to_i}"
}.join " INTERSECT "
answered Oct 10, 2012 at 21:48
\$\endgroup\$
4
\$\begingroup\$

This would be the more idiomatic Rails form, without so much SQL.

friend_ids.
 map {|fid| Test.select(:id).where(:user_id => fid) }.
 reduce {|a,e| a = a & e }

If you really need the sql, then append:

.to_sql

answered Oct 16, 2012 at 15:15
\$\endgroup\$
5
  • \$\begingroup\$ You're missing the intersection-part. As far as I can tell, OP isn't trying to find ids where user_id IN (...), but the intersection of multiple selections. \$\endgroup\$ Commented Oct 17, 2012 at 17:57
  • \$\begingroup\$ @Flambino you're right. Here is the correct for with intersections. I do concede that leaving the intersects within the database may be more performant than to do N queries and then operate upon them. \$\endgroup\$ Commented Oct 18, 2012 at 2:35
  • 1
    \$\begingroup\$ +1. I'd say your solution has the distinct advantage of not generating a query string "by hand". In my answer I'm assuming that the IDs are safe - otherwise, well, hello injection attacks (should probably note that in my answer). Meanwhile, you're assuming that it's Rails. Very probable, but OP doesn't say. As for performance, your guess is as good as mine - OP doesn't say mention anything about the number of queries. \$\endgroup\$ Commented Oct 18, 2012 at 16:24
  • 1
    \$\begingroup\$ the OP didn't specify whether ActiveRecord or Rails was being used. Anyway, refactor of the reduce: reduce([], :&). \$\endgroup\$ Commented Oct 25, 2012 at 13:50
  • \$\begingroup\$ @tokland though arcane, that's hot \$\endgroup\$ Commented Oct 25, 2012 at 13:53

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.