How to search in this activerecord example?

Question

Two models:

Invoice

  :invoice_num        string
  :date               datetime
  .
  .
  :disclaimer_num     integer (foreign key)

Disclaimer

  :disclaimer_num     integer
  :version            integer
  :body               text

For each disclaimer there are multiple versions and will be kept in database. This is how I write the search (simplified):

scope = Invoice.scoped({ :joins => [:disclaimer] })
scope = scope.scoped :conditions => ["Invoice.invoice_num = ?", "#{params[:num]}"]
scope = scope.scoped :conditions => ["Disclaimer.body LIKE ?", "%#{params[:text]}%"]

However, the above search will search again all versions of the disclaimer. How can I limit the search to only the last disclaimer (i.e. the version integer is the maximum).

Please note:

Invoice does not keep the version number. New disclaimers will be added to disclaimer table and keep old versions.

Answer 1

Hmm... I might just be stored procedure happy, but I think at this point you'd benefit greatly from a stored procedure (or even a view) that did something like this:

CREATE PROCEDURE GetRecentDisclaimer @BodyFragmentBeingSearched varchar(200) AS SELECT MAX(version), disclaimer_num, body FROM Disclaimer WHERE body LIKE @BodyFragmentBeingSearched GROUP BY disclaimer_num, body

From there, someone's written a blog about how you'd call a stored procedure in Rails and populate ActiveRecord objects, check it out here:

http://nasir.wordpress.com/2007/12/04/stored-procedures-and-rails-part-2/

Answer 2

If you want only the invoices with the latest version from disclaimer, put a condition on the disclaimer_num. And I also suggest creating a helper method in Disclaimer to make the code cleaner in your scope.

class Disclaimer < ActiveRecord::Base
  def latest
    find(:first, :order => "version DESC")
  end
end

scope = scope.scoped :conditions => { :disclaimer_num => Disclaimer.latest }

And I really hope you removed the sql injection prevention code from your scope for brevity.

Answer 3

Add these two conditions (can be done in scope):

"ORDER BY disclaimer.disclaimer_num DESC"
"LIMIT 0, 1"