Using Find based on method rather than table

Ruby
1

I have a method that returns a date that is either the updated_at field
of a Topic or one of it’s reply Posts.

Is there a way to order the results of a find based on the date returned
by this method? When I try this:

Topic.find(:all, :order => ‘lastest_post_date DESC’)

it throws an error, as it looks for a table rather than using the method
I’ve written.

There must be an easy way to do this in Ruby!

2

Jon H. wrote:

Topic.find(:all, :order => ‘lastest_post_date DESC’)

it throws an error, as it looks for a table rather than using the method
I’ve written.

There must be an easy way to do this in Ruby!

Nope!

Firstly, this is an ActiveRecord question, and you should always post to
the
narrowest technical newsgroup for the best answer. Although this
particular
question is easy, don’t take my follow-up as encouragement to abuse this
group!
Try the Ruby on Rails Talk forum at Google Group for general AR and RoR
questions…

In general, find arguments, like :order’s target, must appear in your
database.
(As a field, not a “table”). That’s because .find’s job is to construct
a wicked
long SQL SELECT string, and submit this to your database engine -
sqlite3,
MySQL, etc.

They know nothing about Ruby, so they can’t just reach back into your
program
and find your latest_post_date.

You could put latest_post_date into the database as a field. Do this if
you need
real speed (unlikely in a young project), or if you need the features
discussed
below.

Next, you could replicate the logic from ‘def latest_post_date’, by
rebuilding
it in a raw SQL fragment. This has odds of working:

Topic.find(:all, :include => :posts,
:order => ‘(CASE WHEN topics.created_at > posts.created_at
THEN topics.created_at ELSE posts.created_at END)
DESC’)

Crack an SQL tutorial (and don’t pester a Ruby or Rails forum about
it!) if
that does not work; I wrote it without testing it or nothing. I also
only made a
guess about the contents of your method.

And you might find it easier to simply sort in Ruby, instead of SQL:

Topic.all.sort_by(&:latest_post_date)

That will reach out to your method! However…

One joy of SQL and AR is you can use the :page, :offset, and/or :limit
fields to
create a “sliding window” of results. Sometimes a query might return too
many
results for one HTML window, so you should give the user those familiar
VCR
controls to slide forward and back thru a simulated “database cursor”.

And that sliding window obviously will not work if you :limit and
:offset your
database query, and only then sort it. The VCR controls will not
exactly
appear to show batches of records in the correct order!

Finally, if you indeed need to use the greater of topics.created_at and
posts.created_at, you could write a Post.after_save that “bumps” the
topics.created_at field. Then you don’t even need latest_post_date.