# Ordering find() queries by non-database properties **URL:** \ **Category:** Rails\ **Created:** [June 11, 2007, 1:25am UTC](https://www.ruby-forum.com/t/ordering-find-queries-by-non-database-properties/106475 "2007-06-11T01:25:28Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![Tim\_Cowlishaw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/848f3c/32.png) [@Tim\_Cowlishaw](https://www.ruby-forum.com/u/Tim_Cowlishaw)\ **Post date:** [June 11, 2007, 1:25am UTC](https://www.ruby-forum.com/t/ordering-find-queries-by-non-database-properties/106475/1 "2007-06-11T01:25:28Z")
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 ---
**Author:** ![Tim\_Cowlishaw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/848f3c/32.png) [@Tim\_Cowlishaw](https://www.ruby-forum.com/u/Tim_Cowlishaw)\ **Post date:** [June 11, 2007, 9:44am UTC](https://www.ruby-forum.com/t/ordering-find-queries-by-non-database-properties/106475/2 "2007-06-11T09:44:08Z")
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 ---
**Author:** ![Tim\_Cowlishaw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/848f3c/32.png) [@Tim\_Cowlishaw](https://www.ruby-forum.com/u/Tim_Cowlishaw)\ **Post date:** [June 11, 2007, 10:44am UTC](https://www.ruby-forum.com/t/ordering-find-queries-by-non-database-properties/106475/3 "2007-06-11T10:44:18Z")
On Jun 11, 8:43 am, Aaron [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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 ---
**Author:** ![Tim\_Cowlishaw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/848f3c/32.png) [@Tim\_Cowlishaw](https://www.ruby-forum.com/u/Tim_Cowlishaw)\ **Post date:** [June 11, 2007, 6:59pm UTC](https://www.ruby-forum.com/t/ordering-find-queries-by-non-database-properties/106475/4 "2007-06-11T18:59:04Z")
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. - Daniel ---
**Author:** ![Tim\_Cowlishaw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/848f3c/32.png) [@Tim\_Cowlishaw](https://www.ruby-forum.com/u/Tim_Cowlishaw)\ **Post date:** [June 12, 2007, 8:04am UTC](https://www.ruby-forum.com/t/ordering-find-queries-by-non-database-properties/106475/5 "2007-06-12T08:04:39Z")
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. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) ---
**Author:** ![Tim\_Cowlishaw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/848f3c/32.png) [@Tim\_Cowlishaw](https://www.ruby-forum.com/u/Tim_Cowlishaw)\ **Post date:** [June 11, 2007, 5:54pm UTC](https://www.ruby-forum.com/t/ordering-find-queries-by-non-database-properties/106475/6 "2007-06-11T17:54:44Z")
> 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