Forum: Ruby on Rails Pagination :conditions not working - MySQL v. PostgreSQL, Ra

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Sean H. (Guest)
on 2006-01-09 23:37
(Received via mailing list)
Hi everyone,

I have this code:

    @person_pages, @people = paginate :person, :per_page => 20,
      :conditions => [ "username LIKE ? OR first_name LIKE ? OR
last_name LIKE ? OR preferred_name LIKE ?",
                     "%" + params[:q].downcase + "%",
                     "%" + params[:q].downcase + "%",
                     "%" + params[:q].downcase + "%",
                     "%" + params[:q].downcase + "%" ], :order =>
'username DESC'

Works like a charm when developing on my local machine.  OSX, Rails 1.0,
MySQL.

When I move the code to production, which uses postgresql, the search
doesn't work correctly.  I think it's only getting results flagged off
of the username.

When I search for "dav" hoping to see lots of Daves and Davids,
locally, I get 20+ results, which is correct.  Production, I get 4,
and all have "dav" in the username, lowercase.  I think it's a
postgresql thing.

The optimal thing would be to get rid of the SQL so that Rails can
handle the specifics for me.  How can I do that?  What's the best way
to set :conditions to search through those columns?

Thanks,

Sean
Rick O. (Guest)
on 2006-01-09 23:46
(Received via mailing list)
On 1/9/06, Sean H. <removed_email_address@domain.invalid> wrote:
>                      "%" + params[:q].downcase + "%" ], :order =>
> and all have "dav" in the username, lowercase.  I think it's a
> postgresql thing.
>
> The optimal thing would be to get rid of the SQL so that Rails can
> handle the specifics for me.  How can I do that?  What's the best way
> to set :conditions to search through those columns?
>
> Thanks,

The immediate problem is that postgresql needs ILIKE to do a case
insensitive query.  Do this:

:conditions => ['LOWER(name) = ?' "%#{params[:q].downcase}%"]

--
rick
http://techno-weenie.net
Kevin O. (Guest)
on 2006-01-10 00:06
Sean H. wrote:
>                      "%" + params[:q].downcase + "%",

I would avoid doing this.  It will probably work fine here, but it will
come back to haunt you later.

Use the ruby way... "%#{params[:q].downcase}%"

"%" + nil + "%" yields all sorts of errors
"%#{nil}%" yields "%%"

_Kevin
Sean H. (Guest)
on 2006-01-10 00:13
(Received via mailing list)
That helps.  I went with this:

:conditions => [LOWER(name) LIKE ?' ....

Thank you!

Sean
This topic is locked and can not be replied to.