How to prettify/Railsify this SQL in will_paginate?

Rails
1

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) ?

2

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
[email protected]

3

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!