# Pagination :conditions not working - MySQL v. PostgreSQL, Ra

**URL:** <https://www.ruby-forum.com/t/pagination-conditions-not-working-mysql-v-postgresql-ra/60618>\
**Category:** Rails\
**Created:** [January 9, 2006, 10:37pm UTC](https://www.ruby-forum.com/t/pagination-conditions-not-working-mysql-v-postgresql-ra/60618 "2006-01-09T22:37:07Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![seanhussey](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/a88e57/32.png) [@seanhussey](https://www.ruby-forum.com/u/seanhussey)\
**Post date:** [January 9, 2006, 10:37pm UTC](https://www.ruby-forum.com/t/pagination-conditions-not-working-mysql-v-postgresql-ra/60618/1 "2006-01-09T22:37:07Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![seanhussey](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/a88e57/32.png) [@seanhussey](https://www.ruby-forum.com/u/seanhussey)\
**Post date:** [January 9, 2006, 10:46pm UTC](https://www.ruby-forum.com/t/pagination-conditions-not-working-mysql-v-postgresql-ra/60618/2 "2006-01-09T22:46:13Z")

</div>

On 1/9/06, Sean H. [removed\_email\_address@domain.invalid](mailto: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](http://techno-weenie.net)

---

<div class="post-metadata">

**Author:** ![seanhussey](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/a88e57/32.png) [@seanhussey](https://www.ruby-forum.com/u/seanhussey)\
**Post date:** [January 9, 2006, 11:06pm UTC](https://www.ruby-forum.com/t/pagination-conditions-not-working-mysql-v-postgresql-ra/60618/3 "2006-01-09T23:06:16Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![seanhussey](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/a88e57/32.png) [@seanhussey](https://www.ruby-forum.com/u/seanhussey)\
**Post date:** [January 9, 2006, 11:13pm UTC](https://www.ruby-forum.com/t/pagination-conditions-not-working-mysql-v-postgresql-ra/60618/4 "2006-01-09T23:13:34Z")

</div>

That helps. I went with this:

:conditions =\> [LOWER(name) LIKE ?’ …

Thank you!

Sean