Ruby + SQL - Lab 3 - Solution

Just looking for the final code?  It's here.

Associations

Open up the labs/3-associations.rb file.  Previously, we've inserted data into the other 3 tables of this database - companies, contacts, and salespeople.  In this lab, we'll be inserting rows into the activities table and then displaying the data.

Insert 3 rows into the activities table between a salesperson and 2 contacts.

Like contacts, the activities table is associated (i.e. related) to rows in other tables.  A contact is associated to a company, so it has a foreign key company_id.  An activity is associated to both a salesperson and a contact, so it has foreign keys for both salesperson_id and contact_id.  We'll need values for both of those columns, so let's start by finding the rows in those respective tables.

# first query to find a salesperson row in the salespeople table
ben = Salesperson.find_by({"first_name" => "Ben", "last_name" => "Block"})

# next, query to find a contact row in the contacts table
cook = Contact.find_by({"first_name" => "Tim", "last_name" => "Cook"})

Now we can insert an activity between this salesperson and contact:

activity = Activity.new
activity["salesperson_id"] = ben["id"]
activity["contact_id"] = cook["id"]
activity["note"] = "quick checkin over facetime"
activity.save

And another activity between the same salesperson and contact:

activity = Activity.new
activity["salesperson_id"] = ben["id"]
activity["contact_id"] = cook["id"]
activity["note"] = "met at Cupertino"
activity.save

Notice that we're reading from the id column in the salesperson row and assigning that to the activity's salesperson_id column.  Similarly, we're reading from the id column in the contact row and assigning that to the activity's contact_id column.

Let's add 1 more activity, but this time between the salesperson and a different contact.  First we'll need to query to find another row in the contacts table.

bezos = Contact.find_by({"first_name" => "Jeff", "last_name" => "Bezos"})

And then we can insert the activity:

activity = Activity.new
activity["salesperson_id"] = ben["id"]
activity["contact_id"] = bezos["id"]
activity["note"] = "met at Blue Origin HQ"
activity.save

Finally, let's execute the file.

rails runner labs/3-associations.rb

Since we don't see any errors output, we can guess everything worked.  But as a sanity check, let's add a line of code to display the count of activities before and after the inserts.

puts "Activities: #{Activity.all.count}"

If we rerun the file, we should see output:

Activities: 0
Activities: 3

And as with our other files, there is some code at the top Activity.destroy_all that clears the table before the inserts to be sure we're not creating duplicate data.

Display all the activities between a salesperson and one of the contacts.

Next let's display the activities between 1 salesperson and 1 contact (e.g. between Ben and Tim Cook).  First we can display some text to give context to the output:

puts "Activities between Ben and Tim Cook:"

Next, we query for the activities associated to the salesperson and contact.  If we didn't already have the salesperson and contact rows stored in variables, we need to find those first.  But since we do, we'll just use those variables from before.

activities = Activity.where({
  "salesperson_id" => ben["id"],
  "contact_id" => cook["id"]
 })

This will return an array of results (i.e. activity rows) that match the query criteria where the activity row's salesperson_id matches the salesperson's id column and the activity row's contact_id matches the contact's id column.  We haven't written a lot of queries with multiple criteria, but as you can see, we just separate each criteria with a comma.

And now that we have an array, what do we typically do with arrays?  We loop through them...

for activity in activities
  # ...display the activity's note
end

And on each iteration of the loop, we'll look at the activity row in the array and display it's note column:

for activity in activities
  # read the note column from the row
  note = activity["note"]

  # display a string with the note
  puts "- #{note}"
end

The output will look something like:

Activities between Ben and Tim Cook:
- quick checkin over facetime
- met at Cupertino

Challenge!

Display all of a salesperson's activities.

Now that we've figured out how to display activities between a salesperson and contact, let's try to modifying the code to display all of a salesperson's activities (including the name of the contact for each activity and the activity's note).

First, we'll display some descriptive text:

puts "Ben's Activities:"

Next, let's query for all of the salesperson's activities:

activities = Activity.where({"salesperson_id" => ben["id"]})

This is similar to the first step above except without the additional criteria of the contact_id since we don't want a specific contact, we want all contacts.  As always, where returns an array of rows.

And now, with the array, let's loop:

for activity in activities
  # ...write code to display the contact for the activity and the note
end

The loop is the same as before, but this time, the code inside the loop will be different.  First, we need to find the activity's contact.

for activity in activities
  # query to find the contact for this activity
  contact = Contact.find_by({"id" => activity["contact_id"]})
end

