Is it possible to "add" SQL clauses related to a `scope` method in a `where` method / clause?

Question

I am using Ruby on Rails 3.2.2 and I am experimenting the Squeel gem. I would like to know if (in some way, by using the Squeel gem or not) it is possible to "add" SQL clauses related to a scope method "directly" in a where clause. That is, I have:

class Article < ActiveRecord::Base
  # Note: This is a scope method.
  def self.created_by(user)
    where(:user_id => user.id)
  end

  # I would like to use a scope method like the following.
  #
  # Note: Code in the following method doesn't work, but it should help
  # understanding what I mean.
  def self.scope_method_name(user)
    where{ created_by(user) | ... & ... }
  end
end

So, when I run Article.scope_method_name(@current_user).to_sql then it should return something like the following:

SELECT articles.* FROM articles WHERE articles.user_id = 1 OR ... AND ...

I tryed sifters but those (at least for me) are intended to be used exclusively in other Squeel statements. That is, if I state a sifter then I cannot use that to scope ActiveRecords because that sifter returns a Squeel::Nodes::Predicate object instead of an ActiveRecord::Relation.

score 1 · Accepted Answer · answered Jun 26 '12 at 12:03

You have to drop down into more raw AREL for OR operations

def self.scope_method_name(user)
  t = arel_table
  where(
    (t[:user_id].eq(user.id).or(
    t[:blah].eq('otherthing')
      ).and([:bleh].eq('thirdthing'))
    )
end

Or something along those lines.

score 0 · Answer 2 · answered Aug 14 '13 at 14:36

You can chain scopes like Article.by_author(user).by_editor() but this joins all the conditions with ANDs. So, to get around this, you can write individual scopes (not chaining them) using Squeel like:

class Article < ActiveRecord::Base

  scope :by_author, ->(user) { where{author_id == user.id} }
  scope :by_editor, ->(user) { where{editor_id == user.id} }
  scope :by_title, ->(token) { where{title =~ "%#{token}%"} }
  scope :by_author_or_editor, ->(user) { where{(author_id == user.id)|(editor_id == user.id)} }
  scope :by_author_or_editor_and_title, ->(user, token) { where{((author_id == user.id)|(editor_id == user.id))&(title =~ "%#{token}%")} }
end

Or you can use sifters:

class Article < ActiveRecord::Base

  sifter :sift_author do |user|
    author_id == user.id
  end

  sifter :sift_editor do |user|
    editor_id == user.id
  end

  sift :sift_title do |token|
    title =~ "%#{token}%"
  end

  scope :by_author, ->(user) { where{sift :sift_author, user} }
  scope :by_editor, ->(user) { where{sift :sift_editor, user} }
  scope :by_title, ->(token) { where{sift :sift_title, token} }
  scope :by_author_or_editor, -> (user) { where{(sift :sift_author, user)|(sift :sift_editor, user)} }
  scope :by_author_or_editor_and_title, ->(user, token) { where{((sift :sift_author, user)|(sift :sift_editor, user))&(sift :sift_title, token)} }
end

This gives you your scopes that return an ActiveRecord::Relation, so you can in theory further chain them.

Is it possible to "add" SQL clauses related to a `scope` method in a `where` method / clause?

2 Answers2