67

Rails 3.2.1

Is there a way (without squeel) to use the hash syntax of ActiveRecord to construct a != operator?

Something like Product.where(id: !params[:id])

Generates SELECT products.* FROM products WHERE id != 5

Looking for the opposite of Product.where(id: params[:id])

UPDATE

In rails 4 there is a not operator.

Product.where.not(id: params[:id])

Tyler DeWitt
  • 23,366
  • 38
  • 119
  • 196

4 Answers4

93

You can use the following

Product.where('id != ?', params[:id])

Which will generate what you are looking for, while parameterizing the query.

With Rails 4, the following syntax has been added to support not clauses

Product.where.not(id: params[:id])

Add multiple clauses with chaining...

Product.where.not(id: params[:id]).where.not(category_id: params[:cat_id])
Jeremy Moritz
  • 13,864
  • 7
  • 39
  • 43
Dan McClain
  • 11,780
  • 9
  • 47
  • 67
  • 2
    This may be work unexcepted when params[:id] is nil. In rails 3.2.X I use `Product.where('id != ?', params[:id].to_i)` – comme Oct 29 '13 at 06:41
  • In rails 3, please advise, if there are more than one id, then how to do it? ex. Product.where('id != ?', 10000, 10001, 10004, 10035, 10088) – rhunal Jan 02 '18 at 07:33
34

There isn't any built-in way to do this (as of Rails 3.2.13). However, you can easily build a method to help you out:

ActiveRecord::Base.class_eval do
  def self.where_not(opts)
    params = []        
    sql = opts.map{|k, v| params << v; "#{quoted_table_name}.#{quote_column_name k} != ?"}.join(' AND ')
    where(sql, *params)
  end
end

And then you can do:

Product.where_not(id: params[:id])

UPDATE

As @DanMclain answered - this is already done for you in Rails 4 (using where.not(...)).

Weston Ganger
  • 6,324
  • 4
  • 41
  • 39
PinnyM
  • 35,165
  • 3
  • 73
  • 81
  • 1
    PLUS ONE TO BE SURE.... But be sure to test for nulls too.. They are (to my mind) not equal, but the resulting query will NOT return records where the field has null. where_not(:status => 'active') for instance.. – baash05 Mar 08 '13 at 01:14
  • Also to be considered... where_not_or Employee.where(:id=>1).where_not_or(:job_title=>'Awesome', :wages=>10000000).update_all(:job_title=>'Awesome', :wages=>10000000, :updated_at => Time.now) would only update if things changed – baash05 Mar 08 '13 at 01:27
  • 1
    @daveatflow, SQL standard for NULLs is to _never_ return unless specifically checked using IS NULL or IS NOT NULL. So I disagree that calling != should modify this behavior. Adding an `or` method would be greatly beneficial, but at this point using Arel or Squeel is easier than reinventing the wheel. – PinnyM Mar 08 '13 at 03:51
24

Rails 4 has this figured out. So maybe you could just update your rails app

Model.where.not(:id => params[:id])
boulder_ruby
  • 38,457
  • 9
  • 79
  • 100
14

Arel could the one you might want to explore It is included in Rails 3+ I guess

Here How you do it using Arel

Product.where(Product.arel_table[:id].not_eq(params[:id]))

and

Product.where(Product.arel_table[:id].not_eq(params[:id])).to_sql

would generate SQL Like below

SELECT `products`.* FROM `products`  WHERE (`products`.`id` != 1)

Hope this Help

Viren
  • 5,812
  • 6
  • 45
  • 98