Optimize N+1 database queries with proper JOINs

[perigrin]

Summary

Several DAO methods perform N+1 queries by fetching junction table records and then individually looking up related records. These should be replaced with proper SQL JOINs for better performance.

Affected Methods

Registry::DAO::Tenant

• Line 46: users() method
  • Currently: Fetch all tenant_users, then loop and lookup each user
  • Should: Single JOIN query between tenant_users and users

Registry::DAO::Session

• Line 80: events() method

  • Currently: Fetch all session_events, then loop and lookup each event
  • Should: Single JOIN query between session_events and events

• Line 103: teachers() method

  • Currently: Fetch all session_teachers, then loop and lookup each teacher
  • Should: Single JOIN query between session_teachers and users

Registry::DAO::Event

• Similar pattern in relationship lookups

Implementation Notes

• Use Mojo::Pg's query builder with proper JOINs
• Maintain existing API - methods should return same data structure
• Add tests to verify performance improvement
• Consider adding database indexes if not already present

Performance Impact

• Current: O(n) queries for n relationships
• After fix: O(1) single JOIN query
• Particularly important for admin dashboards showing multiple programs/sessions

Related

See also: Issue #38 (Database Query Performance Optimizations) for broader optimization work