How to add NULL LAST to sorts

[justin808]

I need NULL results to go last.

This is simple to do in a SQL statement. Just list "NULL LAST" right after the order predicate.

Where is the right place to put this?

Also, in the code for evaluate, I don't think relation.except(:order) is necessary, as the reorder takes care of removing the old order.

        def evaluate(search, opts = {})
          viz = Visitor.new
          relation = @object.where(viz.accept(search.base))
          if search.sorts.any?
            relation = relation.except(:order).reorder(viz.accept(search.sorts))
          end
          opts[:distinct] ? relation.distinct : relation
        end

[jonatack]

Hi @justin808, you could do it with something like this:

controller#index
@admin_posts = @q.result.except(:order).order("#{@q.sorts.first} NULLS LAST")

I have a longer method called set_sort_order that all my controllers call to do this, though with the new multiple sort feature in the sort_link helper, I could simplify it.

Perhaps we could come up with an API for allowing specifying custom queries like nullif and nulls last.

[justin808]

@jonatack That syntax does not work for the latest ransack, although I don't know what @q is for sure.

This prints out an object reference:

#{@q.sorts.first}

I'm guessing that it's what I have as @search: Here's what I have:

class Manager::IndexPresenter
  def initialize(q, page, exclude_null_aum)
    # ransack conflicts with pagination if no query and going to page 2
    if q.present? && q.is_a?(Hash) && (q.values.all? { |v| v.blank? })
      q = nil
    end
    @q = q
    @page = page
    @exclude_null_aum = exclude_null_aum
  end

  def search
    @search ||= begin
      rel = Manager.joins(:most_recent_form_adv).where(simulated: false).
        by_filed_at_within_one_year
      rel = rel.where.not(form_advs: { aum: nil}) if @exclude_null_aum
      search = rel.search(@q)
      search.sorts = 'most_recent_form_adv_aum desc' if search.sorts.empty?
      search
    end
  end

  def managers
    @managers ||= @search.result.page @page
  end

[jonatack]

Sorry, I've given this same reply several times here in Ransack issues, I meant to write:

result.except(:order).order("#{@search.sorts.first.name} #@search.sorts.first.dir} NULLS LAST")

In the Rails console, if you type @search.sorts.first.methods, you'll see that the first two methods are :name and :dir, which would lead you to poke around and try @search.sorts.first.name and @search.sorts.first.dir.

If you have further questions, have a look at the Ransack source code in Ransack::Helpers::FormHelper#sort_link.

[jonatack]

P.S. With will_paginate I don't encounter any conflicts with Ransack like you mention above.

[justin808]

Hi @jonatack, thanks for the help. @search.sorts.first.name yields this query:

SELECT  "managers".* FROM "managers" INNER JOIN "form_advs" ON "form_advs"."id" = "managers"."most_recent_form_adv_id" WHERE "managers"."simulated" = 'f' AND (form_adv_filled_at >= '2013-10-14 08:51:40.351824')  ORDER BY most_recent_form_adv_aum desc NULLS LAST LIMIT 25 OFFSET 0

The problem is that the sort column is really the ransack alias for the joined column.

Join is on "most_recent_form_adv".

Column to sort on join table is "aum".

So I had to do this, using attr_name

       except(:order).order("#{@search.sorts.first.attr_name} #{@search.sorts.first.dir} NULLS LAST").

[jonatack]

Interesting info with attr_name and glad you got your problem sorted ;)
It might be a good idea to set up an API for adding custom orders like this, if anyone is interested in doing a pull request for that.

[justin808]

I'd agree that this feature at least should be documented, and an API would be better. I'm not familiar enough with Ransack to comment right now. I inherited the Ransack code from another developer.

[3du4]

@justin808 it worked for me also with attr_name. Thank you!

[jonatack]

Please see this new blog post by @radar, our co-maintainer: http://ryanbigg.com/2015/11/open-source-work/

This is free, DIY, OSS software that no one is paid to work on, yet many companies and people use it.

The issue tracker is not for feature requests or personal support. It is for reporting bugs.

If there was "progress" on this feature request, you would see it here.

If the feature is important to you, contribute a PR or pay someone to do it.

Please do not request progress on feature requests without doing anything, because it wastes people's time and makes maintainers want to quit.

Maintainers are short of time to test and fix issues and look at PRs. New features are not a priority. If you want them, roll up your sleeves and contribute them, remembering that the maintainer has to maintain them afterward for free in their spare time.

Sorry for ranting, this is a result of many small things adding up and not just this one, and thank you for your understanding.

[aldrinmartoq]

The solution given by @jonatack does not work if the sort column is an association (in my case a self association).

I got it working with the following code for Postgresql:

search = Model.ransack(…)
…
result = search.result
orders = result.orders.map { |order| order.direction == :asc ? "#{order.to_sql} NULLS FIRST" : "#{order.to_sql} NULLS LAST" }
result = result.except(:order).order(orders.join(", ")) if orders.count > 0

Hope this helps, cheers.

[jordanmaguire]

This is a very old issue but it showed up in my search results when trying to implement this so I figured I'd put a nicer solution here.

From the README you can see instructions for creating a virtual field to sort on, so if you wanted to sort on an attribute called "name" you could do:

