# Relationships

---

### Outline
1. Review types of relationships
2. When you should use an association table in SQLAlchemy
3. How to use a association table (CRUD)
4. Some other relationship options

---

## Types of relationships

---

### What type of relationship would you have between...
- `students` and `cohorts`
- `posts` and `comments`
- `users` and `user_profiles`
- `guests` and `events`
- `books` and `authors`

---

### One-to-one
- not expecially common
- typically useful when you have
- a collection of columns that would have null values in most rows
- that you won't access most of the time
- e.g. `users` and `user_profiles`

---

### One-to-many/Many-to-one
- these are most common
- the "many" side has the foreign key
- e.g.
- `employees` and `departments`
- `players` and `teams`

---

### Many-to-many

- no direct relationship between the two tables
- an intermediate table contains both the foreign keys
- e.g.
- `posts` and `tags`
- `students` and `classes`
- self joins too—`users` with `users` in a follows table

---

### Many-to-many

- on the database level, there is no distinction between a many-to-many relationship and just two one-to-many relationships
- the difference emerges in the models/in the ORM

---

### Association tables

- in SQLAlchemy, you only use an association table (as opposed to a model) when there is no other information on that table—just the two primary keys
- it also doesn't let you represent duplicates
- join table uses `db.Table`

---

### Association tables

pet_owners = db.Table(
    "pet_owners",
    db.Column(
        "pet_id", 
        db.Integer, 
        db.ForeignKey("pets.id"), 
        primary_key=True
    ),
    db.Column(
        "owner_id", 
        db.Integer, 
        db.ForeignKey("owners.id"),
        primary_key=True)
    )

---

### Relationships with association tables
- on the Owner model

pets = db.relationship(
        "Pet", 
        secondary=pet_owners, 
        back_populates="owners"
    )

- on the Pet model

owners = db.relationship(
        "Owner", 
        secondary=pet_owners, 
        back_populates="pets"
    )

---

### Use model class or association table?

- `guests` and `events`
- `pets` and `owners`
- `orders` and `products`
- `characters` and `items`

---

## Join table CRUD

---

### Create entries in a join table
- get or create the two entities you want to associate
- use `append` to add one entity to the relevent list

# get my pet object from the database
juice = Pets.query.filter_by(name="Juice").first()
# create an owner object
juliet = Owner(name="Juliet")
# create join table entry for juliet and juice
juliet.pets.append(juice)
db.session.add(juliet)
db.session.commit()

---

### Create entries in a join table (using only ids)

- if you already have the ids of the objects, you would usually just query the database for the objects (using `.get()`) and then insert using the previous, however, if you really needed to, you could insert the values directly into the join table, then add and commit
- the syntax is not as memorable

db.session.connection().execute(
    pet_owners.insert().values(owner_id=5, pet_id=2)
)

---

### Read entries in a join table
- the `db.relationship` will allow you to access the collection of the associated objects as a property

pets = juliet.pets

# use a comprehension to get the information
# that you actually want
print([pet.name for pet in pets])

---

### Update entries in a join table
- you don't!
- there's nothing to update—either the two entries are joined in the table, or they aren't
- there's no other information to change

---

### Delete entries in a join table
- get or create the two objects that you no longer want to be associated
- use `remove` to delete the association

not_juliet.pets.remove(juice) 
db.session.commit()

---

## Additional options

---

### Cascade delete
- add `passive_deletes=True` to the `db.relationship` on the parent model
- add `ondelete="CASCADE"` to the `db.ForeignKey` column

---

### Cascade delete

class Circle(db.Model):
    __tablename__ = 'circles'
    id = db.Column(db.Integer, primary_key=True)
    students = db.relationship(
        "Student", back_populates="circle", passive_deletes=True,
    )
class Student(db.Model):
    __tablename__ = 'students'
    id = db.Column(db.Integer, primary_key=True)
    circle_id = db.Column(
        db.Integer, 
        db.ForeignKey('circles.id', ondelete="CASCADE")
    )
    circle = db.relationship("Circle", back_populates="students")

---

### Lazy and eager loading

- by default, a `db.relationship` will use lazy loading (`lazy='select'`)
- for eager loading, you could use `lazy='joined'`
- sometimes you may want to use `lazy='dynamic'` so that you can perform additional filtering
- you can also configure the loading for an individual query
- more options: [documentation](https://docs.sqlalchemy.org/en/13/orm/relationship_api.html#sqlalchemy.orm.relationship.params.lazy)
- but remember: make it work before you make it fast