The next step in our ruby + SQL unit is understanding how to read data from multiple tables.  Data from a single table is fine, but frequently we need the context from multiple tables to properly use the data in an application.  For example, in our domain model we have a contacts table, but it's important to know the company that a contact is associated to - context matters.

In the domain modeling unit, we spent a good deal of time thinking about the relationships between tables.  Now we want to introduce those relationships in our ruby code.  As a prerequisite, be sure you've created a few rows in the contacts table (if you completed the previous lab, this is already done).

If you haven't already, go to this lesson's repository and open it in Gitpod.  The file for this unit is 3-associations.rb in the code-along directory.

To confirm there are at least a couple rows in both the contacts and companies tables, add the following code to our ruby file and then run it (rails runner code-along/2-associations.rb).

puts Company.all.count
puts Contact.all.count

Finding Records by Foreign Key

If we look back to the CRM domain model (scroll to the bottom for the final structure), the relationship that we're modeling is a 1-to-many where a company can have many contacts and a contact belongs to a single company.  To establish this relationship in the data, the contacts table has a company_id foreign key column which correlates with the companies table id column.  To find all contacts for a given company, we write a query using the company's id.  

For example, if we want all of Apple's contacts, we can first find Apple's id in the database:

apple = Company.find_by({"name" => "Apple Inc."})
puts apple["id"]

That value might be different in each database, but let's just imagine that the output above is 18 - meaning Apple's id in the companies table of this database is 18.  We could then write a query like this:

contacts = Contact.where({"company_id" => 18})

This is ok, but not great - a hardcoded value like an id is fragile.  Again, 18 might not be the id for Apple - multiple developers might have different data in their databases so each would have to change this value to match their data.  It would be better if this code was more dynamic and could adapt to the underlying data.

Actually, it turns out that's pretty easy.  Instead of displaying the id and then hardcoding whatever you see in the output, we can just use apple["id"] in our query which will always work (assuming there's a row somewhere in the table for Apple Inc.).

contacts = Contact.find_by({"company_id" => apple["id"]})

Now it won't matter if the id is 18 or 1 or 99 - it will find the associated contacts regardless.

Next let's practice loops and display all of these contact records (you can reuse your code from the previous lab):

puts "Contacts at Apple: #{contacts.count}"
for contact in contacts
  puts "#{contact["first_name"]} #{contact["last_name"]}"
end

Lab

Time for a lab to practice. Instructions are in the file 3-associations.rb in the labs directory.