In your model:

scope :sort_by_name_nulls_last_asc, -> { order("name ASC NULLS LAST") }
scope :sort_by_name_nulls_last_desc, -> { order("name DESC NULLS LAST") }

in your view:

<th><%= sort_link(@q, :name_nulls_last, "Name") %></th>

[rwxdash]

One issue I found recently is this. If I do this in the controller, the direction is always ASC in the query that runs.

@q = Consent.ransack(params[:q])
@q.sorts = ['consent_given_at desc nulls last'] if @q.sorts.empty?

Consent Load (11.5ms)  SELECT  "consents".* FROM "consents" ORDER BY consent_given_at asc NULLS LAST LIMIT $1 OFFSET $2  [["LIMIT", 100], ["OFFSET", 0]]

[jigarbhatt2711]

This is a very old issue but it showed up in my search results when trying to implement this so I figured I'd put a nicer solution here.

From the README you can see instructions for creating a virtual field to sort on, so if you wanted to sort on an attribute called "name" you could do:

In your model:

scope :sort_by_name_nulls_last_asc, -> { order("name ASC NULLS LAST") }
scope :sort_by_name_nulls_last_desc, -> { order("name DESC NULLS LAST") }

in your view:

<th><%= sort_link(@q, :name_nulls_last, "Name") %></th>

This solution is working. You saved my day. Thanks a lot.

[andrewdesmondm]

One issue I found recently is this. If I do this in the controller, the direction is always ASC in the query that runs.

@q = Consent.ransack(params[:q])
@q.sorts = ['consent_given_at desc nulls last'] if @q.sorts.empty?

Consent Load (11.5ms)  SELECT  "consents".* FROM "consents" ORDER BY consent_given_at asc NULLS LAST LIMIT $1 OFFSET $2  [["LIMIT", 100], ["OFFSET", 0]]

I am having this same issue. Does anyone know the solution to this?

[spovich]

@andrewdesmondm this isn't ideal, but you can apply nulls last by doing a reorder. This works because of active record using lazy evaluation, so only one query is executed with the proper sorting.

@search = scope.ransack(params[:q])
@search.sorts = "foobar asc" if @search.sorts.empty?
result = @search.result
result = result.reorder("#{@search.sorts.first.name} #{@search.sorts.first.dir} NULLS LAST")
@items = result.page(params[:page])

[amirali-ashraf]

Just for the others who might visit this page again, you can add the option for postgres so it puts the nulls either first or last.

Link to code is:

https://github.com/activerecord-hackery/ransack/blob/e13cf7b503d72e9645ee6ce623e10f3f2fd6ec9d/lib/ransack/configuration.rb#L170C8-L170C8

Also you can find the options here:

ransack/lib/ransack/adapters/active_record/context.rb

Line 45 in e13cf7b

case Ransack.options[:postgres_fields_sort_option]

So, you can change the config file to change the behaviour of ransack within your app (initializers/ransack.rb):

Ransack.configure do |config|
  config.postgres_fields_sort_option = :nulls_first # :nulls_last, :null_always_first, :null_always_last
end

[hugolepetit]

Edge case with associations. You would be tempted to use this :

# in model
class MyModel < ApplicationRecord
  has_one :my_association
end

class MyAssociation < ApplicationRecord
  belongs_to :my_model
  scope :sort_by_name_nulls_last_asc, -> { order("name ASC NULLS LAST") }
  scope :sort_by_name_nulls_last_desc, -> { order("name DESC NULLS LAST") }
end

# in view
<th><%= sort_link(@q, :my_model_my_association_name_nulls_last, "Name") %></th>

This will not work. The reason is ransack does not go through associations when trying to get custom ordering scopes. You can find the relevant code here (note the respond_to line) :

ransack/lib/ransack/visitor.rb

Lines 57 to 68 in cc624b9

	def visit_Ransack_Nodes_Sort(object)
	if object.valid?
	if object.attr.is_a?(Arel::Attributes::Attribute)
	object.attr.send(object.dir)
	else
	ordered(object)
	end
	else
	scope_name = :"sort_by_#{object.name}_#{object.dir}"
	scope_name if object.context.object.respond_to?(scope_name)
	end
	end

.

It first tries to validate that we have an attribute whose name is valid and since it receives name_nulls_last, it does try to get a scope that follows the convention sort_by_#{ATTRIBUTE_NAME}_#{DIR} and work with it. The attempt to retrieve the scope is sent to the main model not the associated one which is why it fails.

You can hack your way through it with something along those lines :

# in model
class MyModel < ApplicationRecord
  has_one :my_association
  scope :sort_by_my_association_name_nulls_last_asc, -> { joins(:my_association).order("my_association.name ASC NULLS LAST")
  scope :sort_by_my_association_name_nulls_last_desc, -> { joins(:my_association).order("my_association.name DESC NULLS LAST")
end

class MyAssociation < ApplicationRecord
  belongs_to :my_model
end

# in view
<th><%= sort_link(@q, :my_model_my_association_name_nulls_last, "Name") %></th>

On another note the order clause "my_association.name DESC NULLS LAST" was different on my MySQL system. I went with "ISNULL(my_association.name), my_association.name DESC"