#61 ✓resolved
Łukasz Piestrzeniewicz

Not_blank fails on PostgreSQL

Reported by Łukasz Piestrzeniewicz | March 3rd, 2009 @ 08:28 AM

Using not_blank condition fails on PostgreSQL:


>> User.named_scope :with_email, {:conditions => {:email_not_blank => true}}
=> #<Proc:0x011d9830@/Library/Ruby/Gems/1.8/gems/activerecord-2.1.2/lib/active_record/named_scope.rb:95>
>> User.with_email
ActiveRecord::StatementInvalid: PGError: ERROR:  operator does not exist: character varying <> boolean
LINE 1: ...ULL and "users"."email" != '' and "users"."email" != false))
                                                             ^
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.
: SELECT * FROM "users" WHERE (("users"."email" IS NOT NULL and "users"."email" != '' and "users"."email" != false))

Comments and changes to this ticket

  • limonka

    limonka March 6th, 2009 @ 05:23 AM

    Hi Ben,

    Can you please help me with the problem above?

    Thanks

  • Ben Johnson

    Ben Johnson March 6th, 2009 @ 05:27 AM

    • State changed from “new” to “open”

    What does the correct SQL look like for PostgreSQL?

  • limonka

    limonka March 6th, 2009 @ 05:37 AM

    The correct one is: SELECT * FROM "users" WHERE ("users"."email" IS NOT NULL and "users"."email" != '')


    without: "users"."email" != false in the end

  • Ben Johnson

    Ben Johnson March 6th, 2009 @ 05:57 AM

    i see what's going on. I was going with the typical "blank?" method mentality when doing this. I'll just check if its a boolean or not and write the proper SQL.

  • limonka

    limonka March 6th, 2009 @ 07:13 AM

    My solution is replacing a code in searchlogic\lib\searchlogic\condition\blank.rb
    with code:

    @@@ruby module Searchlogic module Condition

    class Blank < Base
  self.value_type = :boolean

  class << self
    def condition_names_for_column
      super + ["is_blank"]
    end
  end

  def to_conditions(value)
    case column_sql.type
    when  :boolean
        "(#{column_sql} IS NULL or #{column_sql} = '' or #{column_sql} = false)" if value == true
        "(#{column_sql} IS NOT NULL and #{column_sql} != '' and #{column_sql} != false)" if value == false
    else
      "(#{column_sql} IS NULL or #{column_sql} = '')" if value == true
      "(#{column_sql} IS NOT NULL and #{column_sql} != '')" if value == false
    end
  end
end

    end end

    

Am I correct?
Thanks a lot for help Ben :)
  • Ben Johnson

    Ben Johnson March 23rd, 2009 @ 03:03 AM

    • State changed from “open” to “resolved”

    Thanks for this, I apologize for the delay. This has been applied.

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

Provides common named scopes and object based searching.

People watching this ticket

Tags

Pages