Hi all,
I’m having a little problem with find() methods in a project I’m
working on. Basically I’m working on a time management system that
assigns each task a priority (an integer from 1 to 10) based on
various factors (time until deadline, whether other tasks are
dependent upon it), and I need to order a list of the tasks by their
priority.
The ‘priority’ of a task isn’t actually a database column - it’s
calculated by the following method in my Task model:
def priority
allTasks = Task.find_all_incomplete
highest = self.time_left #pretty arbitrary, but the ‘highest
priority’ item cannot be any greater than this by definition.
allTasks.each do |thisTask|
if thisTask.time_left < highest
highest = thisTask.time_left
end
end
return ((self.time_left / highest) * 10).to_i
end
I’ve then tried to use the following arguments to the find() method in
my controller:
@tasks = Task.find(:all,
:order => ‘priority DESC’,
:limit => 10 )
However, this raises the following exception:
ActiveRecord::StatementInvalid in TodoController#index
Mysql::Error: Unknown column ‘priority’ in ‘order clause’: SELECT *
FROM tasks ORDER BY priority DESC LIMIT 10
This is fair enough, I suppose - the order property takes a SQL
fragment, and as the ‘priority’ attribute of the Task model isn’t a
database column, it chokes on it. Therefore, is there anyway to order
a the results of a find statement by an attribute of the model that is
not a database column?
Cheers!
Tim
Tim,
It looks like the priority calculation just manipulates the time_left
such that the incomplete task with the largest time_left has the
highest priority. Why can’t you just order by time_left?
Alternatively, you could add a priority column to your table and then
use a callback like before_save to ensure the priorities are kept up
to date. That’s going to be vastly more efficient when you are
displaying a list of tasks.
Alternatively #2, the one unknown in your priority method is highest
time_left value for incomplete tasks. You could store just this value
as a class variable making the priority calculation trivial. For this
method you need a way to initialize the class variable the first time
its accessed, then keep it up to date with a callback method, use sort
by time_left for your SQL queries, and then call the slimmed down
priority method at display time.
Aaron
On Jun 11, 8:43 am, Aaron [email protected] wrote:
Hey Aaron,
It looks like the priority calculation just manipulates the time_left
such that the incomplete task with the largest time_left has the
highest priority. Why can’t you just order by time_left?
Hmmm… the trouble here is that time_left is also a method of the
Task class, rather than a database column.
Alternatively, you could add a priority column to your table and then
use a callback like before_save to ensure the priorities are kept up
to date. That’s going to be vastly more efficient when you are
displaying a list of tasks.
This makes a lot of sense, actually - there’s a fair few methods of
the task class that dynamically calculate values based on database
values in this way, so caching them in the database would probably be
sensible. The trick in that case is writing them back to the database
in such an order that they are kept up to date (if priority and
time_left are both calculated at run-time, and both need writing back
to the database, but priority is calculated based upon time_left, I
need to make sure that time_left is calculated and written back to the
database first, presumably). Also, would this not have a pretty
significant efficiency cost (with a non-trivial number of tasks) when
saving records, as the priority and time_left of all the database
records would need to be updated en masse whenever one record was
saved?
Thanks for your help!
Cheers,
Tim
Tim Cowlishaw wrote:
Therefore, is there anyway to order the results of a find
statement by an attribute of the model that is not a
database column?
Task.find(:all, :limit => 10).sort_by { |t| t.priority }
Probably not very efficient, but it can be done this way.
I wholly agree with Aaron – identify if your application will perform
more reads than writes, and design your application accordingly. It’s
difficult task, but gets easier with time. Good luck.
Dude seriously look at moving to Postgresql and writing a view in SQL
to deal with this, it’ll be a ton faster than getting rails to do it.
I love Ruby but Active Record ain’t the most efficient beast in the
world.
If there is one thing databases are really good at it’s storing and
retrieving data.
I’d definatly be using a view for that, and simply then using a
find_by_sql to lookup on that view.
Much cleaning in your controller/model code and much faster to
retrieve as well.
Cam
On Jun 12, 2:59 am, Daniel W. [email protected]
Also, would this not have a pretty
significant efficiency cost (with a non-trivial number of tasks) when
saving records, as the priority and time_left of all the database
records would need to be updated en masse whenever one record was
saved?
The answer really depends on your application. The choice is between
calculate on write, or calculate on read. Will you be reading more
often than writing, or writing more often than reading? The priority
method in your original post is going to be really inefficient if you
are displaying a page with multiple records. The call to priority for
every task will do a find of all incomplete tasks and search through
them. With the mass update you can do a single find, update the
records, and write them back out.
With either method I would look for ways to make the calculation less
painful. For example, maybe you can save the highest time so you
don’t always have to search for it.
Aaron