0

For the sake of the example lets say I have 15 users:

  • membertype_id = 2 (5 users called a,b,c,d,e)
  • membertype_id = 3 (5 users called f,g,h,i,j)
  • membertype_id = 4 (5 users called k,l,m,n,o)

I normally sort them with User.order("membertype_id DESC") to make them go:

a,b,c,d,e - f,g,h,i,j - k,l,m,n,o

Now I want to shuffle inside the 3 different categories, but still keep the overall order from the membertype_id:

For instance it could return it in this order:

d,c,a,b,e - g,f,i,j,h - k,m,n,l,o

or

a,c,e,b,d - f,i,j,h,g - m,k,n,o,l

or

b,d,c,a,e - j,g,h,i,f - k,n,o,l,m

but never

m,j,k,a,f,e,b,g,d,h,i,o, and so on

How would I accomplish this? I need to return an ActiveRecord_Relation.

Eyeslandic
  • 14,553
  • 13
  • 41
  • 54
sneglefar
  • 117
  • 9

1 Answers1

1

You can use the postgres random() magic.

@users = User.order(:membertype_id).order('random()')

or if mysql it's

@users = User.order(:membertype_id).order('RAND()')

Edit:

There is also the possibility of doing this in Ruby, you will however end up with an array after this, not an ActiveRelation object

@users = User.
   joins(..).
   where(..).
   shuffle.
   sort_by(&:membertype_id)
Eyeslandic
  • 14,553
  • 13
  • 41
  • 54
  • Thanks Iceman! It seems to work great in the console, but not in my rails views. Does the .order('random()') work on any ActiveRecord_Relation, or which types can the method be used on? I get this error: PG::InvalidColumnReference: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list but I also do some paginate on it – sneglefar May 28 '17 at 11:47
  • the paginate gem adds some `group by` I believe, never used it though, you just need to add the pagination after the `order` stuff. – Eyeslandic May 28 '17 at 11:48
  • How are you calling it with pagination? – Eyeslandic May 28 '17 at 11:49
  • Thanks. I tried this @paginatedresults = @search.results.order(:membertype_id).order('random()').paginate(:per_page => 10, :page => params[:page]) – sneglefar May 28 '17 at 11:49
  • The above question was basically just for example's sake - but I have an ActiveRecord_relation that contains my search results - than I put it like that^ – sneglefar May 28 '17 at 11:50
  • Well, I've never used this paginate gem, but it is probably messing it up – Eyeslandic May 28 '17 at 11:51
  • This `@search.results`, are you doing any specific `select`s there on some columns? – Eyeslandic May 28 '17 at 11:52
  • Thanks. That's my suspicion too - it has messed with me before - and I think it somehow messes with the ActiveRecord_relation object to manipulate the order.. After the SELECT DISTINCT error, I get something like this: : SELECT DISTINCT "clients".* FROM "clients" INNER JOIN "regionmemberships" ON "regionmemberships"."client_id" = "clients"."id" WHERE "clients"."visible" = $1 AND "clients"."category_id" = $2 AND (regionmemberships.region_id = 0 OR regionmemberships.region_id = 2 OR regionmemberships.region_id = 3 OR regionmemberships.region_id = 4 - So Im a little lost on that one so far – sneglefar May 28 '17 at 11:53
  • I see, unfortunately I don't know this gem, so I wish you luck! – Eyeslandic May 28 '17 at 11:55
  • @sneglefar I updated with another possibility, maybe it could help. – Eyeslandic May 28 '17 at 16:13
  • Thanks Iceman - Ive spent the last 4 hours trying. I'll look into it! I think I've half-figured out the way to make it shuffle correctly in the views and it turns out pagination is no problem. The SELECT DISTINCT error came because the order('random()' didnt knew what specific row it should randomize on. I can now shuffle them correctly but there are duplicates now because I had to remove a .uniq thing in my query, due to problems with PostGreSQL and .uniq - This is my current code: @paginatedresults = @search.results.select('clients.*, random()').order('random()').order("membertype_id DESC") – sneglefar May 28 '17 at 16:51
  • So I use @search.results.select to go into a select list basically - to make the method know that im using it on the clients - Only annoying thing now is that I have duplicates in my views, and I want to remove them using rails 5 - seems as if .uniq turns it into an array, but im looking for a way to remove the duplicates and still have an AR_relation. The reason behind is documented here: https://stackoverflow.com/questions/9758793/rails-3-activerecord-postgresql-uniq-command-doesnt-work – sneglefar May 28 '17 at 16:53
  • Can't you add a distinct in there somewhere? – Eyeslandic May 28 '17 at 16:54
  • I've tried a bit with distinct, but I think I might just have typed it the wrong place. I read: https://stackoverflow.com/questions/9658881/rails-select-unique-values-from-a-column on how to use the distinct - but I fail to know where to put it - Where/how would you put the distinct? – sneglefar May 28 '17 at 16:56
  • It's pretty difficult to guess without knowing the schema really, you just have to play around a see what will finally give the right solution. I know it's very frustrating when one is so close to a solution. – Eyeslandic May 28 '17 at 17:01
  • Thanks for all the help though! You're fantastic! Would you think the distinct should go at controller level or model level, now that you mention the schema? – sneglefar May 28 '17 at 17:06
  • That's really up to you, how you want to structure your project. Good luck! – Eyeslandic May 28 '17 at 19:38
  • Eventually solved this by adding .pluck(:id).to_a.uniq - This would pluck the id row, make it an array with only unique values - Then I would use the GREAT gem 'order-as-specified' to turn the array back into an AR_relation. Can definitely recommend that gem - it should really come out of the box with Ruby on Rails. – sneglefar May 30 '17 at 07:51
  • @sneglefar Good you got it working. Never heard of that gem, will check it out. – Eyeslandic May 30 '17 at 08:36