In Ruby/Rails, How to Sort on a Date Value Where the Date Can Sometimes Be Null

In ruby/rails, how do I sort on a date value where the date can sometimes be null?

If you just want to drop entries without date, simplest solution -

ar.select(&:date).sort_by(&:date)

adding nils at the end can be done with

ar.select(&:date).sort_by(&:date) + ar.reject(&:date)

If you happen to know the range of possible dates, you can be fine with something like

ar.sort_by{|e| e.date || Date.new(9999)}

BTW, reduce in your statement can be changed to (IMHO) more clear

@games = @teams.map(&:games).flatten

Set datetime to nil in Rails

Managed to get it done using:

@object.update_column(:assignment_time, nil)

How to use sort_by when you have a nil value and multiple sorts

If you have a preferred replacement value for the possibly missing triggered date, which could be call.scheduled_date.end_of_day as you mentioned in a comment, you can do the following:

calls.sort_by! { |call| [call.scheduled_date, call.triggered_date || call.scheduled_date.end_of_day] }

Then the second entry of the array will be triggered_date if present and call.scheduled_date.end_of_day if the triggered_date is nil.

Old answer:

If you want the nil-part to just be ignored by your sorting (with the effect that those entries with triggered_date == nil are considered "smaller" than those with present triggered_date, you can just do the following:

calls.sort_by! { |call| [call.scheduled_date, call.triggered_date].compact }

compact removes nil values from an array. Thus, the calls without triggered_date only have a size 1 array for comparison while the others have size 2. Shortness seems to win here, so these are sorted in front of any other.

Take care though if scheduled_date may be nil, too! Then this approach can lead to really weird results.

edited: Added solution if possible fallbacks are known

How to use sort_by with values that could be nil?

Use this syntax,

starred.sort_by { |a| [a ? 1 : 0, a] }

When it has to compare two elements, it compares an arrays. When Ruby compares arrays (calls === method), it compares 1st element, and goes to the 2nd elements only if the 1st are equal. ? 1 : 0 guarantees, that we'll have Fixnum as 1st element, so it should be no error.

If you do ? 0 : 1, nil will appear at the end of array instead of beginning.
Here is an example:

irb> [2, 5, 1, nil, 7, 3, nil, nil, 4, 6].sort_by { |i| [i ? 1 : 0, i] }
=> [nil, nil, nil, 1, 2, 3, 4, 5, 6, 7]

Source.

Rails: Sort nils to the end of a scope?

I'm a bit late to the game but this just came up again and the solution really isn't that difficult.

A portable solution is to use a CASE to turn NULLs into something that will go to the end. If your ratings are non-negative, then -1 is a good choice:

order('case when average_rating is null then -1 else average_rating end desc')

Using COALESCE instead will work in many databases and is a lot less noisy than a CASE:

order('coalesce(average_rating, -1) desc')

If you wanted an ASC sort then the above approaches would put NULLs at the beginning. If you wanted them at the end then you'd use something bigger than your highest rating instead of -1. For example, if you rated things from one to five, you could use 11 to replace NULLs:

order('case when average_rating is null then 11 else average_rating end asc')
order('coalesce(average_rating, 11) asc')

Most people would use 10 but sometimes you need to get a little bit louder so ours go to 11.

You can't really depend on the database putting NULLs at the beginning or end so it is best to be explicit even if you're just reminding your future-self that you've handled the NULL case already.

ORDER BY columns that are sometimes empty using Active Record & Rails

You're running in to a letter case problem: Your names are all capitalized, but the emails are lowercase, and with most collations, uppercase letters come before lowercase. Check out this trivial example:

#= select * from (values ('b'), ('B'), ('a'), ('A')) t (letter);
 letter
--------
 b
 B
 a
 A

#= select * from (values ('b'), ('B'), ('a'), ('A')) t (letter) order by letter;
 letter
--------
 A
 B
 a
 b

So your query is actually working perfectly, it's just that cxxr@person.com sorts after Josh. To avoid this, you can sort by the lowercase value. Here's a simple version of the data you have:

#= select * from volunteers;
 first_name | last_name |       email
------------+-----------+--------------------
 Josh       | Broger    | jcool@person.com
 Josh       | Kenton    | aj@person.com
 ∅          | ∅         | cxxr@person.com
 Josh       | Broger    | broger@person.com
 Alex       | Diego     | a.diego@person.com

Then to sort using the coalesce you're after:

#= select * from volunteers order by lower(coalesce(first_name, email));
 first_name | last_name |       email
------------+-----------+--------------------
 Alex       | Diego     | a.diego@person.com
 ∅          | ∅         | cxxr@person.com
 Josh       | Broger    | broger@person.com
 Josh       | Broger    | jcool@person.com
 Josh       | Kenton    | aj@person.com

Or for your full version using ActiveRecord:

Volunteer
  .joins(:volunteer_lists)
  .where(
    "(volunteer_lists.organizer_id = ? AND organizer_type = 'Organization') OR (volunteer_lists.organizer_id IN (?) AND organizer_type = 'Collaborative')",
    organization.id, collaboratives
  )
  .order('LOWER(COALESCE("volunteers"."first_name", "volunteers"."last_name", "volunteers"."email"))')

Graceful date-parsing in Ruby

My preferred approach these days is to use Dry::Types for type coercions and Dry::Monads for representing errors.

require "dry/types"
require "dry/monads"
Dry::Types.load_extensions(:monads)
Types = Dry::Types(default: :strict)

Types::Date.try("2021-07-27T12:23:19-05:00")
# => Success(Tue, 27 Jul 2021)

Types::Date.try("foo")
# => Failure(ConstraintError: "foo" violates constraints (type?(Date, "foo"))

Params comes back as NULL sometimes

Could this be a date localization problem? The date 09/20/2011 and 09/25/2011 are clearly in mm/dd/yyyy format but is they are parsed as dd/mm/yyyy format the would be invalid. The might explain why some dates get through and others do not.

In the case that does actually insert values into the database the dates given are valid in either format - 9/2/2011 is a valid value whichever way you interpret either way, although if the dates are getting switched the values inserted into the database won't be as intended. Did you want to book a vacation from 9th January-9th February or 1-2 September?

Rails DateTime gives invalid date sometimes and not others

Try using to_datetime:

date.to_datetime
# => Fri, 06 Mar 2015 13:00:00 +0000

Also if you read the documentation for DateTime#strptime, here. It states:

Parses the given representation of date and time with the given
template, and creates a date object.

Its important to note that the template sequence must match to that of input string sequence, which don't in your case - leading to error.

Update

Using to_datetime over second example will generate

ArgumentError: invalid date

This is because it expects the date to be in dd-mm-yy format. Same error will be raised for DateTime.parse as to_datetime is nothing but an api for the later. You should use strptime in-case of non-standard custom date formats. Here:

date2 = "03:30pm 05/28/2015"
DateTime.strptime(date2, "%I:%M%p %m/%d/%Y")
# => Thu, 28 May 2015 15:30:00 +0000

---

Related Topics