Home > Mobile >  Rails/SQL combine where conditions for rails scope
Rails/SQL combine where conditions for rails scope

Time:11-18

I want to get total count of items with language_id other than [30, 54] and language_id: nil

LOCALES = {
  non_arabic_languages: {
    id: [30, 54]
  }
}

  scope :non_arabic_languages, -> { where.not(language_id: LOCALES[:non_arabic_languages][:id]) || where(language_id: nil) }

This example predictably returns first part, so I only get non arabic items. && works wrong as well. How may I combine it? We'll be thankful for the advice!

CodePudding user response:

You're falling into a common trap where you confuse logical operations in Ruby with actually creating SQL via the ActiveRecord query interface.

Using || will return the first truthy value:

where.not(language_id: LOCALES[:non_arabic_languages][:id]) || where(language_id: nil) 

Which is the ActiveRecord relation returned by where.not(language_id: LOCALES[:non_arabic_languages][:id]) since everything except false and nil are truthy in Ruby. || where(language_id: nil) is never actually evaluated.

Support for .or was added in Rails 5. In previous versions the most straight forward solution is to use Arel or a SQL string:

scope :non_arabic_languages, -> { 
  non_arabic_languages_ids = LOCALES[:non_arabic_languages].map { |h| h[:id] }
  where(arel_table[:language_id].not_in(non_arabic_languages_ids ).or(arel_table[:language_id].eq(nil)))
}

I would leave a tagging comment (like @fixme) so that you fix this after upgrading to 5.0 or consider a better solution that doesn't involve hardcoding database ids in the first place like for example using a natural key.

  • Related