# 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 ```python= 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 ```python= pets = db.relationship( "Pet", secondary=pet_owners, back_populates="owners" ) ``` - on the Pet model ```python= 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 ```python= # 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 ```python= 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 ```python= 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 ```python= 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 ```python= 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