SQLAlchemy 2 In Practice - Chapter 5 - Advanced Many-To-Many Relationships
a day ago
- This chapter explores advanced many-to-many relationships with additional data in join tables, using the Association Object Pattern.
- UUID primary keys are introduced to avoid exposing database size, with automatic generation using uuid4.
- Write-only relationships are used for potentially large collections, enabling custom queries instead of full loading.
- The join table is modeled as a separate Model class (e.g., OrderItem) with extra columns like unit_price and quantity.
- Creating orders involves manually linking Customer, Order, and OrderItem instances, managing the join table explicitly.
- Deletions require careful handling; non-nullable foreign keys prevent orphaned records, requiring manual removal of related items.
- Import scripts populate the database with sample data from CSV files, demonstrating batch processing.
- Queries demonstrate aggregation functions (avg, sum, count), grouping, filtering by date ranges, and extracting date parts.
- Product reviews are added as another many-to-many relationship with extra columns (rating, comment), using write-only relationships.
- Exercises challenge the reader to write complex queries involving orders, products, manufacturers, and reviews.