sql: DISTINCT give me duplicate results

Question

Hello I am using following query

 Message.select("DISTINCT(commentable_id, user_id) as owner_id").map(&:owner_id)

It gives me the result like this: ["(8,9)", "(8,84)", "(9,8)", "(84,8)"]

here "(8, 9)" and "(9, 8)" are returns as different, but I want only single record. Means the result should be like

["(8,9)", "(8,84)"] So how can I achieve it.

Update

My table:

id | user_id | commentable_id
1  |  8      |      9
2  |  8      |      84
3  |  9      |      8
4  |  84     |      8
5  |  8      |     84

And I want result with id 1, 2. Actually this is conversation view so either I am a sender(user_id) or receiver(commentable_id). If I am a user with id 8 then in my conversation view I will have only two with id 9 and 84.

Thanks

Answer 1

SQL DISTINCT is working as it is designed for. It might be too difficult to do this from only SQL. Since you are using Ruby, I would suggest to use a for-in loop or something to filter out the results further.

Answer 2

You can express what you want in SQL using the greatest() and least() functions:

select distinct least(user_id, commentable_id) as id1, greatest(user_id, commentable_id) as id2
from mytable;

You should be able to express this in Ruby on Rails as well.