In this article, I will show you how to use the ActiveRecord joins method to query data from two or more tables in Ruby on Rails. I will use a simple example of a SsoProfile model that belongs to a User model and has some attributes related to a single sign-on provider.
What is a join?
A join is a way of combining two or more tables based on a common attribute. For example, in this case, the SsoProfile table and the User table have a common attribute called user_id, which is the foreign key that references the User table. A join allows you to access the data from both tables in one query, by matching the rows that have the same user_id value.
How to use joins in Rails?
Rails provides a built-in method called joins that takes an argument of the name of the association or a hash of associations. For example, if you have a SsoProfile model that belongs to a User model, you can use joins like this:
Find all SsoProfiles by joining the users table
sso_profiles = SsoProfile.joins(:user)
Find a SsoProfile by joining the users table and using the email attribute
sso_profile = SsoProfile.joins(:user).find_by(users: {email: "alice@gmail.com"})
The joins method will return an ActiveRecord::Relation object that you can chain with other methods like where, order, limit, etc.
How to illustrate a join?
To illustrate how a join works, let's look at an example of how the SsoProfile table and the User table would look:
| SsoProfile table | user_id | sso_provider | sso_provider_id | sso_provider_data |
|---|---|---|---|---|
| 1 | 1 | 1234567890 | {"name": "Alice", "email": "alice@gmail.com", "picture": "https://example.com/alice.jpg"} | |
| 2 | 2 | 9876543210 | {"name": "Bob", "email": "bob@facebook.com", "picture": "https://example.com/bob.jpg"} | |
| 3 | 3 | 4567891230 | {"name": "Charlie", "email": "charlie@twitter.com", "picture": "https://example.com/charlie.jpg"} |
| User table | id | name | |
|---|---|---|---|
| 1 | 1 | alice@gmail.com | Alice |
| 2 | 2 | bob@facebook.com | Bob |
| 3 | 3 | charlie@twitter.com | Charlie |
A join will combine the two tables based on the user_id attribute, like this:
| SsoProfile table JOIN User table on user_id = id |
|---|
| user_id sso_provider sso_provider_id sso_provider_data id email name |
| 1 google 1234567890 {"name": "Alice", ...} 1 alice@gmail.com Alice |
| 2 facebook 9876543210 {"name": "Bob", ...} 2 bob@facebook.com Bob |
| 3 twitter 4567891230 {"name": "Charlie", ...} 3 charlie@twitter.com Charlie |
You can see that the join has matched the rows that have the same user_id value and created a new table with all the attributes from both tables. You can use this new table to query the data from both tables in one query, by using the table name and the attribute name.
Top comments (0)