Untitled

Rails 3 query matching attribute of has_one association that is a subset of has_many association

class Car < ActiveRecord::Base

  has_many :statuses, class_name: 'CarStatus', order: "timestamp DESC"

  has_one :latest_status, class_name: 'CarStatus', order: "timestamp DESC"

 

  delegate :location, :timestamp, to: 'latest_status', prefix: 'latest', allow_nil: true

 

  # ...

end

       

loc = Car.first.latest_location   # Location object (id = 1 for example)

loc.name                          # "Miami, FL"

       

# car.rb

def self.by_location_id(id)

  ids = []

  find_each(include: :latest_status) do |car|

    ids << car.id if car.latest_status.try(:location_id) == id.to_i

  end

  where("id in (?)", ids)

end

       

def by_location_name(loc)

  ids = []

  find_each(include: {latest_status: :location}) do |car|

    ids << car.id if car.latest_location.try(:name) =~ /#{loc}/i

  end

  where("id in (?)", ids)

end

       

select *

from (

  select cars.id as car_id, statuses.id as status_id, statuses.location_id, statuses.created_at, row_number() over (partition by statuses.id order by statuses.created_at) as rn

  from cars join statuses on cars.id = statuses.car_id

) q

where rn = 1 and location_id = ?

       

def self.by_location(loc)

  joins(

    self.escape_sql('join (

    select *

    from (

      select cars.id as car_id, statuses.id as status_id, statuses.location_id, statuses.created_at, row_number() over (partition by statuses.id order by statuses.created_at) as rn

      from cars join statuses on cars.id = statuses.car_id

    ) q

    where rn = 1 and location_id = ?

    ) as subquery on subquery.car_id = cars.id order by subquery.created_at desc', loc)

  )

end

       

class ActiveRecord::Base

  def self.escape_sql(clause, *rest)

    self.send(:sanitize_sql_array, rest.empty? ? clause : ([clause] + rest))

  end

end

       

def self.by_location_name(name)

 loc = Location.find_by_name(name)

 by_location(loc)

end