Next, we want to read the contact's relevant columns:

for activity in activities
  # query to find the contact for this activity
  contact = Contact.find_by({"id" => activity["contact_id"]})

  # read the first_name and last_name columns from the contact row
  contact_first_name = contact["first_name"]
  contact_last_name = contact["last_name"]
end

Before we get to far along, let's display that data in a string so we know if we're getting errors.

for activity in activities
  # query to find the contact for this activity
  contact = Contact.find_by({"id" => activity["contact_id"]})

  # read the first_name and last_name columns from the contact row
  contact_first_name = contact["first_name"]
  contact_last_name = contact["last_name"]

  # display a string with the contact's full name
  puts "#{contact_first_name} #{contact_last_name}"
end

If you haven't been executing the file at each step, now's a good time to do so.

rails runner labs/3-associations.rb

You should see some output that resembles this:

Ben's Activities:
Tim Cook
Tim Cook
Jeff Bezos

In the inserts above, we created 3 activities for this salesperson - 2 with the "Tim Cook" contact and 1 with the "Jeff Bezos" contact.  So that's what we're seeing in this output.

Last step, let's include the activity's note:

for activity in activities
  # query to find the contact for this activity
  contact = Contact.find_by({"id" => activity["contact_id"]})

  # read the first_name and last_name columns from the contact row
  contact_first_name = contact["first_name"]
  contact_last_name = contact["last_name"]
  
  # read the note column from the activity row
  note = activity["note"]

  # display a string with the contact's full name and note
  puts "#{contact_first_name} #{contact_last_name} - #{note}"
end

And now the output has each note in it:

Ben's Activities:
Tim Cook - quick checkin over facetime
Tim Cook - met at Cupertino
Jeff Bezos - met at Blue Origin HQ

Can you include the contact's company?

Let's take it 1 step further and include the contact's company name.

In the loop above, we already have found the contact for each activity.  A contact is associated to a company.  So, we can find the contact's company in the same way that we found the activity's contact.

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

And then read that name column for the company and include it in the displayed output.

company_name = company["name"]

The final loop code looks like this:

for activity in activities
  # query to find the contact for this activity
  contact = Contact.find_by({"id" => activity["contact_id"]})
  
  # read the first_name and last_name columns from the contact row
  contact_first_name = contact["first_name"]
  contact_last_name = contact["last_name"]
  
  # query to find the company for this contact
  company = Company.find_by({"id" => contact["company_id"]})

  # read the name column from the company row
  company_name = company["name"]
  
  # read the note column from the activity row
  note = activity["note"]

  # display a string with the contact's full name and note
  puts "#{contact_first_name} #{contact_last_name} (#{company_name}) - #{note}"
end

Which outputs:

Ben's Activities:
Tim Cook (Apple) - quick checkin over facetime
Tim Cook (Apple) - met at Cupertino
Jeff Bezos (Amazon) - met at Blue Origin HQ

Great work - we're now utilizing all 4 models in our domain model!

Challenge!

How many activities does each salesperson have?

Perhaps in our application, we want to see which salespeople have been busy!  To see this data, we need to look at each salesperson and count the activities that are associated to them.

We'll start by querying for all the salespeople:

salespeople = Salesperson.all

This returns an array, which means....a loop.  Getting the hang of this?

for salesperson in salespeople
  # ...write code to display the salesperson's number of activities
end

Next we'll query to find the count of activities for the salesperson.

for salesperson in salespeople
  # query to find all rows in activities with relationship to salesperson
  activities = Activity.where({"salesperson_id" => salesperson["id"]})
end

This will return an array of activities associated to the salesperson row on each iteration of the loop.  But we want the count, so let's display the count.

for salesperson in salespeople
  # query to find all rows in activities with relationship to salesperson
  activities = Activity.where({"salesperson_id" => salesperson["id"]})
  # get the count
  number_of_activities = activities.count

  # display count of results
  puts "#{number_of_activities} activities"
end

And the output when we run the file:

3 activities
0 activities

The number of activities for each salesperson isn't very useful out of context - we need to include the salesperson's first_name and last_name columns in the output (or some identifying information) to understand the data.

for salesperson in salespeople
  # query to find all rows in activities with relationship to salesperson
  activities = Activity.where({"salesperson_id" => salesperson["id"]})

  # display count of results
  puts "#{salesperson["first_name"]} #{salesperson["last_name"]}: #{activities.count} activities"
end

And now when we run the file, the output looks something like this.

Ben Block: 3 activities
Brian Eng: 0 activities