CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
josh
josh

Reputation: 135

Random ActiveRecord with where and excluding certain records

I would like to write a class function for my model that returns one random record that meets my condition and excludes some records. The idea is that I will make a "random articles section."

I would like my function to look like this

Article.randomArticle([1, 5, 10]) # array of article ids to exclude

Some pseudo code:

ids_to_exclude = [1,2,3]

loop do
  returned_article = Article.where(published: true).sample
  break unless ids_to_exclude.include?(returned_article.id)
do

Upvotes: 1

Views: 571

Answers (2)

max
max

Reputation: 102218

Lets look at a DB specific option.

class Article
  # ...
  def self.random(limit: 10)
    scope = Article.where(published: true)
    # postgres, sqlite
    scope.limit(limit).order('RANDOM()')
    # mysql
    scope.limit(limit).order('RAND()')
  end
end

Article.random asks the database to get 10 random records for us. So lets look at how we would add an option to exclude some records:

class Article
  # ...
  def self.random(limit: 10, except: nil)
    scope = Article.where(published: true)
    if except
      scope = scope.where.not(id: except)
    end 
    scope.limit(limit).order('RANDOM()')
  end
end

Now Article.random(except: [1,2,3]) would get 10 records where the id is not [1,2,3].

This is because .where in rails returns a scope which is chain-able. For example:

> User.where(email: '[email protected]').where.not(id: 1)
User Load (0.7ms)  SELECT "users".* FROM "users" WHERE "users"."email" = $1 AND ("users"."id" != $2)  [["email", "[email protected]"], ["id", 1]]
=> #<ActiveRecord::Relation []>

We could even pass a scope here:

# cause everyone hates Bob
Article.random(except: Article.where(author: 'Bob'))

See Rails Quick Tips - Random Records for why a DB specific solution is a good choice here.

Upvotes: 2

inye
inye

Reputation: 1796

You can use some like this:

ids_to_exclude = [1,2,3,4]
Article.where("published = ? AND id NOT IN (?)", true , ids_to_exclude ).order( "RANDOM()" ).first

Upvotes: 0

Related Questions