One-to-Many Associations with Express and Postgres

one-to-many-associations-express-and-pg.md

One-to-Many Associations with Express and Postgres

---

A Look Ahead

• ER Diagrams are an essential tool
• Establishing the right relationships will be one of the most important things
• Multiple tables to store info => keeps data organized, DRY and easily query-able

---

---

Let's plan an app...

I love unicorns, but it's difficult to keep track of them. Help!

• an app about tracking unicorn location
• unicorns can only exist in one enchanted forest at a time
• an enchanted forest can house multiple unicorns

Relationship: One-to-Many An enchanted forest has many unicorns, a unicorn belongs to an enchanted forest

---

---

Todo List (tools)

• React front-end (forest index, show, and new)
• Front-end routing (react router)
• SQL Schema (enchantedForests, unicorns)
• Models (EnchantedForest, Unicorn)

Todo Lists can also be feature driven!

Todo List (features)

• See a list of all Enchanted Forests
• See the details of one Enchanted Forest
• On the show page, see all Unicorns in that forest

---

Let's look at the app thus far!

---

-- server/db/schema.sql

CREATE TABLE enchanted_forests (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

---

-- server/db/schema.sql

CREATE TABLE unicorns (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  age INTEGER,
  enchanted_forest_id INTEGER REFERENCES enchanted_forests(id)
);

---

// server/models/EnchantedForest.js
class EnchantedForest {
  constructor({ id, name }) {
    this.id = id
    this.name = name
  }

 // ...
}

---

// server/models/Unicorn.js

class Unicorn {
  constructor({ id, name, age, enchantedForestId, enchanted_forest_id }) {
    this.id = id
    this.name = name
    this.age = age
    this.enchantedForestId = enchantedForestId || enchanted_forest_id
  }
  // ...
}

Take note of both enchantedForestId and enchanted_forest_id!

---

Inserting Related Records

const queryString = `INSERT INTO unicorns (name, enchanted_forest_id) VALUES ($1, $2);`
await pool.query(queryString, [unicornName, enchantedForestId])

---

Retrieving Related Records: Many

An Enchanted Forest has many Unicorns

// server/models/EnchantedForest.js

class EnchantedForest {
  // ...
  async unicorns() {
    // import within method to avoid circular dependency
    // import as Promise-based method with await
    const unicornFile = await import("./Unicorn.js")
    const Unicorn = unicornFile.default
  
    try {
      const query = `SELECT * FROM unicorns WHERE enchanted_forest_id = $1;`
      const result = await pool.query(query, [this.id])
  
      const relatedUnicornsData = result.rows
      const relatedUnicorns = relatedUnicornsData.map((unicorn) => new Unicorn(unicorn))
  
      return relatedUnicorns
    } catch (error) {
      console.log(error)
      throw(error)
    }
  }
  // ...
}

---

Retrieving Related Records: One

A Unicorn belongs to one Enchanted Forest

// server/models/Unicorn.js

class Unicorn {
  // ...
  async enchantedForest() {
    const enchantedForestFile = await import("./EnchantedForest.js")
    const EnchantedForest = enchantedForestFile.default
  
    try {
      const query = `SELECT * FROM enchanted_forests WHERE unicorn_id = $1;`
      const result = await pool.query(query, [this.enchantedForestId])
  
      const relatedEnchantedForestsData = result.rows
      const relatedEnchantedForests = relatedEnchantedForestsData.map((enchantedForest) => new EnchantedForest(enchantedForest))
  
      return relatedEnchantedForests
    } catch (error) {
      console.log(error)
      throw(error)
    }
  }
  // ...
}

---

Thanks! Any Questions?