I have a list of dates that currently looks like this:
I assume this would be an argument to my index/find all method, anyone know how I should approach this or get it to work?
Please advise.
for example:
products = Product.find(:all, :conditions => ["category = ?", params[:category]], :limit => 1000, :order_by => upcoming_date DESC")
Phil Tayo wrote:
products = Product.find(:all, :conditions => ["category = ?", params[:category]], :limit => 1000, :order_by => upcoming_date DESC")
That might be leaner going through method_missing and coming out as...
products = Product.find_all_by_category(params[:category], :limit => 1000, :order_by => upcoming_date DESC")
Thanks for the replies--though I'm still stuck. I don't think I need to use 'category' or 'limit' for what I'm trying to do.
# This puts the closest due date at the top, like I want, but it still puts nil dates ahead of it def index @tasks = Task.find(:all, :order => "due ASC") end
so you'd like to exclude the null dates from the results returned then ?
The SQL would be this:
"SELECT * FROM tasks WHERE due IS NOT NULL ORDER BY due ASC"
so I guess the ruby syntax would be:
@task = Task.find(:all, :conditions=>['due IS NOT NULL'], :order => "due ASC")
Phil! Thank you--this is so close. I did as you suggested and now
the closest upcoming dates are listed at the top (win!). But the nil
dates are not listed at all--I want them to be listed, just after the
results that have defined due dates. Thus the 'Complex SQL sort on
single attribute.' 
I've seen other Rails apps do it, so I know it's possible. I just don't have access to their source to see how they structured their SQL ORDER BY and my Ruby/SQL is obviously not up to par to accomplish this.
Can I combine two SQL queries together to achieve this functionality? The first would be the one you suggested, and the second would list only nil dates? Or is there a way to modify your suggestion to include the second operation, appended to the end of the first, as well?
I'm a lot closer than I was last week so I'm optimistic. Thanks for the help--seems like we're just one small tweak away from the desired behavior.
I'm a lot closer than I was last week so I'm optimistic.
ah but you should always be optimistic.
Plus:
You've seen it done so it's possible, it's just a matter of finding out how to do it. I'll have a think. Hopefully someone else will be quicker than me though....
how about when your accessing your @tasks object can't you just skip the values with null for "due" ?
<%=@tasks.each do |task|%> <%=if !task.due.nil? %> <%=task.name%> <%end%> <%end%>
But isn't that effectively the same as the original SQL statement you suggested? I don't want to omit the nil dates, I just want to list them after the objects with set dates (which should be listed in date ASC order).
Thank you for your patience.
well this is horrible but would work:
@task = Task.find(:all, :order => "due ASC")
<%=@tasks.each do |task|%> <%if !task.due.nil? %> <%=task.name%> <%end%> <%end%>
<%=@tasks.each do |task|%> <%if task.due.nil?%> <%=task.name%> <%end%> <%end%>
so, ahem, first you loop through once and print out all the "due" dates that aren't null in ASC order, then, ahem, you loop through AGAIN and print out all the "due" dates that are null...
bit messy, bit horrible, bit inefficient but i think it does what you want :-s
Try something like this:
:order => "COALESCE(due, '2100-01-01') ASC"
What you're doing here is saying "when due is null, substitute '2100-01-01' and sort the list". Substitute any great big date you want instead of '2100-01-01'.
Peace, Phillip
Wow, with the help of a Philip, a Phil, and a Phillip, this now works as desired. <caps>thank you so much!</caps>
How could I have forgot the good old COALESCE (kidding, obscure SQL to me!)
You guys are the best--thanks!
