Push all zero values to the end - Ruby on Rails (Postgresql)-CodePudding

I have a table vehicles that has_one vehicle_size. The VehicleSize model has a column in the table size, a String. Here are examples of a size value: 12ft, 19ft, EV. The goal is to sort vehicles based on the size from the vehicle_sizes table.

Here is my current solution:

  def order_by_size(resources)
    return resources unless context.params[:by_size] == 'asc' || context.params[:by_size] == 'desc'

    if context.params[:by_size] == 'desc'
      resources.joins(:vehicle_size).group('vehicle_sizes.size').order('vehicle_sizes.size DESC')
    else
      resources.joins(:vehicle_size).group('vehicle_sizes.size').order('vehicle_sizes.size ASC')
    end
  end

The solution above performs sorting. First, however, I need to push all zero values to the end regardless if the order is desc or asc (* zero means EV or any other string without numbers).

I tried to sort records with .sort { ... }, but it returns an array instead of active relation, with is necessary for me.

Solution where I get an array with sort:

  def order_by_size(resources)
    return resources unless context.params[:by_size] == 'asc' || context.params[:by_size] == 'desc'

    if context.params[:by_size] == 'desc'
      resources.joins(:vehicle_size).group('vehicle_sizes.size').sort do |x, y|
        if x.vehicle_size.size.to_i.zero?
          1
        elsif y.vehicle_size.size.to_i.zero?
          -1
        else
          y.vehicle_size.size.to_i <=> x.vehicle_size.size.to_i
        end
      end
    else
      resources.joins(:vehicle_size).group('vehicle_sizes.size').sort do |x, y|
        if x.vehicle_size.size.to_i.zero?
          1
        elsif y.vehicle_size.size.to_i.zero?
          -1
        else
          x.vehicle_size.size.to_i <=> y.vehicle_size.size.to_i
        end
      end
    end
  end

How can I modify my first or second solution to return an active relation where all String (zeros) will be pushed to the end regardless of sorting? Am I missing something here?

Many thanks for considering my request.

CodePudding user response：

VehicleSize.order(Arel.sql("size = 'EV', size"))

VehicleSize.order(Arel.sql("size = 'EV', size desc"))

This way records with size = EV will be last, but others will be sorted as you need

Result will be relation

If you need specify table name, you can use vehicle_sizes.size instead of size

If you have few values without number (EV and ED here) you can do something like this to avoid hardcode

zero_array = %w[EV ED]

VehicleSize.order(
  VehicleSize.sanitize_sql_for_order([Arel.sql("size IN (?), size DESC"), zero_array])
)

CodePudding user response：

You can add a new field to the order

vehicle_sizes.size = 0, vehicle_sizes.size DESC

vehicle_sizes.size <> 0, vehicle_sizes.size DESC