Related rows, with where

Week 2 had a WHERE. Week 3 had a foreign key: a column in one table that holds the id of a row in another. Code-along 5 is those two ideas in Ruby, and nothing more. A contact works at one company; the contacts table holds that company's id; and you find a contact's company, or a company's contacts, by looking the id up.

Two tables, one column between them

The rules under Contact in DONE.md:

## Contact
A contact can't be saved without a company.
I can find all the contacts at a company: Apple has three.

The prompt asked the agent for the model and its migration as well as the tests, so the migration came first:

class CreateContacts < ActiveRecord::Migration[8.1]
  def change
    create_table :contacts do |t|
      t.string "first_name"
      t.string "last_name"
      t.string "email"
      t.integer "company_id"

      t.timestamps
    end
  end
end

company_id is an integer because it holds another table's id. On the Data page, every contact's company_id is the id of a row in companies. That column is the whole relationship. There's no other link anywhere.

The tests

require "test_helper"

class ContactTest < ActiveSupport::TestCase
  # One test for each sentence under Contact in DONE.md, named with that sentence.

  test "a contact can't be saved without a company" do
    contact = Contact.new
    contact["first_name"] = "Tim"
    contact["last_name"] = "Cook"
    contact.save

    assert_equal 0, Contact.all.count
  end

  test "I can find all the contacts at a company: Apple has three" do
    apple = Company.new
    apple["name"] = "Apple"
    apple.save

    amazon = Company.new
    amazon["name"] = "Amazon"
    amazon.save

    for first_name in ["Tim", "Craig", "Lisa"]
      contact = Contact.new
      contact["first_name"] = first_name
      contact["company_id"] = apple["id"]
      contact.save
    end

    contact = Contact.new
    contact["first_name"] = "Andy"
    contact["company_id"] = amazon["id"]
    contact.save

    apple_contacts = Contact.where({ "company_id" => apple["id"] })
    assert_equal 3, apple_contacts.count
  end

end

On the first run, one fails and one passes. "Can't be saved without a company" fails because nothing enforces it yet. "Apple has three" passes on the empty model, because finding rows by a column comes with the table; where is not something you add to a model. Its test is still worth having: it says what "at a company" means (the company_id matches) and that Andy, at Amazon, is not counted.

One line makes the first pass:

class Contact < ApplicationRecord
  validates :company_id, presence: true
end

From a company to its contacts

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

A list of every contact whose company_id is Apple's id. It's the WHERE from week 2, written in Ruby, and the SQL it writes is in log/development.log:

SELECT "contacts".* FROM "contacts" WHERE "contacts"."company_id" = 1

where always returns a list, even when it finds one row, or none.

From a contact to its company

Company.find_by({ "id" => contact["company_id"] })

One row, the company whose id the contact holds. find_by returns a single row, or nil if there isn't one.

That's the script from class, scripts/list-contacts.rb, after it saves Apple, Amazon and four contacts:

# Each contact holds its company's id. Look the company up by that id.
for contact in Contact.all
  company = Company.find_by({ "id" => contact["company_id"] })
  puts "#{contact["first_name"]} #{contact["last_name"]} (#{company["name"]})"
end

Tim Cook (Apple)
Craig Federighi (Apple)
Lisa Jackson (Apple)
Andy Jassy (Amazon)

Every line is one lookup. When the thing you want is two tables away (an activity's contact's company, in lab 3), it's two lookups, following the ids one at a time.

Why not contact.company?

Rails can hide this lookup. With belongs_to :company in the model, contact.company does the find_by for you, and has_many :contacts gives you apple.contacts. It's shorter, and in a bigger app you'd use it.

This repo's AGENTS.md tells the agent not to, and the reason is the same as for tests first: so you can see what's happening. contact.company looks like the contact contains a company. It doesn't. It holds a number, and somebody runs a WHERE with it. Once that's obvious to you, the shorthand is a convenience. Before it is, it's a mystery, and the first time it returns nil you won't know where to look.

If your agent wrote has_many or belongs_to, or a contact.company, point it at AGENTS.md and ask for the where.

The rule of thumb

Which table holds the id? The many side. One company has many contacts, so each contact holds a company_id; the company holds nothing about its contacts. One salesperson logs many activities, so each activity holds a salesperson_id. To go from the one to the many, where on that column. To go from the many to the one, find_by on id.