Rails - Jan 2006 - Pagination :conditions not working - MySQL v. PostgreSQL, Rails abstraction v. embedded SQL

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

On 1/9/06, Sean Hussey <seanhussey@gmail.com>
wrote:
> 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,
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

Sean Hussey 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

-- 
Posted via http://www.ruby-forum.com/.

That helps.  I went with this:

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

Thank you!

Sean

On 1/9/06, Rick Olson <technoweenie@gmail.com>
wrote:
> On 1/9/06, Sean Hussey <seanhussey@gmail.com> wrote:
> > 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,
>
> 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
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>