Complex SQL sort on single attribute

Rails
1

I have a list of dates that currently looks like this:

2/3/08
2/5/08

How can I modify to list the closest upcoming date first and list
subsequent dates in chronological order thereafter, and finally list
all objects with date nil after all of the previously listed ones that
have set dates?

So it would ideally look like this:

2/3/08
2/5/08

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.

2

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

3

U can order by Id desc. Refer ar docs.

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

4

Phil T. 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")

–
Phlip

5

Try sort by upcoming date descending.

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

6

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

7

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.

8

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

2/4/08
2/25/08
3/30/08

This puts nil at the end like I want, but the closest upcoming dates

are at the end of the list–I want them to be first
def index
@tasks = Task.find(:all, :order => “due DESC”)
end

3/30/08
2/25/08
2/4/08

So here’s how I’d like them to display

2/4/08
2/25/08
3/30/08

So it looks like I want to order_by “due ASC” but it seems I need

some sort of logic to put the nil dates after the non-nil dates. I
hope this clarifies.

Thanks for the help.

9

how about when your accessing your @tasks object can’t you just skip the
values with null for “due” ?

<%[email protected] do |task|%>
<%=if !task.due.nil? %>
<%=task.name%>
<%end%>
<%end%>

10

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…

11

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.

12

well this is horrible but would work:

@task = Task.find(:all, :order => “due ASC”)

<%[email protected] do |task|%>
<%if !task.due.nil? %>
<%=task.name%>
<%end%>
<%end%>

<%[email protected] 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

13

On Feb 17, 2008, at 12:57 PM, goodcoffee wrote:

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

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

14

Wow, with the help of a Philip, a Phil, and a Phillip, this now works
as desired. thank you so much!

How could I have forgot the good old COALESCE (kidding, obscure SQL to
me!)

You guys are the best–thanks!