How can I upsert a bunch of ActiveRecord objects and relationships in Rails?

Question

I am working with an API that provides bus arrival data. For every request, I get back (among other things) a list of which routes serve the stop in question. For example, if the list includes result for bus route #1, 2, and 5, then I know that those serve this stop.

I have a many-to-many relationship set up between Route and Stop, and I want to dynamically check and update these associations on every request. There is no "master list" of which routes serve which stops, so this seems like the best way to get this data.

I believe that the way I'm doing it now is very inefficient:

# routes is an array of [number, destination] that I build while iterating over the data
routes.uniq.each do |route|
  number      = route[0]
  destination = route[1]

  r = Route.find_by_number_and_destination(number, destination)

  if !r
    r = Route.new :number => number, :destination => destination
    r.save
  end

  # I have to check if it already exists because I can't find a way
  # to create a uniqueness constraint on the join table with 2 foreign keys
  r.stops << stop unless r.stops.include? stop
end

Basically, I have to do 2 things for every route I find: 1) Create it if it doesn't already exist, 2) Add a relationship to the current stop if it doesn't already exist.

Is there a better way to do this, for example by getting a bunch of the data in memory and doing some of the processing on the app server side, in order to avoid the multitude of database calls I'm currently doing?

Answer 1

If I get it right, you (should) have 2 models. A Route model, and a Stop model.

Here's how I would define these models:

class Route < ActiveRecord::Base
  has_and_belongs_to_many :stops
  belongs_to :stop, :foreign_key => 'destination_id'
end

class Stop < ActiveRecorde::Base
  has_and_belongs_to_many :routes
end

And here's how I would set up my tables:

create_table :routes do |t|
  t.integer :destination_id
  # Any other information you want to store about routes
end

create_table :stops do |t|
  # Any other information you want to store about stops
end

create_table :routes_stops, :primary_key => [:route_id, :stop_id] do |t|
  t.integer :route_id
  t.integer :stop_id
end

Finally, here's the code I'd use:

# First, find all the relevant routes, just for caching.
Route.find(numbers)

r = Route.find(number)
r.destination_id = destination
r.stops << stop

This should use only a few SQL queries.