CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
Vygandas Pliasas
Vygandas Pliasas

Reputation: 192

How to select random rows with distinct user (ActiveRecord)

I have a trouble with selecting two random photos of different users... It would be awesome if u could help me :)

So, lets say that my own user_id=1. We don't need that also.

Table looks something like this:

|----------------------------|
| id | user_id | url | score |
|----------------------------|
| 1  | 1       | A   | 15    |
| 2  | 1       | B   | 5     |
| 3  | 2       | C   | 1     |
| 4  | 2       | D   | 51    |
| 5  | 2       | E   | 2     |
| 6  | 2       | F   | 9     |
| 7  | 3       | G   | 7     |
| 8  | 3       | H   | 3     |
| 9  | 3       | I   | 88    |
| 10 | 4       | J   | 15    |
| 11 | 4       | K   | 0     |
| .. | ...     | ..  | ...   |
|----------------------------|

So as I'm user 1, I don't have to get lines where user_id=1.

I need two random rows of two other users. And those rows cant be of same user also. In short - not me and not other 2 same users.

E.g. 

| 5  | 2       | E   | 2     |
| 8  | 3       | H   | 3     |
or
| 8  | 3       | H   | 3     |
| 11 | 4       | K   | 0     |
or
| 10 | 4       | J   | 15    |
| 3  | 2       | C   | 1     |

SQL query would be OK. But if u can provide it in Ruby on Rails ActiveRecord structure it would be cool.

EDIT:

What I have tried:

@photos = Photo.order('RANDOM()').where.not(:user_id => current_user.id).limit(2).uniq(:user_id)

@photos = Photo.order('RANDOM()').where.not(:user_id => current_user.id).limit(2).distinct(:user_id)

@photos = Photo.distinct(:user_id).random(2)

@photos = Photo.uniq(:user_id).random(2)

P.S. random() came from gem "randumb". No big impact I think.

EDIT 2:

This works

@photos = []
@photo1 = Photo.order('RANDOM()').where.not(:user_id => current_user.id).first
@photo2 = Photo.order('RANDOM()').where.not(:user_id => current_user.id, :user_id => @photo1.user_id).first
@photos << @photo1 << @photo2

Upvotes: 0

Views: 641

Answers (1)

akshayB
akshayB

Reputation: 26

First of all this will get you a random picture of a given user

Photo.where("user_id = ?",<USER_ID>).shuffle.first

Now you need 2 distinct users which are not current users

Photo.select("distinct user_id").where("user_id <> ?",<CURRENT_USER>).shuffle.first(2)

And now everything in one shot

@photos = []
unique_user_cnt = 2
Photo.select("distinct user_id").where("user_id <> ?",<CURRENT_USER>).shuffle.first(unique_user_cnt).each do |p|
  @photos << Photo.where("user_id = ?",p.user_id).shuffle.first
end

Upvotes: 1

Related Questions