0

I have a DataMapper class Song (through PostgreSQL) that has the following property: 

property :rating, Float, :required => false

Users can rate songs. The problem is that I want users to browse available songs by rating. Say I have 5 songs, 3 of them have been rated, then something like this:

Song.all(:order => [:rating.desc])

will get me songs in the following order: {null, null, 10, 7, 6} or whatever. Is there a simple way to get these null values to the bottom of the ordering, so that my results will look like {10, 7, 6, null, null}.

I tried

Song.all(:rating.not => nil, :order => [rating.desc]) + song.all(:rating => nil)

but this combines the queries such that my resultset is nil.

An alternative of course is to default a null rating to something like -1, but due to some limitations I'd rather avoid this if possible.

ujifgc
  • 2,215
  • 2
  • 19
  • 21
AlexQueue
  • 6,353
  • 5
  • 35
  • 44
  • What database are you connecting with? MySQL considers NULL values lesser than any other and it shows the order like `{10, 7, 6, null, null}`. – ujifgc Nov 06 '12 at 07:18

1 Answers1

2

You can specify the position of NULl in ORDER BY clause. Check this Sorting Rows. I am not sure in datamapper but the sql will be like this

SELECT select_list FROM table_expression ORDER BY sort_expression DESC NULLS LAST

Nandakumar V
  • 4,317
  • 4
  • 27
  • 47