Rails: Load multiple records by ID (find v where)

ActiveRecord includes lots of handy methods to query data from the database. Sometimes, it can be confusing which ones work in which way and . This post touches on a common need – look up multiple records from a database table given the ID of the records.

In a simplified sense, the context in my case is that a service returns a list of IDs that can be used to look up the records in a table in our database. So, what I want is a way to look up all the links by ID.

All of these work fine:

# links is an array of integer IDs that matches the ID field of a table called link_names
LinkName.where(id: links)
LinkName.where("id IN (?)", links)

# Or we can use "find" (since it's the ID field we are looking up)

All of these will create the same query:

SELECT "link_names".* FROM "link_names"
WHERE "link_names"."id" 
  IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

So, what’s the difference? Every now and then, if you use find, you will encounter an exception like this:

  (Couldn't find all ... with 'id': (220046, 20004, ..., 310910) 
  (found 18 results, but was looking for 20).):

This comes up because find expects to find a matching record for each ID that is passed. That’s why the last line says something like “expected 18, but was looking for 20” – this means that some of the IDs do not match records. If that’s a problem, then using find is great – an exception is thrown if each ID did not return a record. On the other hand, if it’s entirely possible that some IDs won’t match records, then using where might be better.

Since both approaches use the same query, the performance is similar – so, it depends on what would help you more in your application or business logic.

You can read more in the FinderMethods Documentation

comments powered by Disqus