CODEWITHSUNDEEP
ruby-on-railspostgresqlsortingip-address
Kashiftufail
Kashiftufail

Reputation: 10885

How i can sort ip address in Rails 4 using postgresql

I have "ip" column in my postgresql table which contain ip addresses. I want to sort these ip addresses in ascending order. Below is data for example

    147.226.211.40
    1.39.80.12
    128.237.199.43

Result should be 

    1.39.80.12
    128.237.199.43
    147.226.211.40

In other way around, so if ip address is a.b.c.d then sort by a, then by b, then by c and then d.

This below not work for me in model.

  default_scope { order('ip ASC') }

How i can do it?

Upvotes: 1

Views: 1145

Answers (2)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95632

PostgreSQL supports an inet data type. Use the right data type, and many problems just vanish. 

scratch=# create table test (
scratch(# ip_addr inet not null);
CREATE TABLE
scratch=# insert into test values ('147.226.211.40');
INSERT 0 1
scratch=# insert into test values ('1.39.80.12');
INSERT 0 1
scratch=# insert into test values ('128.237.199.43');
INSERT 0 1
scratch=# select * from test order by ip_addr;

    ip_addr     
----------------
 1.39.80.12
 128.237.199.43
 147.226.211.40
(3 rows)

ActiveRecord supports the inet data type. In brief . . .

$ bin/rails generate scaffold IpAddr ip_addr:inet

Edit the controller. Order by the symbol :ip_addr.

$ head -9 app/controllers/ip_addrs_controller.rb 
class IpAddrsController < ApplicationController
  before_action :set_ip_addr, only: [:show, :edit, :update, :destroy]

  # GET /ip_addrs
  # GET /ip_addrs.json
  def index
    @ip_addrs = IpAddr.all.order(:ip_addr)
    #                      ^^^^^^^^^^^^^^^
  end

Browse to that page, and you'll find the IP addresses ordered correctly.

Upvotes: 5

Ben Grimm
Ben Grimm

Reputation: 4371

Just cast to inet for the purpose of ordering: 

ORDER BY ip::inet ASC

This should work: 

default_scope { order('ip::inet ASC') }

Or, in your case with bad data, this may work, though it will be terribly slow without creating an index:

ORDER BY regexp_replace(ip, '.?([3-9]\d{2}|25[6-9]|2[6-9]\d)', '')::cidr

Upvotes: 0

Related Questions