Find users in a specific location by querying their latest status update location

I'm working on a Rails application, which is very similar to Twitter, that is used to track members of a teams and their updated status through status updates called 'pings'. Twitter calls these statuses 'tweets'.

The gist of the application is this:

Employee (:first_name, :last_name)

Ping (:datetime, :status, :latitude, :longitude)

Employee Model:

class Employee < ActiveRecord::Base
  has_many :pings
  has_one  :ping, :order => "created_at DESC" # Returns the lastest Ping (employee.ping)
end

Ping Model:

class Ping < ActiveRecord::Base
  belongs_to :employee
  acts_as_mappable  :default_units => :miles,
                    :default_formula => :sphere,
                    :distance_field_name => :distance,
                    :lat_column_name => :latitude,
                    :lng_column_name => :longitude
end

I need to query all of the employees' latest ping by the current location. The problem is I don't know how to do that.

If I search for all pings in the current location I get multiple pings that belong to an employee. I would then have to compare each ping.id with employee.ping.id to see if one of them is the LATEST ping of the employee.

I can't search by Employee because the geo location information is located in the Ping object. And the only pings I care about are the latest ones.

Ping Controller

  def location
    pings = Ping.geo_scope(:within => params[:distance], :origin => [params[:latitude], params[:longitude]])
    render :json => pings, :include => :employee, :only => [:id, :first_name, :last_name, :status, :longitude, :latitude]
    # this returns all Pings that were ever created in this location.
  end

Thanks for any feedback and help!

Thanks, Robin for the help. You inspired 开发者_如何学Gome to come up with the following:

employees = Employee.all

current_pings = []    
employees.each do |employee|
  current_pings << employee.ping.id
end

pings = Ping.geo_scope(:within => params[:distance], :origin => [params[:latitude], params[:longitude]]).find_all_by_id(current_pings)

render :json => pings, :include => :employee, :only => [:id, :first_name, :last_name, :status, :longitude, :latitude, :created_at]

---

This is untested, but my suggestion would be to use Rails' group_by method so you could group all the pings by employee_id (sorted by created at) and then iterate over the collection, returning the key (the employee_id) and the first value in the array (the most recent ping for that employee).

hash = Hash.new
pings.group_by(&:employee_id).order('created_at DESC').each do |k,v|
  hash[k] = v
end
render :json => hash

Might need some tweaking to return the exact data you need in respect of each employee but should work in principle.

Robin