# How to prettify/Railsify this SQL in will\_paginate?

**URL:** <https://www.ruby-forum.com/t/how-to-prettify-railsify-this-sql-in-will-paginate/180008>\
**Category:** Rails\
**Created:** [December 9, 2009, 4:45pm UTC](https://www.ruby-forum.com/t/how-to-prettify-railsify-this-sql-in-will-paginate/180008 "2009-12-09T16:45:31Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![trevoke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ee7513/32.png) [@trevoke](https://www.ruby-forum.com/u/trevoke)\
**Post date:** [December 9, 2009, 4:45pm UTC](https://www.ruby-forum.com/t/how-to-prettify-railsify-this-sql-in-will-paginate/180008/1 "2009-12-09T16:45:31Z")

</div>

I have the following code in my model:

class DictatedExam \< ActiveRecord::Base  
belongs\_to :patient

def self.search\_by\_name(search, page)  
query = %Q{ SELECT DE.\* FROM dictated\_exams DE  
INNER JOIN patients P on P.id = DE.patient\_id  
WHERE CONCAT(last, ', ', first) like ? }  
paginate\_by\_sql [query, “%#{search}%”], :per\_page =\> 30, :page =\>  
page,  
:order =\> ‘dictated\_date asc’

```
#paginate :per_page => 30, :page => page,
# :conditions => ['patient_name like ?', "%#{search}%"],
# :order => 'dictated_date asc'

```

end  
end

I used to have the patient\_name stored directly in this table, as  
indicated by the commented ‘paginate’ statement. I moved it into its own  
table. Table name: Patient. Columns include “Last” and “First” for last  
name and first name… And now I’m not sure how to search for a patient  
name through this table!  
I know I can do this:

> > DictatedExam.find(some\_id).patient.last  
> > =\> Patient’s last name

I am not a very big fan of pure SQL in Rails (portability and so forth),  
especially some odd command like CONCAT. Is there a way to do this in  
Rails, in the will\_paginate plugin (which does things with its :finder  
option, for instance, which I don’t understand) ?

---

<div class="post-metadata">

**Author:** ![trevoke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ee7513/32.png) [@trevoke](https://www.ruby-forum.com/u/trevoke)\
**Post date:** [December 9, 2009, 6:05pm UTC](https://www.ruby-forum.com/t/how-to-prettify-railsify-this-sql-in-will-paginate/180008/2 "2009-12-09T18:05:10Z")

</div>

Aldric G. wrote:

> I have the following code in my model:
> 
> class DictatedExam \< ActiveRecord::Base  
> belongs\_to :patient
> 
> def self.search\_by\_name(search, page)  
> query = %Q{ SELECT DE.\* FROM dictated\_exams DE  
> INNER JOIN patients P on P.id = DE.patient\_id  
> WHERE CONCAT(last, ', ', first) like ? }  
> paginate\_by\_sql [query, “%#{search}%”], :per\_page =\> 30, :page =\>  
> page,  
> :order =\> ‘dictated\_date asc’

Yuck! Besides other problems, CONCAT() is a mySQL abomination –  
standard SQL uses the || operator (and so will mySQL if you turn on ANSI  
mode).

How about  
if search.include? ', ’  
last, first = \*(search.split ', ')  
conditions = [‘last like ? and first like ?’, “%#{last}”, “#{first}%”]  
else  
conditions = [‘last like :search or first like :search’, {:search =\>  
“%#{search}%”}]  
end

DictatedExam.find :all, :joins =\> :patients, :conditions =\> conditions  
?

Best,  
–Â  
Marnen Laibow-Koser  
[http://www.marnen.org](http://www.marnen.org)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![trevoke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ee7513/32.png) [@trevoke](https://www.ruby-forum.com/u/trevoke)\
**Post date:** [December 9, 2009, 6:57pm UTC](https://www.ruby-forum.com/t/how-to-prettify-railsify-this-sql-in-will-paginate/180008/3 "2009-12-09T18:57:45Z")

</div>

Marnen Laibow-Koser wrote:

> How about  
> if search.include? ', ’  
> last, first = \*(search.split ', ')  
> conditions = [‘last like ? and first like ?’, “%#{last}”, “#{first}%”]  
> else  
> conditions = [‘last like :search or first like :search’, {:search =\>  
> “%#{search}%”}]  
> end

Much more elegant!  
I finished it up with  
paginate :per\_page =\> 30, :page =\> page, :joins =\> :patient,  
:conditions =\> conditions,  
:order =\> ‘dictated\_date asc’  
“:joins =\> :patients” generated an error, so I guess the association is  
through the model name, not table name?

My lesson here is, I think, to use proper Ruby first, and -then- to look  
for “the Rails way”… I even learned a thing or two about queries today.

Thank you!