Hero Team Management System

Build a hero team management system that efficiently loads hero-team relationships using different database query strategies.

Requirements

Create a system that manages heroes and their teams with optimized relationship loading.

Data Models

Define two database models:

• Hero with fields: id (primary key), name (string), secret_name (string), and a relationship to team
• Team with fields: id (primary key), name (string), headquarters (string), and a relationship to heroes

Functions to Implement

Implement the following functions in hero_manager.py:

1. get_hero_with_team_lazy(session, hero_id: int) - Retrieve a hero by ID. The team should be loaded on-demand when accessed (default behavior, no eager loading)
2. get_team_with_heroes_joined(session, team_id: int) - Retrieve a team with all its heroes loaded in a single query using a SQL JOIN
3. get_all_teams_with_heroes_selectin(session) - Retrieve all teams and efficiently load their heroes in a second separate SELECT query
4. get_team_heroes_count(session, team_id: int) -> int - Return the count of heroes in a specific team

Each function should return the appropriate model instance(s) with relationships accessible as specified.

Test Cases

• When retrieving a hero with lazy loading, the hero object is returned and team can be accessed @test
• When retrieving a team with joined loading, only one SQL query is executed @test
• When retrieving all teams with selectin loading, heroes are efficiently loaded in a separate query @test
• The hero count function returns the correct number of heroes for a team @test

Implementation

@generates

Dependencies { .dependencies }

sqlmodel { .dependency }

Provides SQL database operations with ORM and data validation support.