Module 3: Multi-Table Queries

Foreign Keys

Foreign keys are a type of table field used for creating links between tables. Like primary keys, they are most often integers that identify (rather than store) data. However, whereas a primary key is used to id rows in a table, foreign keys are used to connect a record in one table to a record in a second table.

id  farm_name
1   Beech Ranch
2   Morton Farms

id  rancher_name farm_id
1   John Doe     1
2   Jane Doe     1
3   Jim Done     2
4   Jay Dow      2
5   Jen Dunn     1

Querying Across Tables

We can use a JOIN to combine query data from multiple tables using a single SELECT statement.

There are different types of joins; some are listed below:

• inner joins.
• outer joins.
• left joins.
• right joins.
• cross joins.
• non-equality joins.
• self joins.

Using joins requires that the two tables of interest contain at least one field with shared information. For example, if a departments table has an id field, and an employee table has a department_id field, and the values that exist in the id column of the departments table live in the department_id field of the employee table, we can use those fields to join both tables like so:

select * from employees
join departments on employees.department_id = departments.id

This query will return the data from both tables for every instance where the ON condition is true. If there are employees with no value for departmentid or where the value stored in the field does not correspond to an existing id in the departments table, then that record will NOT be returned. In a similar fashion, any records from the departments table that don't have an employee associated with them will also be omitted from the results. Basically, if the id* does not show as the value of department_id for an employee, it won't be able to join.

We can shorten the condition by giving the table names an alias. This is a common practice. Below is the same example using aliases, picking which fields to return, and sorting the results:

select d.id, d.name, e.id, e.first_name, e.last_name, e.salary
from employees as e
join departments as d
  on e.department_id = d.id
order by d.name, e.last_name

Notice that we can take advantage of white space and indentation to make queries more readable.

There are several ways of writing joins, but the one shown here should work on all database management systems and avoid pitfalls, so we recommend it.

The syntax for performing a similar join using Knex is as follows:

db('employees as e')
  .join('departments as d', 'e.department_id', 'd.id')
  .select('d.id', 'd.name', 'e.first_name', 'e.last_name', 'e.salary')

Database Access Methods

While we can write database code directly into our endpoints, best practices dictate that all database logic exists in separate, modular methods. These files containing database access helpers are often called models.

function find() {
}

function findById(id) {
}

function add(user) {
}

function update(changes, id) {
}

function remove(id) {
}

function find() {
  return db('users');
}

function findById(id) {
  // first() returns the first entry in the db matching the query
  return db('users').where({ id }).first();
}

function add(user) {
  db('users').insert(user)
    .then(ids => {
      return findById(ids[0]);
    });
}

module.exports = {
  find,
  findById,
  add,
  update,
  delete,
}

const User = require('./user-model.js');

router.get('/', (req, res) => {
  User.find()
    .then(users => {
      res.json(users);
    })
    .catch(err => {});
});

Guided Project

Multi-Table Queries Starter Code

Multi-Table Queries Solution

Module 3 Project: Multi-Table Queries

The module project contains advanced problems that will challenge and stretch your understanding of the module's content. The project has built-in tests for you to check your work, and the solution video is available in case you need help or want to see how we solved each challenge, but remember, there is always more than one way to solve a problem. Before reviewing the solution video, be sure to attempt the project and try solving the challenges yourself.

Instructions

The link below takes you to Bloom's code repository of the assignment. You'll need to fork the repo to your own GitHub account, and clone it down to your computer:

Starter Repo: Multi-Table Queries

• Fork the repository,
• clone it to your machine, and
• open the README.md file in VSCode, where you will find instructions on completing this Project.
• submit your completed project to the BloomTech Portal

Solution