Complex SQL sort on single attribute

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")

U can order by Id desc. Refer ar docs.

Http://www.rubyplus.org
Free Ruby & Rails screencasts

Try sort by upcoming date descending.

Http://www.rubyplus.org
Free Ruby & Rails screencasts

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.' :slight_smile:

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!