<p>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.</p>

<p>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.</p>

<p>I believe that the way I'm doing it now is very inefficient:</p>

<pre><code># 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 =&gt; number, :destination =&gt; 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 &lt;&lt; stop unless r.stops.include? stop
end
</code></pre>

<p>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.</p>

<p>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?</p>

[{'answer_id': 48529, 'author': 'Can Berk Güder', 'author_id': 2119, 'author_profile': 'https://Stackoverflow.com/users/2119', 'pm_score': 1, 'selected': False, 'text': "<p>If I get it right, you (should) have 2 models. A Route model, and a Stop model.</p>\n\n<p>Here's how I would define these models:</p>\n\n<pre><code>class Route &lt; ActiveRecord::Base\n  has_and_belongs_to_many :stops\n  belongs_to :stop, :foreign_key =&gt; 'destination_id'\nend\n\nclass Stop &lt; ActiveRecorde::Base\n  has_and_belongs_to_many :routes\nend\n</code></pre>\n\n<p>And here's how I would set up my tables:</p>\n\n<pre><code>create_table :routes do |t|\n  t.integer :destination_id\n  # Any other information you want to store about routes\nend\n\ncreate_table :stops do |t|\n  # Any other information you want to store about stops\nend\n\ncreate_table :routes_stops, :primary_key =&gt; [:route_id, :stop_id] do |t|\n  t.integer :route_id\n  t.integer :stop_id\nend\n</code></pre>\n\n<p>Finally, here's the code I'd use:</p>\n\n<pre><code># First, find all the relevant routes, just for caching.\nRoute.find(numbers)\n\nr = Route.find(number)\nr.destination_id = destination\nr.stops &lt;&lt; stop\n</code></pre>\n\n<p>This should use only a few SQL queries.</p>\n"}, {'answer_id': 9577609, 'author': 'GeekOnCoffee', 'author_id': 262162, 'author_profile': 'https://Stackoverflow.com/users/262162', 'pm_score': 0, 'selected': False, 'text': "<p>There's likely a good way to cleanup the stops call, but this cleans it up quite a bit assuming I'm picturing properly how routes is structured. </p>\n\n<pre><code>routes.uniq.each do |number, destination|\n\n  r = Route.find_or_create_by_number_and_destination(route[0], destination)\n\n  r.stops &lt;&lt; stop unless r.stops.include? stop\n\nend\n</code></pre>\n"}, {'answer_id': 11144875, 'author': 'jacklin', 'author_id': 215708, 'author_profile': 'https://Stackoverflow.com/users/215708', 'pm_score': 1, 'selected': False, 'text': '<p>Try this gem:\n<a href="https://github.com/seamusabshere/upsert" rel="nofollow">https://github.com/seamusabshere/upsert</a></p>\n\n<p>Docs say its 80% faster than find_or_create_by</p>\n'}]
['https://Stackoverflow.com/questions/48240', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/124/']