Eager loading database table using ActiveRecord

Question

I have a Ruby 2.2.2 app that uses ActiveRecord as the ORM (similar to Rails). I have two tables, "users" and "accounts", where "accounts" have belongs_to :user. For a given user, I simply want to eager load the contents of the "accounts" table into a Ruby object (named user_accounts) so I can perform operations like:

user_accounts.find_by_product_name("nameofproduct")

...without the find_by_product_name method performing a SQL query. I simply want to preload all entries from the "accounts" table (that belong to a given user) into a Ruby object so I can minimize the number of SQL queries performed.

No matter how much documentation I read, I cannot figure out how to do this properly. Is this possible? If so, how?

DanneManne · Accepted Answer · 2016-04-26 05:17:05Z

2

If you don't want the ORM to re-query the database, then I think you are better of using the select method added by the Enumerable mixin. Because if you try to use the find_by_* methods, I think it will always send another query.

Here is an example of how it could be achieved.

# The accounts association will be loaded and cached the first time
@user.accounts.select { |account| account.name == "nameofproduct" }

# The next time, it is already loaded and will select from the previously loaded records
@user.accounts.select { |account| account.name == "nameofanotherproduct" }

answered Apr 26, 2016 at 5:17

DanneManne

21.2k5 gold badges60 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

JimboTwice Over a year ago

This seems to work great, thank you. One last question: If I wanted to append a record to this array manually, would it be possible to do so? So say I eager loaded the user.accounts, and I want to both A) add a record to the actual DB and B) append that record to the user.accounts eager-loaded array without having to reload the entire table, is that possible?

DanneManne Over a year ago

Yes, I don't think that should be any problem. If you add the new record to the DB by using either @user.accounts.build or @user.accounts.create, then the added record will be appended to the cached records without reloading them all.

JimboTwice Over a year ago

Awesome, thank you. We have one last question regarding this: Is it possible to load an individual record from the User table and eager_load accounts for that individual user? We can't determine how to do this: if we User.find_by_email("[email protected]"), we can't seem to leverage .eager_load. Is it possible to load an individual entry from the User table and eager_load the accounts table for for that entry?

DanneManne Over a year ago

You could use the includes method to do this, but in my opinion, it's not really that big of a difference if you only load one user. If you would load multiple users, then it would make more sense to eager load all accounts for all those users. But it's up to you if you want to do it. I think it should work if you call it like this @user = User.includes(:accounts).where(email: "[email protected]").first

JimboTwice Over a year ago

One thing we've noticed that, if we use .build or .create after eager loading with .includes, it does both commit the new DB record and automatically add it to the eager-loaded array, as you stated. However, if we use .destroy, while it does remove a DB record, it does not remove the record from the eager-loaded array. Is there a way to delete the record and have it automatically remove the record from the eager-loaded array?

|

spickermann · Accepted Answer · 2016-04-26 09:15:43Z

1

I would store that accounts in a hash instead of an array, because lookups in a hash are much faster (in O(1)) than in an array which only allows O(n).

group_by helps you building that hash.

# assuming that @user.accounts returns all accounts
user_accounts = @user.accounts.group_by(&:name)

# querying the hash. use `first` to receive the first matching 
# account (even if there is only one)
user_accounts['nameofproduct'].first

edited Apr 26, 2016 at 9:15

answered Apr 26, 2016 at 5:55

spickermann

108k9 gold badges115 silver badges147 bronze badges

2 Comments

JimboTwice Over a year ago

Thank you. When I execute this, I receive "wrong number of arguments (1 for 0). Also, if I did this, would I be able to create a hash that contained all columns in the accounts table, or just one (such as :name)?

spickermann Over a year ago

I am sorry, I missed a & in the line with the group_by. Please see my updated answer. A hash needs one key, therefore it is not possible to hash by multiple keys. You would need multiple hashes in that case.

Collectives™ on Stack Overflow

Eager loading database table using ActiveRecord

2 Answers 2

6 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related