Struct JoinQuery 

pub struct JoinQuery<'a, L, R>
where
    L: 'static,
    R: 'static,
{ /* private fields */ }

A query builder for joining two collections.

Supports inner joins, left joins, and filtered joins using key-paths for type-safe join conditions.

Type Parameters

• 'a - The lifetime of the data being joined
• L - The type of items in the left collection
• R - The type of items in the right collection

Example

let user_orders = JoinQuery::new(&users, &orders)
    .inner_join(
        User::id_r(),
        Order::user_id_r(),
        |user, order| (user.name.clone(), order.total)
    );

Implementations

impl<'a, L, R> JoinQuery<'a, L, R>

where L: 'static, R: 'static,

pub fn new(left: &'a [L], right: &'a [R]) -> JoinQuery<'a, L, R>

Creates a new join query from two collections.

Note: No Clone required on L or R. The mapper function handles any cloning needed for the result type.

Arguments

• left - The left collection to join
• right - The right collection to join

Example

let join = JoinQuery::new(&users, &orders);

Examples found in repository

examples/without_clone.rs (line 148)

fn main() {
    println!("╔════════════════════════════════════════════════════════════╗");
    println!("║  Query Builder Without Clone - Performance Optimization   ║");
    println!("╚════════════════════════════════════════════════════════════╝\n");

    let employees = vec![
        Employee {
            id: 1,
            name: "Alice".to_string(),
            email: "alice@example.com".to_string(),
            department: "Engineering".to_string(),
            salary: 95000.0,
            large_data: vec![0; 1000], // Large data - expensive to clone!
        },
        Employee {
            id: 2,
            name: "Bob".to_string(),
            email: "bob@example.com".to_string(),
            department: "Engineering".to_string(),
            salary: 87000.0,
            large_data: vec![0; 1000],
        },
        Employee {
            id: 3,
            name: "Carol".to_string(),
            email: "carol@example.com".to_string(),
            department: "Sales".to_string(),
            salary: 75000.0,
            large_data: vec![0; 1000],
        },
    ];

    let departments = vec![
        Department {
            id: 1,
            name: "Engineering".to_string(),
            budget: 1000000.0,
        },
        Department {
            id: 2,
            name: "Sales".to_string(),
            budget: 500000.0,
        },
    ];

    println!("✅ Operations that DON'T require Clone:\n");

    // 1. WHERE filtering - returns Vec<&T>
    println!("1. WHERE filtering (returns references)");
    let query = Query::new(&employees)
        .where_(Employee::department_r(), |dept| dept == "Engineering");
    let engineering = query.all();
    println!("   Found {} engineering employees", engineering.len());
    for emp in &engineering {
        println!("     - {}: ${:.0}", emp.name, emp.salary);
    }

    // 2. COUNT - no cloning needed
    println!("\n2. COUNT aggregation");
    let count = Query::new(&employees)
        .where_(Employee::salary_r(), |&sal| sal > 80000.0)
        .count();
    println!("   {} employees earn over $80k", count);

    // 3. SELECT - only clones the selected field
    println!("\n3. SELECT (only selected fields are cloned)");
    let names: Vec<String> = Query::new(&employees)
        .select(Employee::name_r());
    println!("   Employee names: {:?}", names);

    // 4. FIRST - returns Option<&T>
    println!("\n4. FIRST (returns reference)");
    let query = Query::new(&employees)
        .where_(Employee::salary_r(), |&sal| sal > 90000.0);
    if let Some(emp) = query.first() {
        println!("   First high earner: {} (${:.0})", emp.name, emp.salary);
    }

    // 5. SUM/AVG aggregations - no cloning
    println!("\n5. Aggregations (SUM/AVG)");
    let eng_query = Query::new(&employees)
        .where_(Employee::department_r(), |dept| dept == "Engineering");
    let total = eng_query.sum(Employee::salary_r());
    let avg = eng_query.avg(Employee::salary_r()).unwrap_or(0.0);
    println!("   Engineering total: ${:.0}", total);
    println!("   Engineering average: ${:.0}", avg);

    // 6. MIN/MAX - no cloning
    println!("\n6. MIN/MAX");
    let min = Query::new(&employees).min_float(Employee::salary_r());
    let max = Query::new(&employees).max_float(Employee::salary_r());
    println!("   Salary range: ${:.0} - ${:.0}", min.unwrap(), max.unwrap());

    // 7. LIMIT - returns Vec<&T>
    println!("\n7. LIMIT (returns references)");
    let query = Query::new(&employees);
    let first_two = query.limit(2);
    println!("   First 2 employees:");
    for emp in &first_two {
        println!("     - {}", emp.name);
    }

    // 8. SKIP/Pagination - returns Vec<&T>
    println!("\n8. SKIP/Pagination (returns references)");
    let query = Query::new(&employees);
    let page_2 = query.skip(2).limit(1);
    println!("   Page 2:");
    for emp in &page_2 {
        println!("     - {}", emp.name);
    }

    // 9. EXISTS - just checks
    println!("\n9. EXISTS check");
    let has_sales = Query::new(&employees)
        .where_(Employee::department_r(), |dept| dept == "Sales")
        .exists();
    println!("   Has Sales employees: {}", has_sales);

    // 10. JOIN - no Clone required on L or R!
    println!("\n10. JOIN operations (no Clone required!)");
    let results = JoinQuery::new(&employees, &departments)
        .inner_join(
            Employee::department_r(),
            Department::name_r(),
            |emp, dept| {
                // Mapper only clones what it needs for the result
                (emp.name.clone(), dept.budget)
            },
        );
    println!("   Employee-Department pairs:");
    for (name, budget) in &results {
        println!("     - {} works in dept with ${:.0} budget", name, budget);
    }

    println!("\n╔════════════════════════════════════════════════════════════╗");
    println!("║  Operations that REQUIRE Clone (only when needed)         ║");
    println!("╚════════════════════════════════════════════════════════════╝\n");

    println!("⚠️  The following operations require Clone because they return owned Vec<T>:");
    println!("   - order_by() / order_by_desc()");
    println!("   - order_by_float() / order_by_float_desc()");
    println!("   - group_by()");
    println!("\n   To use these, add #[derive(Clone)] to your struct:");
    println!("   ```rust");
    println!("   #[derive(Clone, Keypaths)]  // Add Clone here");
    println!("   struct Employee {{ ... }}");
    println!("   ```");

    println!("\n╔════════════════════════════════════════════════════════════╗");
    println!("║  Performance Benefits                                      ║");
    println!("╚════════════════════════════════════════════════════════════╝\n");

    println!("✅ Zero cloning for most operations");
    println!("✅ Work with large structs efficiently");
    println!("✅ No unnecessary memory allocations");
    println!("✅ Only clone when you actually need owned data");
    println!("✅ Pay for what you use");

    println!("\n✓ Example complete! Most operations work without Clone.\n");
}

examples/doc_examples.rs (line 151)

fn main() {
    println!("Testing documentation examples...\n");

    // Example from README - Quick Start
    println!("Test 1: README Quick Start Example");
    {
        let products = vec![
            Product { id: 1, name: "Laptop".to_string(), price: 999.99, category: "Electronics".to_string(), stock: 15, rating: 4.5 },
            Product { id: 2, name: "Mouse".to_string(), price: 29.99, category: "Electronics".to_string(), stock: 50, rating: 4.0 },
            Product { id: 3, name: "Desk".to_string(), price: 299.99, category: "Furniture".to_string(), stock: 10, rating: 4.8 },
        ];

        let affordable_query = Query::new(&products)
            .where_(Product::category_r(), |cat| cat == "Electronics")
            .where_(Product::price_r(), |&price| price < 100.0);
        let affordable_electronics = affordable_query.all();

        println!("  Found {} affordable electronics ✅", affordable_electronics.len());
    }

    // Example from README - Filtering
    println!("\nTest 2: Filtering Example");
    {
        let products = vec![
            Product { id: 1, name: "Laptop".to_string(), price: 999.99, category: "Electronics".to_string(), stock: 15, rating: 4.5 },
        ];

        let electronics_query = Query::new(&products)
            .where_(Product::category_r(), |cat| cat == "Electronics");
        let electronics = electronics_query.all();

        println!("  Found {} electronics ✅", electronics.len());
    }

    // Example from README - Selecting
    println!("\nTest 3: Selecting Fields Example");
    {
        let products = vec![
            Product { id: 1, name: "Laptop".to_string(), price: 999.99, category: "Electronics".to_string(), stock: 15, rating: 4.5 },
            Product { id: 2, name: "Mouse".to_string(), price: 29.99, category: "Electronics".to_string(), stock: 50, rating: 4.0 },
        ];

        let names: Vec<String> = Query::new(&products)
            .select(Product::name_r());

        println!("  Selected {} names ✅", names.len());
    }

    // Example from README - Ordering
    println!("\nTest 4: Ordering Example");
    {
        let products = vec![
            Product { id: 1, name: "Laptop".to_string(), price: 999.99, category: "Electronics".to_string(), stock: 15, rating: 4.5 },
            Product { id: 2, name: "Mouse".to_string(), price: 29.99, category: "Electronics".to_string(), stock: 50, rating: 4.0 },
        ];

        let by_price = Query::new(&products).order_by_float(Product::price_r());
        println!("  Ordered {} products ✅", by_price.len());
    }

    // Example from README - Aggregations
    println!("\nTest 5: Aggregations Example");
    {
        let products = vec![
            Product { id: 1, name: "Laptop".to_string(), price: 999.99, category: "Electronics".to_string(), stock: 15, rating: 4.5 },
            Product { id: 2, name: "Mouse".to_string(), price: 29.99, category: "Electronics".to_string(), stock: 50, rating: 4.0 },
        ];

        let electronics_query = Query::new(&products)
            .where_(Product::category_r(), |cat| cat == "Electronics");

        let count = electronics_query.count();
        let total_value: f64 = electronics_query.sum(Product::price_r());
        let avg_price = electronics_query.avg(Product::price_r()).unwrap_or(0.0);

        println!("  Count: {}, Total: ${:.2}, Avg: ${:.2} ✅", count, total_value, avg_price);
    }

    // Example from README - Grouping
    println!("\nTest 6: Grouping Example");
    {
        let products = vec![
            Product { id: 1, name: "Laptop".to_string(), price: 999.99, category: "Electronics".to_string(), stock: 15, rating: 4.5 },
            Product { id: 2, name: "Mouse".to_string(), price: 29.99, category: "Electronics".to_string(), stock: 50, rating: 4.0 },
            Product { id: 3, name: "Desk".to_string(), price: 299.99, category: "Furniture".to_string(), stock: 10, rating: 4.8 },
        ];

        let by_category = Query::new(&products).group_by(Product::category_r());
        println!("  Grouped into {} categories ✅", by_category.len());
    }

    // Example from README - Pagination
    println!("\nTest 7: Pagination Example");
    {
        let products = vec![
            Product { id: 1, name: "P1".to_string(), price: 10.0, category: "A".to_string(), stock: 1, rating: 4.0 },
            Product { id: 2, name: "P2".to_string(), price: 20.0, category: "A".to_string(), stock: 1, rating: 4.0 },
            Product { id: 3, name: "P3".to_string(), price: 30.0, category: "A".to_string(), stock: 1, rating: 4.0 },
        ];

        let query = Query::new(&products);
        let first_10 = query.limit(10);
        let page_1 = query.skip(0).limit(10);

        println!("  Limited to {} products ✅", first_10.len());
        println!("  Page 1 has {} products ✅", page_1.len());
    }

    // Example from README - Join
    println!("\nTest 8: Join Example");
    {
        let users = vec![
            User { id: 1, name: "Alice".to_string() },
            User { id: 2, name: "Bob".to_string() },
        ];

        let orders = vec![
            Order { id: 101, user_id: 1, total: 99.99 },
            Order { id: 102, user_id: 1, total: 149.99 },
        ];

        let user_orders = JoinQuery::new(&users, &orders).inner_join(
            User::id_r(),
            Order::user_id_r(),
            |user, order| (user.name.clone(), order.total),
        );

        println!("  Joined {} user-order pairs ✅", user_orders.len());
    }

    // Example from SQL_COMPARISON - SELECT with WHERE
    println!("\nTest 9: SQL Comparison - SELECT with WHERE");
    {
        #[derive(Clone, Keypaths)]
        struct Employee {
            department: String,
        }

        let employees = vec![
            Employee { department: "Engineering".to_string() },
            Employee { department: "Sales".to_string() },
        ];

        let engineering_query = Query::new(&employees)
            .where_(Employee::department_r(), |dept| dept == "Engineering");
        let engineering = engineering_query.all();

        println!("  Found {} engineering employees ✅", engineering.len());
    }

    // Example from USAGE.md - Complex Filtering
    println!("\nTest 10: USAGE - Complex Filtering");
    {
        let products = vec![
            Product { id: 1, name: "Laptop".to_string(), price: 999.99, category: "Electronics".to_string(), stock: 15, rating: 4.5 },
        ];

        let results_query = Query::new(&products)
            .where_(Product::category_r(), |cat| cat == "Electronics")
            .where_(Product::price_r(), |&price| price >= 100.0 && price <= 500.0)
            .where_(Product::stock_r(), |&stock| stock > 10);
        let results = results_query.order_by_float(Product::price_r());

        println!("  Filtered {} products ✅", results.len());
    }

    println!("\n✅ All documentation examples compile and run successfully!");
}

examples/join_query_builder.rs (line 205)

fn main() {
    println!("=== Join Query Builder Demo ===\n");

    let (users, orders, products) = create_sample_data();

    println!("Database:");
    println!("  Users: {}", users.len());
    println!("  Orders: {}", orders.len());
    println!("  Products: {}\n", products.len());

    // Join 1: Inner join Users and Orders
    println!("--- Join 1: Users with Their Orders ---");
    let user_orders = JoinQuery::new(&users, &orders).inner_join(
        User::id_r(),
        Order::user_id_r(),
        |user, order| UserOrder {
            user_name: user.name.clone(),
            user_email: user.email.clone(),
            order_id: order.id,
            quantity: order.quantity,
            total: order.total,
        },
    );

    for uo in &user_orders {
        println!(
            "  • {} - Order #{} - {} items - ${:.2}",
            uo.user_name, uo.order_id, uo.quantity, uo.total
        );
    }
    println!("Total: {} user-order pairs", user_orders.len());

    // Join 2: Three-way join (Orders -> Users, Orders -> Products)
    println!("\n--- Join 2: Complete Order Details (3-Way Join) ---");
    
    // First join: Orders with Users
    let orders_with_users = JoinQuery::new(&orders, &users).inner_join(
        Order::user_id_r(),
        User::id_r(),
        |order, user| (order.clone(), user.clone()),
    );

    // Second join: (Orders+Users) with Products
    let mut order_details = Vec::new();
    for (order, user) in &orders_with_users {
        for product in &products {
            if order.product_id == product.id {
                order_details.push(OrderDetail {
                    order_id: order.id,
                    user_name: user.name.clone(),
                    product_name: product.name.clone(),
                    quantity: order.quantity,
                    price: product.price,
                    total: order.total,
                });
            }
        }
    }

    for od in &order_details {
        println!(
            "  • Order #{}: {} bought {} x {} @ ${:.2} = ${:.2}",
            od.order_id, od.user_name, od.quantity, od.product_name, od.price, od.total
        );
    }

    // Join 3: Left join to show all users (including those without orders)
    println!("\n--- Join 3: All Users with Order Count (Left Join) ---");
    
    // Use left_join to get all users with their orders (or None)
    let user_order_pairs = JoinQuery::new(&users, &orders).left_join(
        User::id_r(),
        Order::user_id_r(),
        |user, order| (user.clone(), order.map(|o| o.clone())),
    );

    // Group by user to count orders
    let mut user_stats: HashMap<u32, (String, String, usize, f64)> = HashMap::new();
    for (user, order) in &user_order_pairs {
        let entry = user_stats
            .entry(user.id)
            .or_insert_with(|| (user.name.clone(), user.city.clone(), 0, 0.0));
        if let Some(order) = order {
            entry.2 += 1; // order count
            entry.3 += order.total; // total spent
        }
    }

    let mut user_order_stats: Vec<_> = user_stats
        .into_iter()
        .map(|(_, (name, city, count, total))| UserOrderCount {
            user_name: name,
            user_city: city,
            order_count: count,
            total_spent: total,
        })
        .collect();

    user_order_stats.sort_by(|a, b| a.user_name.cmp(&b.user_name));

    for stat in &user_order_stats {
        if stat.order_count > 0 {
            println!(
                "  • {} ({}) - {} orders - ${:.2} total",
                stat.user_name, stat.user_city, stat.order_count, stat.total_spent
            );
        } else {
            println!("  • {} ({}) - No orders yet", stat.user_name, stat.user_city);
        }
    }

    // Join 4: Aggregated join - Category sales analysis
    println!("\n--- Join 4: Sales by Product Category ---");

    // Join orders with products to get category information
    let order_products = JoinQuery::new(&orders, &products).inner_join(
        Order::product_id_r(),
        Product::id_r(),
        |order, product| (order.clone(), product.clone()),
    );

    // Aggregate by category
    let mut category_stats: HashMap<String, (Vec<u32>, f64, std::collections::HashSet<u32>)> =
        HashMap::new();

    for (order, product) in &order_products {
        let entry = category_stats
            .entry(product.category.clone())
            .or_insert_with(|| (Vec::new(), 0.0, std::collections::HashSet::new()));
        entry.0.push(order.id);
        entry.1 += order.total;
        entry.2.insert(order.user_id);
    }

    let mut category_sales: Vec<CategorySales> = category_stats
        .into_iter()
        .map(|(category, (orders, revenue, customers))| CategorySales {
            category,
            total_orders: orders.len(),
            total_revenue: revenue,
            unique_customers: customers.len(),
        })
        .collect();

    category_sales.sort_by(|a, b| {
        b.total_revenue
            .partial_cmp(&a.total_revenue)
            .unwrap_or(std::cmp::Ordering::Equal)
    });

    for cs in &category_sales {
        println!(
            "  • {}: {} orders - ${:.2} revenue - {} customers",
            cs.category, cs.total_orders, cs.total_revenue, cs.unique_customers
        );
    }

    // Join 5: Filtered join - High value orders
    println!("\n--- Join 5: High Value Orders (>$100) with User Details ---");
    let high_value_orders = JoinQuery::new(&orders, &users).inner_join_where(
        Order::user_id_r(),
        User::id_r(),
        |order, _user| order.total > 100.0,
        |order, user| (user.name.clone(), order.id, order.total),
    );

    for (name, order_id, total) in &high_value_orders {
        println!("  • {} - Order #{} - ${:.2}", name, order_id, total);
    }

    // Join 6: Users in same city analysis
    println!("\n--- Join 6: Users from Same City ---");
    let user_pairs = JoinQuery::new(&users, &users).inner_join_where(
        User::city_r(),
        User::city_r(),
        |u1, u2| u1.id < u2.id, // Avoid duplicates and self-pairs
        |u1, u2| (u1.name.clone(), u2.name.clone(), u1.city.clone()),
    );

    for (name1, name2, city) in &user_pairs {
        println!("  • {} and {} both live in {}", name1, name2, city);
    }

    // Join 7: Product popularity
    println!("\n--- Join 7: Product Popularity Ranking ---");
    
    // Join orders with products
    let product_order_pairs = JoinQuery::new(&products, &orders).inner_join(
        Product::id_r(),
        Order::product_id_r(),
        |product, order| (product.clone(), order.clone()),
    );

    // Aggregate by product
    let mut product_sales: HashMap<u32, (String, usize, u32, f64)> = HashMap::new();
    for (product, order) in &product_order_pairs {
        let entry = product_sales
            .entry(product.id)
            .or_insert_with(|| (product.name.clone(), 0, 0, 0.0));
        entry.1 += 1; // order count
        entry.2 += order.quantity; // total quantity
        entry.3 += order.total; // total revenue
    }

    let mut popularity: Vec<_> = product_sales.into_iter().collect();
    popularity.sort_by(|a, b| b.1 .1.cmp(&a.1 .1)); // sort by order count

    for (_, (name, order_count, total_qty, revenue)) in &popularity {
        println!(
            "  • {} - {} orders - {} units - ${:.2}",
            name, order_count, total_qty, revenue
        );
    }

    // Join 8: User spending by city
    println!("\n--- Join 8: Total Spending by City ---");
    
    // Join users with orders to get city and spending info
    let user_city_orders = JoinQuery::new(&users, &orders).inner_join(
        User::id_r(),
        Order::user_id_r(),
        |user, order| (user.city.clone(), order.total, user.id),
    );

    // Aggregate by city
    let mut city_spending: HashMap<String, (f64, std::collections::HashSet<u32>)> = HashMap::new();
    for (city, total, user_id) in &user_city_orders {
        let entry = city_spending
            .entry(city.clone())
            .or_insert_with(|| (0.0, std::collections::HashSet::new()));
        entry.0 += total;
        entry.1.insert(*user_id);
    }

    let mut city_stats: Vec<_> = city_spending
        .into_iter()
        .map(|(city, (total, customers))| (city, total, customers.len()))
        .collect();
    
    city_stats.sort_by(|a, b| {
        b.1.partial_cmp(&a.1).unwrap_or(std::cmp::Ordering::Equal)
    });

    for (city, total, customer_count) in &city_stats {
        println!(
            "  • {} - ${:.2} total - {} customers - ${:.2} avg",
            city,
            total,
            customer_count,
            total / *customer_count as f64
        );
    }

    // Statistics summary
    println!("\n=== Summary Statistics ===");
    println!("Total orders: {}", orders.len());
    
    let total_revenue: f64 = orders.iter().map(|o| o.total).sum();
    println!("Total revenue: ${:.2}", total_revenue);
    println!("Average order value: ${:.2}", total_revenue / orders.len() as f64);
    
    // Count unique customers using a join
    let unique_customers: std::collections::HashSet<u32> = 
        orders.iter().map(|o| o.user_id).collect();
    println!("Active customers: {}", unique_customers.len());
    println!(
        "Average orders per customer: {:.1}",
        orders.len() as f64 / unique_customers.len() as f64
    );

    println!("\n✓ Join query builder demo complete!");
}

examples/sql_comparison.rs (line 333)

fn main() {
    let employees = create_employees();
    let departments = create_departments();
    let projects = create_projects();

    println!("\n╔════════════════════════════════════════════════════════════════════════╗");
    println!("║     SQL vs Rust Query Builder Comparison                              ║");
    println!("║     Demonstrating equivalent operations                               ║");
    println!("╚════════════════════════════════════════════════════════════════════════╝");

    // ============================================================================
    // EXAMPLE 1: Simple SELECT with WHERE
    // ============================================================================
    print_separator("Example 1: SELECT with WHERE clause");
    print_query(
        "SELECT * FROM employees WHERE department = 'Engineering';",
        "Filter employees by department"
    );

    let eng_query = Query::new(&employees)
        .where_(Employee::department_r(), |dept| dept == "Engineering");
    let engineering_employees = eng_query.all();

    for emp in &engineering_employees {
        println!("  ID: {}, Name: {}, Salary: ${:.2}", emp.id, emp.name, emp.salary);
    }
    println!("\nRust Query Builder:");
    println!("Query::new(&employees)");
    println!("    .where_(Employee::department_r(), |dept| dept == \"Engineering\")");
    println!("    .all()");
    println!("✓ Found {} employees", engineering_employees.len());

    // ============================================================================
    // EXAMPLE 2: SELECT specific columns
    // ============================================================================
    print_separator("Example 2: SELECT specific columns (Projection)");
    print_query(
        "SELECT name FROM employees WHERE salary > 80000;",
        "Get names of high-earning employees"
    );

    let high_earners = Query::new(&employees)
        .where_(Employee::salary_r(), |&salary| salary > 80000.0)
        .select(Employee::name_r());

    for name in &high_earners {
        println!("  {}", name);
    }
    println!("\nRust Query Builder:");
    println!("Query::new(&employees)");
    println!("    .where_(Employee::salary_r(), |&salary| salary > 80000.0)");
    println!("    .select(Employee::name_r())");
    println!("✓ Found {} employees", high_earners.len());

    // ============================================================================
    // EXAMPLE 3: COUNT
    // ============================================================================
    print_separator("Example 3: COUNT aggregation");
    print_query(
        "SELECT COUNT(*) FROM employees WHERE age < 30;",
        "Count young employees"
    );

    let young_count = Query::new(&employees)
        .where_(Employee::age_r(), |&age| age < 30)
        .count();

    println!("  Count: {}", young_count);
    println!("\nRust Query Builder:");
    println!("Query::new(&employees)");
    println!("    .where_(Employee::age_r(), |&age| age < 30)");
    println!("    .count()");
    println!("✓ Result: {}", young_count);

    // ============================================================================
    // EXAMPLE 4: SUM, AVG, MIN, MAX
    // ============================================================================
    print_separator("Example 4: Aggregate functions (SUM, AVG, MIN, MAX)");
    print_query(
        "SELECT \n\
         SUM(salary) as total_salary,\n\
         AVG(salary) as avg_salary,\n\
         MIN(salary) as min_salary,\n\
         MAX(salary) as max_salary\n\
         FROM employees WHERE department = 'Engineering';",
        "Engineering department salary statistics"
    );

    let eng_query = Query::new(&employees)
        .where_(Employee::department_r(), |dept| dept == "Engineering");

    let total = eng_query.sum(Employee::salary_r());
    let avg = eng_query.avg(Employee::salary_r()).unwrap_or(0.0);
    let min = eng_query.min_float(Employee::salary_r()).unwrap_or(0.0);
    let max = eng_query.max_float(Employee::salary_r()).unwrap_or(0.0);

    println!("  Total Salary: ${:.2}", total);
    println!("  Avg Salary:   ${:.2}", avg);
    println!("  Min Salary:   ${:.2}", min);
    println!("  Max Salary:   ${:.2}", max);

    println!("\nRust Query Builder:");
    println!("let query = Query::new(&employees)");
    println!("    .where_(Employee::department_r(), |dept| dept == \"Engineering\");");
    println!("query.sum(Employee::salary_r())  // ${:.2}", total);
    println!("query.avg(Employee::salary_r())  // ${:.2}", avg);
    println!("query.min_float(Employee::salary_r())  // ${:.2}", min);
    println!("query.max_float(Employee::salary_r())  // ${:.2}", max);

    // ============================================================================
    // EXAMPLE 5: GROUP BY with aggregation
    // ============================================================================
    print_separator("Example 5: GROUP BY with aggregation");
    print_query(
        "SELECT \n\
         department,\n\
         COUNT(*) as emp_count,\n\
         AVG(salary) as avg_salary\n\
         FROM employees\n\
         GROUP BY department;",
        "Statistics by department"
    );

    let by_dept = Query::new(&employees).group_by(Employee::department_r());

    for (dept, emps) in &by_dept {
        let dept_query = Query::new(emps);
        let count = emps.len();
        let avg_sal = dept_query.avg(Employee::salary_r()).unwrap_or(0.0);
        println!("  {}: {} employees, avg salary ${:.2}", dept, count, avg_sal);
    }

    println!("\nRust Query Builder:");
    println!("let by_dept = Query::new(&employees).group_by(Employee::department_r());");
    println!("for (dept, emps) in &by_dept {{");
    println!("    let dept_query = Query::new(emps);");
    println!("    dept_query.avg(Employee::salary_r())");
    println!("}}");

    // ============================================================================
    // EXAMPLE 6: ORDER BY
    // ============================================================================
    print_separator("Example 6: ORDER BY");
    print_query(
        "SELECT name, salary FROM employees\n\
         WHERE department = 'Sales'\n\
         ORDER BY salary DESC;",
        "Sales employees ordered by salary (descending)"
    );

    let sales_sorted = Query::new(&employees)
        .where_(Employee::department_r(), |dept| dept == "Sales")
        .order_by_float_desc(Employee::salary_r());

    for emp in &sales_sorted {
        println!("  {}: ${:.2}", emp.name, emp.salary);
    }

    println!("\nRust Query Builder:");
    println!("Query::new(&employees)");
    println!("    .where_(Employee::department_r(), |dept| dept == \"Sales\")");
    println!("    .order_by_float_desc(Employee::salary_r())");

    // ============================================================================
    // EXAMPLE 7: Multiple WHERE conditions (AND)
    // ============================================================================
    print_separator("Example 7: Multiple WHERE conditions (AND)");
    print_query(
        "SELECT * FROM employees\n\
         WHERE salary > 70000 AND age < 35;",
        "High-earning young employees"
    );

    let filter_query = Query::new(&employees)
        .where_(Employee::salary_r(), |&salary| salary > 70000.0)
        .where_(Employee::age_r(), |&age| age < 35);
    let filtered = filter_query.all();

    for emp in &filtered {
        println!("  {}: Age {}, Salary ${:.2}", emp.name, emp.age, emp.salary);
    }

    println!("\nRust Query Builder:");
    println!("Query::new(&employees)");
    println!("    .where_(Employee::salary_r(), |&salary| salary > 70000.0)");
    println!("    .where_(Employee::age_r(), |&age| age < 35)");
    println!("    .all()");
    println!("✓ Found {} employees", filtered.len());

    // ============================================================================
    // EXAMPLE 8: LIMIT (TOP N)
    // ============================================================================
    print_separator("Example 8: LIMIT / TOP N");
    print_query(
        "SELECT TOP 3 name, salary FROM employees\n\
         ORDER BY salary DESC;",
        "Top 3 highest paid employees"
    );

    let top_earners = Query::new(&employees)
        .order_by_float_desc(Employee::salary_r());

    for (i, emp) in top_earners.iter().take(3).enumerate() {
        println!("  {}. {}: ${:.2}", i + 1, emp.name, emp.salary);
    }

    println!("\nRust Query Builder:");
    println!("Query::new(&employees)");
    println!("    .order_by_float_desc(Employee::salary_r())");
    println!("    .into_iter().take(3)");

    // ============================================================================
    // EXAMPLE 9: OFFSET and LIMIT (Pagination)
    // ============================================================================
    print_separator("Example 9: OFFSET and LIMIT (Pagination)");
    print_query(
        "SELECT name FROM employees\n\
         ORDER BY name\n\
         LIMIT 3 OFFSET 3;",
        "Page 2 of employee names (3 per page)"
    );

    let page_2 = Query::new(&employees)
        .order_by(Employee::name_r())
        .into_iter()
        .skip(3)
        .take(3)
        .collect::<Vec<_>>();

    for emp in &page_2 {
        println!("  {}", emp.name);
    }

    println!("\nRust Query Builder:");
    println!("Query::new(&employees)");
    println!("    .order_by(Employee::name_r())");
    println!("    .into_iter().skip(3).take(3)");

    // ============================================================================
    // EXAMPLE 10: INNER JOIN
    // ============================================================================
    print_separator("Example 10: INNER JOIN");
    print_query(
        "SELECT e.name, d.name as dept_name, d.budget\n\
         FROM employees e\n\
         INNER JOIN departments d ON e.department = d.name;",
        "Employees with their department info"
    );

    let emp_dept = JoinQuery::new(&employees, &departments).inner_join(
        Employee::department_r(),
        Department::name_r(),
        |emp, dept| (emp.name.clone(), dept.name.clone(), dept.budget),
    );

    for (emp_name, dept_name, budget) in emp_dept.iter().take(5) {
        println!("  {} works in {} (Budget: ${:.0})", emp_name, dept_name, budget);
    }
    println!("  ... (showing first 5)");

    println!("\nRust Query Builder:");
    println!("JoinQuery::new(&employees, &departments).inner_join(");
    println!("    Employee::department_r(),");
    println!("    Department::name_r(),");
    println!("    |emp, dept| (emp.name.clone(), dept.name.clone(), dept.budget)");
    println!(")");
    println!("✓ Found {} matches", emp_dept.len());

    // ============================================================================
    // EXAMPLE 11: GROUP BY with HAVING equivalent
    // ============================================================================
    print_separator("Example 11: GROUP BY with filtering (HAVING equivalent)");
    print_query(
        "SELECT city, COUNT(*) as emp_count, AVG(salary) as avg_salary\n\
         FROM employees\n\
         GROUP BY city\n\
         HAVING COUNT(*) > 1;",
        "Cities with multiple employees"
    );

    let by_city = Query::new(&employees).group_by(Employee::city_r());
    let mut city_stats: Vec<_> = by_city
        .iter()
        .filter(|(_, emps)| emps.len() > 1) // HAVING equivalent
        .map(|(city, emps)| {
            let avg_sal = Query::new(emps).avg(Employee::salary_r()).unwrap_or(0.0);
            (city.clone(), emps.len(), avg_sal)
        })
        .collect();
    
    city_stats.sort_by(|a, b| b.1.cmp(&a.1)); // Sort by count

    for (city, count, avg_sal) in &city_stats {
        println!("  {}: {} employees, avg salary ${:.2}", city, count, avg_sal);
    }

    println!("\nRust Query Builder:");
    println!("let by_city = Query::new(&employees).group_by(Employee::city_r());");
    println!("by_city.iter()");
    println!("    .filter(|(_, emps)| emps.len() > 1)  // HAVING equivalent");
    println!("    .map(|(city, emps)| {{");
    println!("        let avg = Query::new(emps).avg(Employee::salary_r());");
    println!("        (city, emps.len(), avg)");
    println!("    }})");

    // ============================================================================
    // EXAMPLE 12: Complex query with multiple operations
    // ============================================================================
    print_separator("Example 12: Complex multi-operation query");
    print_query(
        "SELECT name, salary, age\n\
         FROM employees\n\
         WHERE department IN ('Engineering', 'Sales')\n\
         AND salary BETWEEN 80000 AND 100000\n\
         AND age >= 30\n\
         ORDER BY salary DESC;",
        "Experienced mid-to-senior level employees in core departments"
    );

    let complex_query = Query::new(&employees)
        .where_(Employee::department_r(), |dept| dept == "Engineering" || dept == "Sales")
        .where_(Employee::salary_r(), |&sal| sal >= 80000.0 && sal <= 100000.0)
        .where_(Employee::age_r(), |&age| age >= 30)
        .order_by_float_desc(Employee::salary_r());

    for emp in &complex_query {
        println!("  {}: Age {}, {} dept, ${:.2}", emp.name, emp.age, emp.department, emp.salary);
    }

    println!("\nRust Query Builder:");
    println!("Query::new(&employees)");
    println!("    .where_(Employee::department_r(), |dept| dept == \"Engineering\" || dept == \"Sales\")");
    println!("    .where_(Employee::salary_r(), |&sal| sal >= 80000.0 && sal <= 100000.0)");
    println!("    .where_(Employee::age_r(), |&age| age >= 30)");
    println!("    .order_by_float_desc(Employee::salary_r())");
    println!("✓ Found {} employees", complex_query.len());

    // ============================================================================
    // EXAMPLE 13: Three-table JOIN
    // ============================================================================
    print_separator("Example 13: Three-table JOIN");
    print_query(
        "SELECT p.name as project, d.name as department, d.location\n\
         FROM projects p\n\
         INNER JOIN departments d ON p.department_id = d.id;",
        "Projects with their department details"
    );

    let proj_dept = JoinQuery::new(&projects, &departments).inner_join(
        Project::department_id_r(),
        Department::id_r(),
        |proj, dept| {
            (proj.name.clone(), dept.name.clone(), dept.location.clone(), proj.budget)
        },
    );

    for (proj_name, dept_name, location, budget) in &proj_dept {
        println!("  {}: {} dept in {} (${:.0})", proj_name, dept_name, location, budget);
    }

    println!("\nRust Query Builder:");
    println!("JoinQuery::new(&projects, &departments).inner_join(");
    println!("    Project::department_id_r(),");
    println!("    Department::id_r(),");
    println!("    |proj, dept| (proj.name, dept.name, dept.location, proj.budget)");
    println!(")");

    // ============================================================================
    // EXAMPLE 14: Subquery equivalent (using intermediate results)
    // ============================================================================
    print_separator("Example 14: Subquery equivalent");
    print_query(
        "SELECT * FROM employees\n\
         WHERE salary > (SELECT AVG(salary) FROM employees);",
        "Employees earning above average"
    );

    let avg_salary = Query::new(&employees)
        .avg(Employee::salary_r())
        .unwrap_or(0.0);

    let above_avg_query = Query::new(&employees)
        .where_(Employee::salary_r(), move |&sal| sal > avg_salary);
    let above_avg = above_avg_query.all();

    println!("  Average salary: ${:.2}", avg_salary);
    for emp in &above_avg {
        println!("  {}: ${:.2} ({:.1}% above average)", 
            emp.name, emp.salary, ((emp.salary - avg_salary) / avg_salary * 100.0));
    }

    println!("\nRust Query Builder:");
    println!("let avg = Query::new(&employees).avg(Employee::salary_r()).unwrap_or(0.0);");
    println!("Query::new(&employees)");
    println!("    .where_(Employee::salary_r(), |&sal| sal > avg)");
    println!("    .all()");
    println!("✓ Found {} employees above average", above_avg.len());

    // ============================================================================
    // EXAMPLE 15: Advanced aggregation (revenue calculation)
    // ============================================================================
    print_separator("Example 15: Advanced aggregation");
    print_query(
        "SELECT \n\
         d.name,\n\
         d.budget as dept_budget,\n\
         SUM(p.budget) as total_project_budget,\n\
         (d.budget - SUM(p.budget)) as remaining_budget\n\
         FROM departments d\n\
         LEFT JOIN projects p ON d.id = p.department_id\n\
         GROUP BY d.name, d.budget;",
        "Department budget utilization"
    );

    // Join departments with projects
    let dept_projects = JoinQuery::new(&departments, &projects).left_join(
        Department::id_r(),
        Project::department_id_r(),
        |dept, proj| (dept.clone(), proj.map(|p| p.clone())),
    );

    // Aggregate by department
    let mut dept_budget_map: HashMap<String, (f64, Vec<f64>)> = HashMap::new();
    for (dept, proj) in dept_projects {
        let entry = dept_budget_map
            .entry(dept.name.clone())
            .or_insert((dept.budget, Vec::new()));
        if let Some(p) = proj {
            entry.1.push(p.budget);
        }
    }

    for (dept_name, (total_budget, project_budgets)) in dept_budget_map.iter() {
        let used: f64 = project_budgets.iter().sum();
        let remaining = total_budget - used;
        println!("  {}: Budget ${:.0}, Used ${:.0}, Remaining ${:.0} ({:.1}% utilized)",
            dept_name, total_budget, used, remaining, (used / total_budget * 100.0));
    }

    println!("\nRust Query Builder:");
    println!("let dept_projects = JoinQuery::new(&departments, &projects)");
    println!("    .left_join(Department::id_r(), Project::department_id_r(), ...)");
    println!("// Then aggregate using HashMap");

    // ============================================================================
    // Summary
    // ============================================================================
    print_separator("Summary");
    println!("\n✓ All 15 SQL queries successfully replicated with Rust Query Builder!");
    println!("\nDemonstrated equivalents for:");
    println!("  • SELECT with WHERE");
    println!("  • Projection (SELECT specific columns)");
    println!("  • Aggregations (COUNT, SUM, AVG, MIN, MAX)");
    println!("  • GROUP BY");
    println!("  • ORDER BY");
    println!("  • LIMIT and OFFSET");
    println!("  • INNER JOIN and LEFT JOIN");
    println!("  • Complex conditions (AND, OR, BETWEEN)");
    println!("  • Subqueries");
    println!("  • Multi-table operations");
    println!("\n🎯 Type-safe, compile-time checked, and zero runtime overhead!");
    println!();
}

examples/memory_safety_verification.rs (line 252)

fn main() {
    println!("\n╔═══════════════════════════════════════════════════════════════╗");
    println!("║  Memory Safety Verification                                   ║");
    println!("║  Proving 'static doesn't cause memory leaks                   ║");
    println!("╚═══════════════════════════════════════════════════════════════╝\n");

    println!("🔍 Understanding 'static:\n");
    println!("  • T: 'static means: Type T doesn't contain non-'static references");
    println!("  • It does NOT mean: Data lives for entire program");
    println!("  • It's needed for: Storing types in trait objects");
    println!("  • Safety: Compiler ensures no dangling references\n");

    // ============================================================================
    // TEST 1: Basic Query - Verify Cleanup
    // ============================================================================
    println!("═══════════════════════════════════════════════════════════════");
    println!("Test 1: Basic WHERE query - verify all data is dropped");
    println!("═══════════════════════════════════════════════════════════════\n");

    reset_stats();
    {
        let employees = vec![
            create_employee(1, "Alice", "Engineering", 95000.0),
            create_employee(2, "Bob", "Engineering", 87000.0),
            create_employee(3, "Carol", "Sales", 75000.0),
        ];
        print_memory_status("After creating employees");

        {
            let query = Query::new(&employees)
                .where_(Employee::department_r(), |dept| dept == "Engineering");
            let results = query.all();
            
            println!("  Found {} engineering employees", results.len());
            print_memory_status("During query execution");
        }
        
        print_memory_status("After query scope ends");
    }
    
    print_memory_status("After employees scope ends");
    println!();

    // ============================================================================
    // TEST 2: Multiple Queries - No Accumulation
    // ============================================================================
    println!("═══════════════════════════════════════════════════════════════");
    println!("Test 2: Multiple queries - verify no memory accumulation");
    println!("═══════════════════════════════════════════════════════════════\n");

    reset_stats();
    {
        let employees = vec![
            create_employee(1, "Alice", "Engineering", 95000.0),
            create_employee(2, "Bob", "Sales", 75000.0),
        ];

        let initial_stats = get_stats();
        println!("  Initial allocations: {}", initial_stats.0);

        // Run 10 queries
        for i in 1..=10 {
            let query = Query::new(&employees)
                .where_(Employee::salary_r(), |&s| s > 70000.0);
            let _results = query.all();
            
            if i % 3 == 0 {
                let (allocs, drops) = get_stats();
                println!("  After {} queries - Allocated: {}, Dropped: {}", i, allocs, drops);
            }
        }

        let final_stats = get_stats();
        println!("\n  After 10 queries:");
        println!("    Allocations: {} (should be same as initial)", final_stats.0);
        println!("    ✅ No memory accumulation from queries!");
    }

    print_memory_status("After all queries and employees dropped");
    println!();

    // ============================================================================
    // TEST 3: ORDER BY (requires Clone) - Track Cloning
    // ============================================================================
    println!("═══════════════════════════════════════════════════════════════");
    println!("Test 3: ORDER BY (with Clone) - verify controlled cloning");
    println!("═══════════════════════════════════════════════════════════════\n");

    reset_stats();
    {
        let employees = vec![
            create_employee(1, "Alice", "Engineering", 95000.0),
            create_employee(2, "Bob", "Sales", 87000.0),
            create_employee(3, "Carol", "Marketing", 75000.0),
        ];
        
        let (before_allocs, _) = get_stats();
        println!("  Before sorting: {} allocations", before_allocs);

        {
            let sorted = Query::new(&employees)
                .order_by_float_desc(Employee::salary_r());
            
            let (after_allocs, _) = get_stats();
            println!("  After sorting: {} allocations", after_allocs);
            println!("  Cloned items: {} (expected: {})", after_allocs - before_allocs, employees.len());
            println!("  Sorted {} employees by salary", sorted.len());
            
            // sorted goes out of scope here
        }
        
        print_memory_status("After sorted results dropped");
    }
    
    print_memory_status("After employees dropped");
    println!();

    // ============================================================================
    // TEST 4: JOIN Operations - No Leaks
    // ============================================================================
    println!("═══════════════════════════════════════════════════════════════");
    println!("Test 4: JOIN operations - verify no memory leaks");
    println!("═══════════════════════════════════════════════════════════════\n");

    #[derive(Keypaths)]
    struct Department {
        id: u32,
        name: String,
        drop_tracker: DropTracker,
    }

    reset_stats();
    {
        let employees = vec![
            create_employee(1, "Alice", "Engineering", 95000.0),
            create_employee(2, "Bob", "Sales", 87000.0),
        ];

        let departments = vec![
            Department {
                id: 1,
                name: "Engineering".to_string(),
                drop_tracker: DropTracker::new(),
            },
            Department {
                id: 2,
                name: "Sales".to_string(),
                drop_tracker: DropTracker::new(),
            },
        ];

        print_memory_status("After creating data");

        {
            let results = JoinQuery::new(&employees, &departments)
                .inner_join(
                    Employee::department_r(),
                    Department::name_r(),
                    |emp, dept| (emp.name.clone(), dept.name.clone()),
                );
            
            println!("  Joined {} pairs", results.len());
            print_memory_status("During join results");
        }

        print_memory_status("After join results dropped");
    }

    print_memory_status("After all data dropped");
    println!();

    // ============================================================================
    // TEST 5: Large Scale - Memory Behavior
    // ============================================================================
    println!("═══════════════════════════════════════════════════════════════");
    println!("Test 5: Large scale (1000 items) - verify cleanup");
    println!("═══════════════════════════════════════════════════════════════\n");

    reset_stats();
    {
        let mut large_dataset = Vec::new();
        for i in 0..1000 {
            large_dataset.push(create_employee(
                i,
                &format!("Employee {}", i),
                if i % 3 == 0 { "Engineering" } else if i % 3 == 1 { "Sales" } else { "Marketing" },
                50000.0 + (i as f64 * 100.0),
            ));
        }

        let (initial_allocs, _) = get_stats();
        println!("  Created 1000 employees: {} allocations (~10MB)", initial_allocs);

        // Run complex query
        {
            let query = Query::new(&large_dataset)
                .where_(Employee::salary_r(), |&s| s > 80000.0)
                .where_(Employee::department_r(), |d| d == "Engineering");
            let results = query.all();
            
            println!("  Filtered to {} employees", results.len());
            
            let (during_allocs, _) = get_stats();
            let extra = during_allocs - initial_allocs;
            println!("  Extra allocations during query: {} (should be 0)", extra);
            
            if extra == 0 {
                println!("  ✅ Zero-copy filtering confirmed!");
            }
        }

        print_memory_status("After query results dropped");
    }

    print_memory_status("After 1000 employees dropped");
    println!();

    // ============================================================================
    // TEST 6: Explanation of 'static
    // ============================================================================
    println!("═══════════════════════════════════════════════════════════════");
    println!("Explanation: Why 'static is safe and doesn't leak");
    println!("═══════════════════════════════════════════════════════════════\n");

    println!("❓ What does T: 'static mean?\n");
    println!("  WRONG ❌: \"T lives for the entire program\"");
    println!("  RIGHT ✅: \"T doesn't contain non-'static references\"\n");

    println!("Examples:\n");
    println!("  struct OwnedData {{          // T: 'static ✅");
    println!("      id: u32,                 // Owned data");
    println!("      name: String,            // Owned data");
    println!("  }}");
    println!();
    println!("  struct WithReference<'a> {{  // NOT 'static ❌");
    println!("      data: &'a String,        // Contains reference");
    println!("  }}");
    println!();

    println!("Why we use T: 'static:\n");
    println!("  1. Store type in trait objects: Box<dyn Fn(&T) -> bool>");
    println!("  2. Prevent dangling references in closures");
    println!("  3. Ensure type safety at compile time");
    println!();

    println!("Lifetime of data:\n");
    println!("  • Data is owned by your Vec<T>");
    println!("  • Query just borrows &'a [T]");
    println!("  • When Vec<T> is dropped, all T are dropped");
    println!("  • No memory leaks possible!\n");

    // ============================================================================
    // TEST 7: Drop Order Verification
    // ============================================================================
    println!("═══════════════════════════════════════════════════════════════");
    println!("Test 7: Drop order - verify proper RAII");
    println!("═══════════════════════════════════════════════════════════════\n");

    reset_stats();
    
    println!("Creating scoped data...");
    {
        let employees = vec![
            create_employee(1, "Alice", "Engineering", 95000.0),
            create_employee(2, "Bob", "Sales", 87000.0),
        ];
        println!("  Created 2 employees");

        {
            println!("  Creating query...");
            let query = Query::new(&employees)
                .where_(Employee::department_r(), |dept| dept == "Engineering");
            
            {
                println!("  Executing query...");
                let results = query.all();
                println!("    Found {} results", results.len());
                println!("  Query results going out of scope...");
            }
            println!("  Results dropped (just Vec<&Employee>, no Employee drops)");
            
            println!("  Query going out of scope...");
        }
        println!("  Query dropped (just filters, no Employee drops)");
        
        println!("  Employees vector going out of scope...");
    }
    println!("  Employees dropped - NOW Employees are freed!\n");
    
    let (allocs, drops) = get_stats();
    println!("Final stats:");
    println!("  Allocated: {}", allocs);
    println!("  Dropped: {}", drops);
    println!("  Leaked: {}", allocs - drops);
    
    if allocs == drops {
        println!("\n✅ Perfect! All allocated memory was freed!");
    } else {
        println!("\n❌ Memory leak detected!");
    }

    // ============================================================================
    // TEST 8: Arc/Rc Compatibility
    // ============================================================================
    println!("\n═══════════════════════════════════════════════════════════════");
    println!("Test 8: Arc/Rc compatibility - shared ownership works");
    println!("═══════════════════════════════════════════════════════════════\n");

    {
        use std::sync::Arc;
        
        #[derive(Keypaths)]
        struct SharedData {
            id: u32,
            value: Arc<String>,  // Shared ownership
        }

        let shared_string = Arc::new("Shared Value".to_string());
        println!("  Arc strong count: {}", Arc::strong_count(&shared_string));

        let data = vec![
            SharedData { id: 1, value: Arc::clone(&shared_string) },
            SharedData { id: 2, value: Arc::clone(&shared_string) },
        ];
        
        println!("  Arc strong count after creating data: {}", Arc::strong_count(&shared_string));

        {
            let query = Query::new(&data)
                .where_(SharedData::id_r(), |&id| id > 0);
            let results = query.all();
            println!("  Found {} items", results.len());
            println!("  Arc strong count during query: {}", Arc::strong_count(&shared_string));
        }

        println!("  Arc strong count after query: {}", Arc::strong_count(&shared_string));
    }
    
    println!("  ✅ Arc reference counting works correctly!\n");

    // ============================================================================
    // TEST 9: Large Data Without Clone - Zero Copy
    // ============================================================================
    println!("═══════════════════════════════════════════════════════════════");
    println!("Test 9: Large data without Clone - verify zero-copy");
    println!("═══════════════════════════════════════════════════════════════\n");

    #[derive(Keypaths)]  // NO Clone!
    struct LargeRecord {
        id: u32,
        // Simulate 1MB of data that we DON'T want to clone
        huge_data: Vec<u8>,
    }

    {
        println!("  Creating 10 records (1MB each = 10MB total)...");
        let large_records: Vec<LargeRecord> = (0..10)
            .map(|i| LargeRecord {
                id: i,
                huge_data: vec![i as u8; 1_000_000], // 1MB each
            })
            .collect();

        println!("  Total memory: ~10MB");

        {
            println!("\n  Running query without Clone...");
            let query = Query::new(&large_records)
                .where_(LargeRecord::id_r(), |&id| id < 5);
            let results = query.all();  // Vec<&LargeRecord> - NO CLONING!
            
            println!("  Found {} records", results.len());
            println!("  Memory copied: 0 bytes (just references)");
            println!("  ✅ Zero-copy achieved!");
        }

        println!("\n  Query dropped - no memory freed (no cloning happened)");
    }
    
    println!("  Records dropped - 10MB freed\n");

    // ============================================================================
    // TEST 10: Lifetime Safety
    // ============================================================================
    println!("═══════════════════════════════════════════════════════════════");
    println!("Test 10: Lifetime safety - compiler prevents dangling refs");
    println!("═══════════════════════════════════════════════════════════════\n");

    println!("  The following code WILL NOT COMPILE (by design):\n");
    println!("  ```rust");
    println!("  let query;");
    println!("  {{");
    println!("      let data = vec![...];");
    println!("      query = Query::new(&data);  // data borrowed here");
    println!("  }}  // data dropped");
    println!("  let results = query.all();  // ❌ ERROR: data doesn't live long enough");
    println!("  ```\n");
    println!("  ✅ Rust's borrow checker prevents use-after-free!");
    println!("  ✅ 'static bound + lifetimes = memory safety guaranteed!\n");

    // ============================================================================
    // Summary
    // ============================================================================
    println!("═══════════════════════════════════════════════════════════════");
    println!("Summary: Memory Safety Guarantees");
    println!("═══════════════════════════════════════════════════════════════\n");

    let (total_allocs, total_drops) = get_stats();
    let leaked = total_allocs.saturating_sub(total_drops);

    println!("Overall Statistics:");
    println!("  Total allocations: {}", total_allocs);
    println!("  Total drops: {}", total_drops);
    println!("  Memory leaks: {}", leaked);

    if leaked == 0 {
        println!("\n🎉 VERIFIED: Zero memory leaks!\n");
    } else {
        println!("\n⚠️  WARNING: Potential memory leak detected!\n");
    }

    println!("Guarantees Verified:");
    println!("  ✅ 'static doesn't cause data to live forever");
    println!("  ✅ All allocated memory is properly freed");
    println!("  ✅ No memory leaks from queries");
    println!("  ✅ Query only holds references, not ownership");
    println!("  ✅ Rust's borrow checker prevents dangling references");
    println!("  ✅ RAII ensures proper cleanup");
    println!("  ✅ Zero-copy operations don't allocate");
    println!("  ✅ Clone operations are explicit and controlled\n");

    println!("Performance Benefits:");
    println!("  ✅ Filtering: 0 bytes copied (v0.2.0) vs 10MB (v0.1.0)");
    println!("  ✅ Counting: 0 bytes copied");
    println!("  ✅ Aggregations: 0 bytes copied");
    println!("  ✅ Only ordering/grouping clone when needed\n");

    println!("Safety Guarantees:");
    println!("  ✅ Compile-time prevention of dangling references");
    println!("  ✅ No use-after-free possible");
    println!("  ✅ No double-free possible");
    println!("  ✅ Automatic cleanup via RAII\n");

    println!("✓ All memory safety tests PASSED!\n");
}

pub fn inner_join<K, O, F>( &self, left_key: KeyPaths<L, K>, right_key: KeyPaths<R, K>, mapper: F, ) -> Vec<O>

where K: Eq + Hash + Clone + 'static, F: Fn(&L, &R) -> O,

Performs an inner join between two collections.

Returns only the pairs where the join keys match. Uses a hash-based algorithm for O(n + m) performance.

Arguments

• left_key - Key-path to the join field in the left collection
• right_key - Key-path to the join field in the right collection
• mapper - Function to transform matching pairs into the result type

Example

let results = JoinQuery::new(&users, &orders)
    .inner_join(
        User::id_r(),
        Order::user_id_r(),
        |user, order| UserOrder {
            user_name: user.name.clone(),
            order_total: order.total,
        }
    );

Examples found in repository

examples/without_clone.rs (lines 149-156)

fn main() {
    println!("╔════════════════════════════════════════════════════════════╗");
    println!("║  Query Builder Without Clone - Performance Optimization   ║");
    println!("╚════════════════════════════════════════════════════════════╝\n");

    let employees = vec![
        Employee {
            id: 1,
            name: "Alice".to_string(),
            email: "alice@example.com".to_string(),
            department: "Engineering".to_string(),
            salary: 95000.0,
            large_data: vec![0; 1000], // Large data - expensive to clone!
        },
        Employee {
            id: 2,
            name: "Bob".to_string(),
            email: "bob@example.com".to_string(),
            department: "Engineering".to_string(),
            salary: 87000.0,
            large_data: vec![0; 1000],
        },
        Employee {
            id: 3,
            name: "Carol".to_string(),
            email: "carol@example.com".to_string(),
            department: "Sales".to_string(),
            salary: 75000.0,
            large_data: vec![0; 1000],
        },
    ];

    let departments = vec![
        Department {
            id: 1,
            name: "Engineering".to_string(),
            budget: 1000000.0,
        },
        Department {
            id: 2,
            name: "Sales".to_string(),
            budget: 500000.0,
        },
    ];

    println!("✅ Operations that DON'T require Clone:\n");

    // 1. WHERE filtering - returns Vec<&T>
    println!("1. WHERE filtering (returns references)");
    let query = Query::new(&employees)
        .where_(Employee::department_r(), |dept| dept == "Engineering");
    let engineering = query.all();
    println!("   Found {} engineering employees", engineering.len());
    for emp in &engineering {
        println!("     - {}: ${:.0}", emp.name, emp.salary);
    }

    // 2. COUNT - no cloning needed
    println!("\n2. COUNT aggregation");
    let count = Query::new(&employees)
        .where_(Employee::salary_r(), |&sal| sal > 80000.0)
        .count();
    println!("   {} employees earn over $80k", count);

    // 3. SELECT - only clones the selected field
    println!("\n3. SELECT (only selected fields are cloned)");
    let names: Vec<String> = Query::new(&employees)
        .select(Employee::name_r());
    println!("   Employee names: {:?}", names);

    // 4. FIRST - returns Option<&T>
    println!("\n4. FIRST (returns reference)");
    let query = Query::new(&employees)
        .where_(Employee::salary_r(), |&sal| sal > 90000.0);
    if let Some(emp) = query.first() {
        println!("   First high earner: {} (${:.0})", emp.name, emp.salary);
    }

    // 5. SUM/AVG aggregations - no cloning
    println!("\n5. Aggregations (SUM/AVG)");
    let eng_query = Query::new(&employees)
        .where_(Employee::department_r(), |dept| dept == "Engineering");
    let total = eng_query.sum(Employee::salary_r());
    let avg = eng_query.avg(Employee::salary_r()).unwrap_or(0.0);
    println!("   Engineering total: ${:.0}", total);
    println!("   Engineering average: ${:.0}", avg);

    // 6. MIN/MAX - no cloning
    println!("\n6. MIN/MAX");
    let min = Query::new(&employees).min_float(Employee::salary_r());
    let max = Query::new(&employees).max_float(Employee::salary_r());
    println!("   Salary range: ${:.0} - ${:.0}", min.unwrap(), max.unwrap());

    // 7. LIMIT - returns Vec<&T>
    println!("\n7. LIMIT (returns references)");
    let query = Query::new(&employees);
    let first_two = query.limit(2);
    println!("   First 2 employees:");
    for emp in &first_two {
        println!("     - {}", emp.name);
    }

    // 8. SKIP/Pagination - returns Vec<&T>
    println!("\n8. SKIP/Pagination (returns references)");
    let query = Query::new(&employees);
    let page_2 = query.skip(2).limit(1);
    println!("   Page 2:");
    for emp in &page_2 {
        println!("     - {}", emp.name);
    }

    // 9. EXISTS - just checks
    println!("\n9. EXISTS check");
    let has_sales = Query::new(&employees)
        .where_(Employee::department_r(), |dept| dept == "Sales")
        .exists();
    println!("   Has Sales employees: {}", has_sales);

    // 10. JOIN - no Clone required on L or R!
    println!("\n10. JOIN operations (no Clone required!)");
    let results = JoinQuery::new(&employees, &departments)
        .inner_join(
            Employee::department_r(),
            Department::name_r(),
            |emp, dept| {
                // Mapper only clones what it needs for the result
                (emp.name.clone(), dept.budget)
            },
        );
    println!("   Employee-Department pairs:");
    for (name, budget) in &results {
        println!("     - {} works in dept with ${:.0} budget", name, budget);
    }

    println!("\n╔════════════════════════════════════════════════════════════╗");
    println!("║  Operations that REQUIRE Clone (only when needed)         ║");
    println!("╚════════════════════════════════════════════════════════════╝\n");

    println!("⚠️  The following operations require Clone because they return owned Vec<T>:");
    println!("   - order_by() / order_by_desc()");
    println!("   - order_by_float() / order_by_float_desc()");
    println!("   - group_by()");
    println!("\n   To use these, add #[derive(Clone)] to your struct:");
    println!("   ```rust");
    println!("   #[derive(Clone, Keypaths)]  // Add Clone here");
    println!("   struct Employee {{ ... }}");
    println!("   ```");

    println!("\n╔════════════════════════════════════════════════════════════╗");
    println!("║  Performance Benefits                                      ║");
    println!("╚════════════════════════════════════════════════════════════╝\n");

    println!("✅ Zero cloning for most operations");
    println!("✅ Work with large structs efficiently");
    println!("✅ No unnecessary memory allocations");
    println!("✅ Only clone when you actually need owned data");
    println!("✅ Pay for what you use");

    println!("\n✓ Example complete! Most operations work without Clone.\n");
}

examples/doc_examples.rs (lines 151-155)

fn main() {
    println!("Testing documentation examples...\n");

    // Example from README - Quick Start
    println!("Test 1: README Quick Start Example");
    {
        let products = vec![
            Product { id: 1, name: "Laptop".to_string(), price: 999.99, category: "Electronics".to_string(), stock: 15, rating: 4.5 },
            Product { id: 2, name: "Mouse".to_string(), price: 29.99, category: "Electronics".to_string(), stock: 50, rating: 4.0 },
            Product { id: 3, name: "Desk".to_string(), price: 299.99, category: "Furniture".to_string(), stock: 10, rating: 4.8 },
        ];

        let affordable_query = Query::new(&products)
            .where_(Product::category_r(), |cat| cat == "Electronics")
            .where_(Product::price_r(), |&price| price < 100.0);
        let affordable_electronics = affordable_query.all();

        println!("  Found {} affordable electronics ✅", affordable_electronics.len());
    }

    // Example from README - Filtering
    println!("\nTest 2: Filtering Example");
    {
        let products = vec![
            Product { id: 1, name: "Laptop".to_string(), price: 999.99, category: "Electronics".to_string(), stock: 15, rating: 4.5 },
        ];

        let electronics_query = Query::new(&products)
            .where_(Product::category_r(), |cat| cat == "Electronics");
        let electronics = electronics_query.all();

        println!("  Found {} electronics ✅", electronics.len());
    }

    // Example from README - Selecting
    println!("\nTest 3: Selecting Fields Example");
    {
        let products = vec![
            Product { id: 1, name: "Laptop".to_string(), price: 999.99, category: "Electronics".to_string(), stock: 15, rating: 4.5 },
            Product { id: 2, name: "Mouse".to_string(), price: 29.99, category: "Electronics".to_string(), stock: 50, rating: 4.0 },
        ];

        let names: Vec<String> = Query::new(&products)
            .select(Product::name_r());

        println!("  Selected {} names ✅", names.len());
    }

    // Example from README - Ordering
    println!("\nTest 4: Ordering Example");
    {
        let products = vec![
            Product { id: 1, name: "Laptop".to_string(), price: 999.99, category: "Electronics".to_string(), stock: 15, rating: 4.5 },
            Product { id: 2, name: "Mouse".to_string(), price: 29.99, category: "Electronics".to_string(), stock: 50, rating: 4.0 },
        ];

        let by_price = Query::new(&products).order_by_float(Product::price_r());
        println!("  Ordered {} products ✅", by_price.len());
    }

    // Example from README - Aggregations
    println!("\nTest 5: Aggregations Example");
    {
        let products = vec![
            Product { id: 1, name: "Laptop".to_string(), price: 999.99, category: "Electronics".to_string(), stock: 15, rating: 4.5 },
            Product { id: 2, name: "Mouse".to_string(), price: 29.99, category: "Electronics".to_string(), stock: 50, rating: 4.0 },
        ];

        let electronics_query = Query::new(&products)
            .where_(Product::category_r(), |cat| cat == "Electronics");

        let count = electronics_query.count();
        let total_value: f64 = electronics_query.sum(Product::price_r());
        let avg_price = electronics_query.avg(Product::price_r()).unwrap_or(0.0);

        println!("  Count: {}, Total: ${:.2}, Avg: ${:.2} ✅", count, total_value, avg_price);
    }

    // Example from README - Grouping
    println!("\nTest 6: Grouping Example");
    {
        let products = vec![
            Product { id: 1, name: "Laptop".to_string(), price: 999.99, category: "Electronics".to_string(), stock: 15, rating: 4.5 },
            Product { id: 2, name: "Mouse".to_string(), price: 29.99, category: "Electronics".to_string(), stock: 50, rating: 4.0 },
            Product { id: 3, name: "Desk".to_string(), price: 299.99, category: "Furniture".to_string(), stock: 10, rating: 4.8 },
        ];

        let by_category = Query::new(&products).group_by(Product::category_r());
        println!("  Grouped into {} categories ✅", by_category.len());
    }

    // Example from README - Pagination
    println!("\nTest 7: Pagination Example");
    {
        let products = vec![
            Product { id: 1, name: "P1".to_string(), price: 10.0, category: "A".to_string(), stock: 1, rating: 4.0 },
            Product { id: 2, name: "P2".to_string(), price: 20.0, category: "A".to_string(), stock: 1, rating: 4.0 },
            Product { id: 3, name: "P3".to_string(), price: 30.0, category: "A".to_string(), stock: 1, rating: 4.0 },
        ];

        let query = Query::new(&products);
        let first_10 = query.limit(10);
        let page_1 = query.skip(0).limit(10);

        println!("  Limited to {} products ✅", first_10.len());
        println!("  Page 1 has {} products ✅", page_1.len());
    }

    // Example from README - Join
    println!("\nTest 8: Join Example");
    {
        let users = vec![
            User { id: 1, name: "Alice".to_string() },
            User { id: 2, name: "Bob".to_string() },
        ];

        let orders = vec![
            Order { id: 101, user_id: 1, total: 99.99 },
            Order { id: 102, user_id: 1, total: 149.99 },
        ];

        let user_orders = JoinQuery::new(&users, &orders).inner_join(
            User::id_r(),
            Order::user_id_r(),
            |user, order| (user.name.clone(), order.total),
        );

        println!("  Joined {} user-order pairs ✅", user_orders.len());
    }

    // Example from SQL_COMPARISON - SELECT with WHERE
    println!("\nTest 9: SQL Comparison - SELECT with WHERE");
    {
        #[derive(Clone, Keypaths)]
        struct Employee {
            department: String,
        }

        let employees = vec![
            Employee { department: "Engineering".to_string() },
            Employee { department: "Sales".to_string() },
        ];

        let engineering_query = Query::new(&employees)
            .where_(Employee::department_r(), |dept| dept == "Engineering");
        let engineering = engineering_query.all();

        println!("  Found {} engineering employees ✅", engineering.len());
    }

    // Example from USAGE.md - Complex Filtering
    println!("\nTest 10: USAGE - Complex Filtering");
    {
        let products = vec![
            Product { id: 1, name: "Laptop".to_string(), price: 999.99, category: "Electronics".to_string(), stock: 15, rating: 4.5 },
        ];

        let results_query = Query::new(&products)
            .where_(Product::category_r(), |cat| cat == "Electronics")
            .where_(Product::price_r(), |&price| price >= 100.0 && price <= 500.0)
            .where_(Product::stock_r(), |&stock| stock > 10);
        let results = results_query.order_by_float(Product::price_r());

        println!("  Filtered {} products ✅", results.len());
    }

    println!("\n✅ All documentation examples compile and run successfully!");
}

examples/join_query_builder.rs (lines 205-215)

fn main() {
    println!("=== Join Query Builder Demo ===\n");

    let (users, orders, products) = create_sample_data();

    println!("Database:");
    println!("  Users: {}", users.len());
    println!("  Orders: {}", orders.len());
    println!("  Products: {}\n", products.len());

    // Join 1: Inner join Users and Orders
    println!("--- Join 1: Users with Their Orders ---");
    let user_orders = JoinQuery::new(&users, &orders).inner_join(
        User::id_r(),
        Order::user_id_r(),
        |user, order| UserOrder {
            user_name: user.name.clone(),
            user_email: user.email.clone(),
            order_id: order.id,
            quantity: order.quantity,
            total: order.total,
        },
    );

    for uo in &user_orders {
        println!(
            "  • {} - Order #{} - {} items - ${:.2}",
            uo.user_name, uo.order_id, uo.quantity, uo.total
        );
    }
    println!("Total: {} user-order pairs", user_orders.len());

    // Join 2: Three-way join (Orders -> Users, Orders -> Products)
    println!("\n--- Join 2: Complete Order Details (3-Way Join) ---");
    
    // First join: Orders with Users
    let orders_with_users = JoinQuery::new(&orders, &users).inner_join(
        Order::user_id_r(),
        User::id_r(),
        |order, user| (order.clone(), user.clone()),
    );

    // Second join: (Orders+Users) with Products
    let mut order_details = Vec::new();
    for (order, user) in &orders_with_users {
        for product in &products {
            if order.product_id == product.id {
                order_details.push(OrderDetail {
                    order_id: order.id,
                    user_name: user.name.clone(),
                    product_name: product.name.clone(),
                    quantity: order.quantity,
                    price: product.price,
                    total: order.total,
                });
            }
        }
    }

    for od in &order_details {
        println!(
            "  • Order #{}: {} bought {} x {} @ ${:.2} = ${:.2}",
            od.order_id, od.user_name, od.quantity, od.product_name, od.price, od.total
        );
    }

    // Join 3: Left join to show all users (including those without orders)
    println!("\n--- Join 3: All Users with Order Count (Left Join) ---");
    
    // Use left_join to get all users with their orders (or None)
    let user_order_pairs = JoinQuery::new(&users, &orders).left_join(
        User::id_r(),
        Order::user_id_r(),
        |user, order| (user.clone(), order.map(|o| o.clone())),
    );

    // Group by user to count orders
    let mut user_stats: HashMap<u32, (String, String, usize, f64)> = HashMap::new();
    for (user, order) in &user_order_pairs {
        let entry = user_stats
            .entry(user.id)
            .or_insert_with(|| (user.name.clone(), user.city.clone(), 0, 0.0));
        if let Some(order) = order {
            entry.2 += 1; // order count
            entry.3 += order.total; // total spent
        }
    }

    let mut user_order_stats: Vec<_> = user_stats
        .into_iter()
        .map(|(_, (name, city, count, total))| UserOrderCount {
            user_name: name,
            user_city: city,
            order_count: count,
            total_spent: total,
        })
        .collect();

    user_order_stats.sort_by(|a, b| a.user_name.cmp(&b.user_name));

    for stat in &user_order_stats {
        if stat.order_count > 0 {
            println!(
                "  • {} ({}) - {} orders - ${:.2} total",
                stat.user_name, stat.user_city, stat.order_count, stat.total_spent
            );
        } else {
            println!("  • {} ({}) - No orders yet", stat.user_name, stat.user_city);
        }
    }

    // Join 4: Aggregated join - Category sales analysis
    println!("\n--- Join 4: Sales by Product Category ---");

    // Join orders with products to get category information
    let order_products = JoinQuery::new(&orders, &products).inner_join(
        Order::product_id_r(),
        Product::id_r(),
        |order, product| (order.clone(), product.clone()),
    );

    // Aggregate by category
    let mut category_stats: HashMap<String, (Vec<u32>, f64, std::collections::HashSet<u32>)> =
        HashMap::new();

    for (order, product) in &order_products {
        let entry = category_stats
            .entry(product.category.clone())
            .or_insert_with(|| (Vec::new(), 0.0, std::collections::HashSet::new()));
        entry.0.push(order.id);
        entry.1 += order.total;
        entry.2.insert(order.user_id);
    }

    let mut category_sales: Vec<CategorySales> = category_stats
        .into_iter()
        .map(|(category, (orders, revenue, customers))| CategorySales {
            category,
            total_orders: orders.len(),
            total_revenue: revenue,
            unique_customers: customers.len(),
        })
        .collect();

    category_sales.sort_by(|a, b| {
        b.total_revenue
            .partial_cmp(&a.total_revenue)
            .unwrap_or(std::cmp::Ordering::Equal)
    });

    for cs in &category_sales {
        println!(
            "  • {}: {} orders - ${:.2} revenue - {} customers",
            cs.category, cs.total_orders, cs.total_revenue, cs.unique_customers
        );
    }

    // Join 5: Filtered join - High value orders
    println!("\n--- Join 5: High Value Orders (>$100) with User Details ---");
    let high_value_orders = JoinQuery::new(&orders, &users).inner_join_where(
        Order::user_id_r(),
        User::id_r(),
        |order, _user| order.total > 100.0,
        |order, user| (user.name.clone(), order.id, order.total),
    );

    for (name, order_id, total) in &high_value_orders {
        println!("  • {} - Order #{} - ${:.2}", name, order_id, total);
    }

    // Join 6: Users in same city analysis
    println!("\n--- Join 6: Users from Same City ---");
    let user_pairs = JoinQuery::new(&users, &users).inner_join_where(
        User::city_r(),
        User::city_r(),
        |u1, u2| u1.id < u2.id, // Avoid duplicates and self-pairs
        |u1, u2| (u1.name.clone(), u2.name.clone(), u1.city.clone()),
    );

    for (name1, name2, city) in &user_pairs {
        println!("  • {} and {} both live in {}", name1, name2, city);
    }

    // Join 7: Product popularity
    println!("\n--- Join 7: Product Popularity Ranking ---");
    
    // Join orders with products
    let product_order_pairs = JoinQuery::new(&products, &orders).inner_join(
        Product::id_r(),
        Order::product_id_r(),
        |product, order| (product.clone(), order.clone()),
    );

    // Aggregate by product
    let mut product_sales: HashMap<u32, (String, usize, u32, f64)> = HashMap::new();
    for (product, order) in &product_order_pairs {
        let entry = product_sales
            .entry(product.id)
            .or_insert_with(|| (product.name.clone(), 0, 0, 0.0));
        entry.1 += 1; // order count
        entry.2 += order.quantity; // total quantity
        entry.3 += order.total; // total revenue
    }

    let mut popularity: Vec<_> = product_sales.into_iter().collect();
    popularity.sort_by(|a, b| b.1 .1.cmp(&a.1 .1)); // sort by order count

    for (_, (name, order_count, total_qty, revenue)) in &popularity {
        println!(
            "  • {} - {} orders - {} units - ${:.2}",
            name, order_count, total_qty, revenue
        );
    }

    // Join 8: User spending by city
    println!("\n--- Join 8: Total Spending by City ---");
    
    // Join users with orders to get city and spending info
    let user_city_orders = JoinQuery::new(&users, &orders).inner_join(
        User::id_r(),
        Order::user_id_r(),
        |user, order| (user.city.clone(), order.total, user.id),
    );

    // Aggregate by city
    let mut city_spending: HashMap<String, (f64, std::collections::HashSet<u32>)> = HashMap::new();
    for (city, total, user_id) in &user_city_orders {
        let entry = city_spending
            .entry(city.clone())
            .or_insert_with(|| (0.0, std::collections::HashSet::new()));
        entry.0 += total;
        entry.1.insert(*user_id);
    }

    let mut city_stats: Vec<_> = city_spending
        .into_iter()
        .map(|(city, (total, customers))| (city, total, customers.len()))
        .collect();
    
    city_stats.sort_by(|a, b| {
        b.1.partial_cmp(&a.1).unwrap_or(std::cmp::Ordering::Equal)
    });

    for (city, total, customer_count) in &city_stats {
        println!(
            "  • {} - ${:.2} total - {} customers - ${:.2} avg",
            city,
            total,
            customer_count,
            total / *customer_count as f64
        );
    }

    // Statistics summary
    println!("\n=== Summary Statistics ===");
    println!("Total orders: {}", orders.len());
    
    let total_revenue: f64 = orders.iter().map(|o| o.total).sum();
    println!("Total revenue: ${:.2}", total_revenue);
    println!("Average order value: ${:.2}", total_revenue / orders.len() as f64);
    
    // Count unique customers using a join
    let unique_customers: std::collections::HashSet<u32> = 
        orders.iter().map(|o| o.user_id).collect();
    println!("Active customers: {}", unique_customers.len());
    println!(
        "Average orders per customer: {:.1}",
        orders.len() as f64 / unique_customers.len() as f64
    );

    println!("\n✓ Join query builder demo complete!");
}

examples/sql_comparison.rs (lines 333-337)

fn main() {
    let employees = create_employees();
    let departments = create_departments();
    let projects = create_projects();

    println!("\n╔════════════════════════════════════════════════════════════════════════╗");
    println!("║     SQL vs Rust Query Builder Comparison                              ║");
    println!("║     Demonstrating equivalent operations                               ║");
    println!("╚════════════════════════════════════════════════════════════════════════╝");

    // ============================================================================
    // EXAMPLE 1: Simple SELECT with WHERE
    // ============================================================================
    print_separator("Example 1: SELECT with WHERE clause");
    print_query(
        "SELECT * FROM employees WHERE department = 'Engineering';",
        "Filter employees by department"
    );

    let eng_query = Query::new(&employees)
        .where_(Employee::department_r(), |dept| dept == "Engineering");
    let engineering_employees = eng_query.all();

    for emp in &engineering_employees {
        println!("  ID: {}, Name: {}, Salary: ${:.2}", emp.id, emp.name, emp.salary);
    }
    println!("\nRust Query Builder:");
    println!("Query::new(&employees)");
    println!("    .where_(Employee::department_r(), |dept| dept == \"Engineering\")");
    println!("    .all()");
    println!("✓ Found {} employees", engineering_employees.len());

    // ============================================================================
    // EXAMPLE 2: SELECT specific columns
    // ============================================================================
    print_separator("Example 2: SELECT specific columns (Projection)");
    print_query(
        "SELECT name FROM employees WHERE salary > 80000;",
        "Get names of high-earning employees"
    );

    let high_earners = Query::new(&employees)
        .where_(Employee::salary_r(), |&salary| salary > 80000.0)
        .select(Employee::name_r());

    for name in &high_earners {
        println!("  {}", name);
    }
    println!("\nRust Query Builder:");
    println!("Query::new(&employees)");
    println!("    .where_(Employee::salary_r(), |&salary| salary > 80000.0)");
    println!("    .select(Employee::name_r())");
    println!("✓ Found {} employees", high_earners.len());

    // ============================================================================
    // EXAMPLE 3: COUNT
    // ============================================================================
    print_separator("Example 3: COUNT aggregation");
    print_query(
        "SELECT COUNT(*) FROM employees WHERE age < 30;",
        "Count young employees"
    );

    let young_count = Query::new(&employees)
        .where_(Employee::age_r(), |&age| age < 30)
        .count();

    println!("  Count: {}", young_count);
    println!("\nRust Query Builder:");
    println!("Query::new(&employees)");
    println!("    .where_(Employee::age_r(), |&age| age < 30)");
    println!("    .count()");
    println!("✓ Result: {}", young_count);

    // ============================================================================
    // EXAMPLE 4: SUM, AVG, MIN, MAX
    // ============================================================================
    print_separator("Example 4: Aggregate functions (SUM, AVG, MIN, MAX)");
    print_query(
        "SELECT \n\
         SUM(salary) as total_salary,\n\
         AVG(salary) as avg_salary,\n\
         MIN(salary) as min_salary,\n\
         MAX(salary) as max_salary\n\
         FROM employees WHERE department = 'Engineering';",
        "Engineering department salary statistics"
    );

    let eng_query = Query::new(&employees)
        .where_(Employee::department_r(), |dept| dept == "Engineering");

    let total = eng_query.sum(Employee::salary_r());
    let avg = eng_query.avg(Employee::salary_r()).unwrap_or(0.0);
    let min = eng_query.min_float(Employee::salary_r()).unwrap_or(0.0);
    let max = eng_query.max_float(Employee::salary_r()).unwrap_or(0.0);

    println!("  Total Salary: ${:.2}", total);
    println!("  Avg Salary:   ${:.2}", avg);
    println!("  Min Salary:   ${:.2}", min);
    println!("  Max Salary:   ${:.2}", max);

    println!("\nRust Query Builder:");
    println!("let query = Query::new(&employees)");
    println!("    .where_(Employee::department_r(), |dept| dept == \"Engineering\");");
    println!("query.sum(Employee::salary_r())  // ${:.2}", total);
    println!("query.avg(Employee::salary_r())  // ${:.2}", avg);
    println!("query.min_float(Employee::salary_r())  // ${:.2}", min);
    println!("query.max_float(Employee::salary_r())  // ${:.2}", max);

    // ============================================================================
    // EXAMPLE 5: GROUP BY with aggregation
    // ============================================================================
    print_separator("Example 5: GROUP BY with aggregation");
    print_query(
        "SELECT \n\
         department,\n\
         COUNT(*) as emp_count,\n\
         AVG(salary) as avg_salary\n\
         FROM employees\n\
         GROUP BY department;",
        "Statistics by department"
    );

    let by_dept = Query::new(&employees).group_by(Employee::department_r());

    for (dept, emps) in &by_dept {
        let dept_query = Query::new(emps);
        let count = emps.len();
        let avg_sal = dept_query.avg(Employee::salary_r()).unwrap_or(0.0);
        println!("  {}: {} employees, avg salary ${:.2}", dept, count, avg_sal);
    }

    println!("\nRust Query Builder:");
    println!("let by_dept = Query::new(&employees).group_by(Employee::department_r());");
    println!("for (dept, emps) in &by_dept {{");
    println!("    let dept_query = Query::new(emps);");
    println!("    dept_query.avg(Employee::salary_r())");
    println!("}}");

    // ============================================================================
    // EXAMPLE 6: ORDER BY
    // ============================================================================
    print_separator("Example 6: ORDER BY");
    print_query(
        "SELECT name, salary FROM employees\n\
         WHERE department = 'Sales'\n\
         ORDER BY salary DESC;",
        "Sales employees ordered by salary (descending)"
    );

    let sales_sorted = Query::new(&employees)
        .where_(Employee::department_r(), |dept| dept == "Sales")
        .order_by_float_desc(Employee::salary_r());

    for emp in &sales_sorted {
        println!("  {}: ${:.2}", emp.name, emp.salary);
    }

    println!("\nRust Query Builder:");
    println!("Query::new(&employees)");
    println!("    .where_(Employee::department_r(), |dept| dept == \"Sales\")");
    println!("    .order_by_float_desc(Employee::salary_r())");

    // ============================================================================
    // EXAMPLE 7: Multiple WHERE conditions (AND)
    // ============================================================================
    print_separator("Example 7: Multiple WHERE conditions (AND)");
    print_query(
        "SELECT * FROM employees\n\
         WHERE salary > 70000 AND age < 35;",
        "High-earning young employees"
    );

    let filter_query = Query::new(&employees)
        .where_(Employee::salary_r(), |&salary| salary > 70000.0)
        .where_(Employee::age_r(), |&age| age < 35);
    let filtered = filter_query.all();

    for emp in &filtered {
        println!("  {}: Age {}, Salary ${:.2}", emp.name, emp.age, emp.salary);
    }

    println!("\nRust Query Builder:");
    println!("Query::new(&employees)");
    println!("    .where_(Employee::salary_r(), |&salary| salary > 70000.0)");
    println!("    .where_(Employee::age_r(), |&age| age < 35)");
    println!("    .all()");
    println!("✓ Found {} employees", filtered.len());

    // ============================================================================
    // EXAMPLE 8: LIMIT (TOP N)
    // ============================================================================
    print_separator("Example 8: LIMIT / TOP N");
    print_query(
        "SELECT TOP 3 name, salary FROM employees\n\
         ORDER BY salary DESC;",
        "Top 3 highest paid employees"
    );

    let top_earners = Query::new(&employees)
        .order_by_float_desc(Employee::salary_r());

    for (i, emp) in top_earners.iter().take(3).enumerate() {
        println!("  {}. {}: ${:.2}", i + 1, emp.name, emp.salary);
    }

    println!("\nRust Query Builder:");
    println!("Query::new(&employees)");
    println!("    .order_by_float_desc(Employee::salary_r())");
    println!("    .into_iter().take(3)");

    // ============================================================================
    // EXAMPLE 9: OFFSET and LIMIT (Pagination)
    // ============================================================================
    print_separator("Example 9: OFFSET and LIMIT (Pagination)");
    print_query(
        "SELECT name FROM employees\n\
         ORDER BY name\n\
         LIMIT 3 OFFSET 3;",
        "Page 2 of employee names (3 per page)"
    );

    let page_2 = Query::new(&employees)
        .order_by(Employee::name_r())
        .into_iter()
        .skip(3)
        .take(3)
        .collect::<Vec<_>>();

    for emp in &page_2 {
        println!("  {}", emp.name);
    }

    println!("\nRust Query Builder:");
    println!("Query::new(&employees)");
    println!("    .order_by(Employee::name_r())");
    println!("    .into_iter().skip(3).take(3)");

    // ============================================================================
    // EXAMPLE 10: INNER JOIN
    // ============================================================================
    print_separator("Example 10: INNER JOIN");
    print_query(
        "SELECT e.name, d.name as dept_name, d.budget\n\
         FROM employees e\n\
         INNER JOIN departments d ON e.department = d.name;",
        "Employees with their department info"
    );

    let emp_dept = JoinQuery::new(&employees, &departments).inner_join(
        Employee::department_r(),
        Department::name_r(),
        |emp, dept| (emp.name.clone(), dept.name.clone(), dept.budget),
    );

    for (emp_name, dept_name, budget) in emp_dept.iter().take(5) {
        println!("  {} works in {} (Budget: ${:.0})", emp_name, dept_name, budget);
    }
    println!("  ... (showing first 5)");

    println!("\nRust Query Builder:");
    println!("JoinQuery::new(&employees, &departments).inner_join(");
    println!("    Employee::department_r(),");
    println!("    Department::name_r(),");
    println!("    |emp, dept| (emp.name.clone(), dept.name.clone(), dept.budget)");
    println!(")");
    println!("✓ Found {} matches", emp_dept.len());

    // ============================================================================
    // EXAMPLE 11: GROUP BY with HAVING equivalent
    // ============================================================================
    print_separator("Example 11: GROUP BY with filtering (HAVING equivalent)");
    print_query(
        "SELECT city, COUNT(*) as emp_count, AVG(salary) as avg_salary\n\
         FROM employees\n\
         GROUP BY city\n\
         HAVING COUNT(*) > 1;",
        "Cities with multiple employees"
    );

    let by_city = Query::new(&employees).group_by(Employee::city_r());
    let mut city_stats: Vec<_> = by_city
        .iter()
        .filter(|(_, emps)| emps.len() > 1) // HAVING equivalent
        .map(|(city, emps)| {
            let avg_sal = Query::new(emps).avg(Employee::salary_r()).unwrap_or(0.0);
            (city.clone(), emps.len(), avg_sal)
        })
        .collect();
    
    city_stats.sort_by(|a, b| b.1.cmp(&a.1)); // Sort by count

    for (city, count, avg_sal) in &city_stats {
        println!("  {}: {} employees, avg salary ${:.2}", city, count, avg_sal);
    }

    println!("\nRust Query Builder:");
    println!("let by_city = Query::new(&employees).group_by(Employee::city_r());");
    println!("by_city.iter()");
    println!("    .filter(|(_, emps)| emps.len() > 1)  // HAVING equivalent");
    println!("    .map(|(city, emps)| {{");
    println!("        let avg = Query::new(emps).avg(Employee::salary_r());");
    println!("        (city, emps.len(), avg)");
    println!("    }})");

    // ============================================================================
    // EXAMPLE 12: Complex query with multiple operations
    // ============================================================================
    print_separator("Example 12: Complex multi-operation query");
    print_query(
        "SELECT name, salary, age\n\
         FROM employees\n\
         WHERE department IN ('Engineering', 'Sales')\n\
         AND salary BETWEEN 80000 AND 100000\n\
         AND age >= 30\n\
         ORDER BY salary DESC;",
        "Experienced mid-to-senior level employees in core departments"
    );

    let complex_query = Query::new(&employees)
        .where_(Employee::department_r(), |dept| dept == "Engineering" || dept == "Sales")
        .where_(Employee::salary_r(), |&sal| sal >= 80000.0 && sal <= 100000.0)
        .where_(Employee::age_r(), |&age| age >= 30)
        .order_by_float_desc(Employee::salary_r());

    for emp in &complex_query {
        println!("  {}: Age {}, {} dept, ${:.2}", emp.name, emp.age, emp.department, emp.salary);
    }

    println!("\nRust Query Builder:");
    println!("Query::new(&employees)");
    println!("    .where_(Employee::department_r(), |dept| dept == \"Engineering\" || dept == \"Sales\")");
    println!("    .where_(Employee::salary_r(), |&sal| sal >= 80000.0 && sal <= 100000.0)");
    println!("    .where_(Employee::age_r(), |&age| age >= 30)");
    println!("    .order_by_float_desc(Employee::salary_r())");
    println!("✓ Found {} employees", complex_query.len());

    // ============================================================================
    // EXAMPLE 13: Three-table JOIN
    // ============================================================================
    print_separator("Example 13: Three-table JOIN");
    print_query(
        "SELECT p.name as project, d.name as department, d.location\n\
         FROM projects p\n\
         INNER JOIN departments d ON p.department_id = d.id;",
        "Projects with their department details"
    );

    let proj_dept = JoinQuery::new(&projects, &departments).inner_join(
        Project::department_id_r(),
        Department::id_r(),
        |proj, dept| {
            (proj.name.clone(), dept.name.clone(), dept.location.clone(), proj.budget)
        },
    );

    for (proj_name, dept_name, location, budget) in &proj_dept {
        println!("  {}: {} dept in {} (${:.0})", proj_name, dept_name, location, budget);
    }

    println!("\nRust Query Builder:");
    println!("JoinQuery::new(&projects, &departments).inner_join(");
    println!("    Project::department_id_r(),");
    println!("    Department::id_r(),");
    println!("    |proj, dept| (proj.name, dept.name, dept.location, proj.budget)");
    println!(")");

    // ============================================================================
    // EXAMPLE 14: Subquery equivalent (using intermediate results)
    // ============================================================================
    print_separator("Example 14: Subquery equivalent");
    print_query(
        "SELECT * FROM employees\n\
         WHERE salary > (SELECT AVG(salary) FROM employees);",
        "Employees earning above average"
    );

    let avg_salary = Query::new(&employees)
        .avg(Employee::salary_r())
        .unwrap_or(0.0);

    let above_avg_query = Query::new(&employees)
        .where_(Employee::salary_r(), move |&sal| sal > avg_salary);
    let above_avg = above_avg_query.all();

    println!("  Average salary: ${:.2}", avg_salary);
    for emp in &above_avg {
        println!("  {}: ${:.2} ({:.1}% above average)", 
            emp.name, emp.salary, ((emp.salary - avg_salary) / avg_salary * 100.0));
    }

    println!("\nRust Query Builder:");
    println!("let avg = Query::new(&employees).avg(Employee::salary_r()).unwrap_or(0.0);");
    println!("Query::new(&employees)");
    println!("    .where_(Employee::salary_r(), |&sal| sal > avg)");
    println!("    .all()");
    println!("✓ Found {} employees above average", above_avg.len());

    // ============================================================================
    // EXAMPLE 15: Advanced aggregation (revenue calculation)
    // ============================================================================
    print_separator("Example 15: Advanced aggregation");
    print_query(
        "SELECT \n\
         d.name,\n\
         d.budget as dept_budget,\n\
         SUM(p.budget) as total_project_budget,\n\
         (d.budget - SUM(p.budget)) as remaining_budget\n\
         FROM departments d\n\
         LEFT JOIN projects p ON d.id = p.department_id\n\
         GROUP BY d.name, d.budget;",
        "Department budget utilization"
    );

    // Join departments with projects
    let dept_projects = JoinQuery::new(&departments, &projects).left_join(
        Department::id_r(),
        Project::department_id_r(),
        |dept, proj| (dept.clone(), proj.map(|p| p.clone())),
    );

    // Aggregate by department
    let mut dept_budget_map: HashMap<String, (f64, Vec<f64>)> = HashMap::new();
    for (dept, proj) in dept_projects {
        let entry = dept_budget_map
            .entry(dept.name.clone())
            .or_insert((dept.budget, Vec::new()));
        if let Some(p) = proj {
            entry.1.push(p.budget);
        }
    }

    for (dept_name, (total_budget, project_budgets)) in dept_budget_map.iter() {
        let used: f64 = project_budgets.iter().sum();
        let remaining = total_budget - used;
        println!("  {}: Budget ${:.0}, Used ${:.0}, Remaining ${:.0} ({:.1}% utilized)",
            dept_name, total_budget, used, remaining, (used / total_budget * 100.0));
    }

    println!("\nRust Query Builder:");
    println!("let dept_projects = JoinQuery::new(&departments, &projects)");
    println!("    .left_join(Department::id_r(), Project::department_id_r(), ...)");
    println!("// Then aggregate using HashMap");

    // ============================================================================
    // Summary
    // ============================================================================
    print_separator("Summary");
    println!("\n✓ All 15 SQL queries successfully replicated with Rust Query Builder!");
    println!("\nDemonstrated equivalents for:");
    println!("  • SELECT with WHERE");
    println!("  • Projection (SELECT specific columns)");
    println!("  • Aggregations (COUNT, SUM, AVG, MIN, MAX)");
    println!("  • GROUP BY");
    println!("  • ORDER BY");
    println!("  • LIMIT and OFFSET");
    println!("  • INNER JOIN and LEFT JOIN");
    println!("  • Complex conditions (AND, OR, BETWEEN)");
    println!("  • Subqueries");
    println!("  • Multi-table operations");
    println!("\n🎯 Type-safe, compile-time checked, and zero runtime overhead!");
    println!();
}

examples/memory_safety_verification.rs (lines 253-257)

fn main() {
    println!("\n╔═══════════════════════════════════════════════════════════════╗");
    println!("║  Memory Safety Verification                                   ║");
    println!("║  Proving 'static doesn't cause memory leaks                   ║");
    println!("╚═══════════════════════════════════════════════════════════════╝\n");

    println!("🔍 Understanding 'static:\n");
    println!("  • T: 'static means: Type T doesn't contain non-'static references");
    println!("  • It does NOT mean: Data lives for entire program");
    println!("  • It's needed for: Storing types in trait objects");
    println!("  • Safety: Compiler ensures no dangling references\n");

    // ============================================================================
    // TEST 1: Basic Query - Verify Cleanup
    // ============================================================================
    println!("═══════════════════════════════════════════════════════════════");
    println!("Test 1: Basic WHERE query - verify all data is dropped");
    println!("═══════════════════════════════════════════════════════════════\n");

    reset_stats();
    {
        let employees = vec![
            create_employee(1, "Alice", "Engineering", 95000.0),
            create_employee(2, "Bob", "Engineering", 87000.0),
            create_employee(3, "Carol", "Sales", 75000.0),
        ];
        print_memory_status("After creating employees");

        {
            let query = Query::new(&employees)
                .where_(Employee::department_r(), |dept| dept == "Engineering");
            let results = query.all();
            
            println!("  Found {} engineering employees", results.len());
            print_memory_status("During query execution");
        }
        
        print_memory_status("After query scope ends");
    }
    
    print_memory_status("After employees scope ends");
    println!();

    // ============================================================================
    // TEST 2: Multiple Queries - No Accumulation
    // ============================================================================
    println!("═══════════════════════════════════════════════════════════════");
    println!("Test 2: Multiple queries - verify no memory accumulation");
    println!("═══════════════════════════════════════════════════════════════\n");

    reset_stats();
    {
        let employees = vec![
            create_employee(1, "Alice", "Engineering", 95000.0),
            create_employee(2, "Bob", "Sales", 75000.0),
        ];

        let initial_stats = get_stats();
        println!("  Initial allocations: {}", initial_stats.0);

        // Run 10 queries
        for i in 1..=10 {
            let query = Query::new(&employees)
                .where_(Employee::salary_r(), |&s| s > 70000.0);
            let _results = query.all();
            
            if i % 3 == 0 {
                let (allocs, drops) = get_stats();
                println!("  After {} queries - Allocated: {}, Dropped: {}", i, allocs, drops);
            }
        }

        let final_stats = get_stats();
        println!("\n  After 10 queries:");
        println!("    Allocations: {} (should be same as initial)", final_stats.0);
        println!("    ✅ No memory accumulation from queries!");
    }

    print_memory_status("After all queries and employees dropped");
    println!();

    // ============================================================================
    // TEST 3: ORDER BY (requires Clone) - Track Cloning
    // ============================================================================
    println!("═══════════════════════════════════════════════════════════════");
    println!("Test 3: ORDER BY (with Clone) - verify controlled cloning");
    println!("═══════════════════════════════════════════════════════════════\n");

    reset_stats();
    {
        let employees = vec![
            create_employee(1, "Alice", "Engineering", 95000.0),
            create_employee(2, "Bob", "Sales", 87000.0),
            create_employee(3, "Carol", "Marketing", 75000.0),
        ];
        
        let (before_allocs, _) = get_stats();
        println!("  Before sorting: {} allocations", before_allocs);

        {
            let sorted = Query::new(&employees)
                .order_by_float_desc(Employee::salary_r());
            
            let (after_allocs, _) = get_stats();
            println!("  After sorting: {} allocations", after_allocs);
            println!("  Cloned items: {} (expected: {})", after_allocs - before_allocs, employees.len());
            println!("  Sorted {} employees by salary", sorted.len());
            
            // sorted goes out of scope here
        }
        
        print_memory_status("After sorted results dropped");
    }
    
    print_memory_status("After employees dropped");
    println!();

    // ============================================================================
    // TEST 4: JOIN Operations - No Leaks
    // ============================================================================
    println!("═══════════════════════════════════════════════════════════════");
    println!("Test 4: JOIN operations - verify no memory leaks");
    println!("═══════════════════════════════════════════════════════════════\n");

    #[derive(Keypaths)]
    struct Department {
        id: u32,
        name: String,
        drop_tracker: DropTracker,
    }

    reset_stats();
    {
        let employees = vec![
            create_employee(1, "Alice", "Engineering", 95000.0),
            create_employee(2, "Bob", "Sales", 87000.0),
        ];

        let departments = vec![
            Department {
                id: 1,
                name: "Engineering".to_string(),
                drop_tracker: DropTracker::new(),
            },
            Department {
                id: 2,
                name: "Sales".to_string(),
                drop_tracker: DropTracker::new(),
            },
        ];

        print_memory_status("After creating data");

        {
            let results = JoinQuery::new(&employees, &departments)
                .inner_join(
                    Employee::department_r(),
                    Department::name_r(),
                    |emp, dept| (emp.name.clone(), dept.name.clone()),
                );
            
            println!("  Joined {} pairs", results.len());
            print_memory_status("During join results");
        }

        print_memory_status("After join results dropped");
    }

    print_memory_status("After all data dropped");
    println!();

    // ============================================================================
    // TEST 5: Large Scale - Memory Behavior
    // ============================================================================
    println!("═══════════════════════════════════════════════════════════════");
    println!("Test 5: Large scale (1000 items) - verify cleanup");
    println!("═══════════════════════════════════════════════════════════════\n");

    reset_stats();
    {
        let mut large_dataset = Vec::new();
        for i in 0..1000 {
            large_dataset.push(create_employee(
                i,
                &format!("Employee {}", i),
                if i % 3 == 0 { "Engineering" } else if i % 3 == 1 { "Sales" } else { "Marketing" },
                50000.0 + (i as f64 * 100.0),
            ));
        }

        let (initial_allocs, _) = get_stats();
        println!("  Created 1000 employees: {} allocations (~10MB)", initial_allocs);

        // Run complex query
        {
            let query = Query::new(&large_dataset)
                .where_(Employee::salary_r(), |&s| s > 80000.0)
                .where_(Employee::department_r(), |d| d == "Engineering");
            let results = query.all();
            
            println!("  Filtered to {} employees", results.len());
            
            let (during_allocs, _) = get_stats();
            let extra = during_allocs - initial_allocs;
            println!("  Extra allocations during query: {} (should be 0)", extra);
            
            if extra == 0 {
                println!("  ✅ Zero-copy filtering confirmed!");
            }
        }

        print_memory_status("After query results dropped");
    }

    print_memory_status("After 1000 employees dropped");
    println!();

    // ============================================================================
    // TEST 6: Explanation of 'static
    // ============================================================================
    println!("═══════════════════════════════════════════════════════════════");
    println!("Explanation: Why 'static is safe and doesn't leak");
    println!("═══════════════════════════════════════════════════════════════\n");

    println!("❓ What does T: 'static mean?\n");
    println!("  WRONG ❌: \"T lives for the entire program\"");
    println!("  RIGHT ✅: \"T doesn't contain non-'static references\"\n");

    println!("Examples:\n");
    println!("  struct OwnedData {{          // T: 'static ✅");
    println!("      id: u32,                 // Owned data");
    println!("      name: String,            // Owned data");
    println!("  }}");
    println!();
    println!("  struct WithReference<'a> {{  // NOT 'static ❌");
    println!("      data: &'a String,        // Contains reference");
    println!("  }}");
    println!();

    println!("Why we use T: 'static:\n");
    println!("  1. Store type in trait objects: Box<dyn Fn(&T) -> bool>");
    println!("  2. Prevent dangling references in closures");
    println!("  3. Ensure type safety at compile time");
    println!();

    println!("Lifetime of data:\n");
    println!("  • Data is owned by your Vec<T>");
    println!("  • Query just borrows &'a [T]");
    println!("  • When Vec<T> is dropped, all T are dropped");
    println!("  • No memory leaks possible!\n");

    // ============================================================================
    // TEST 7: Drop Order Verification
    // ============================================================================
    println!("═══════════════════════════════════════════════════════════════");
    println!("Test 7: Drop order - verify proper RAII");
    println!("═══════════════════════════════════════════════════════════════\n");

    reset_stats();
    
    println!("Creating scoped data...");
    {
        let employees = vec![
            create_employee(1, "Alice", "Engineering", 95000.0),
            create_employee(2, "Bob", "Sales", 87000.0),
        ];
        println!("  Created 2 employees");

        {
            println!("  Creating query...");
            let query = Query::new(&employees)
                .where_(Employee::department_r(), |dept| dept == "Engineering");
            
            {
                println!("  Executing query...");
                let results = query.all();
                println!("    Found {} results", results.len());
                println!("  Query results going out of scope...");
            }
            println!("  Results dropped (just Vec<&Employee>, no Employee drops)");
            
            println!("  Query going out of scope...");
        }
        println!("  Query dropped (just filters, no Employee drops)");
        
        println!("  Employees vector going out of scope...");
    }
    println!("  Employees dropped - NOW Employees are freed!\n");
    
    let (allocs, drops) = get_stats();
    println!("Final stats:");
    println!("  Allocated: {}", allocs);
    println!("  Dropped: {}", drops);
    println!("  Leaked: {}", allocs - drops);
    
    if allocs == drops {
        println!("\n✅ Perfect! All allocated memory was freed!");
    } else {
        println!("\n❌ Memory leak detected!");
    }

    // ============================================================================
    // TEST 8: Arc/Rc Compatibility
    // ============================================================================
    println!("\n═══════════════════════════════════════════════════════════════");
    println!("Test 8: Arc/Rc compatibility - shared ownership works");
    println!("═══════════════════════════════════════════════════════════════\n");

    {
        use std::sync::Arc;
        
        #[derive(Keypaths)]
        struct SharedData {
            id: u32,
            value: Arc<String>,  // Shared ownership
        }

        let shared_string = Arc::new("Shared Value".to_string());
        println!("  Arc strong count: {}", Arc::strong_count(&shared_string));

        let data = vec![
            SharedData { id: 1, value: Arc::clone(&shared_string) },
            SharedData { id: 2, value: Arc::clone(&shared_string) },
        ];
        
        println!("  Arc strong count after creating data: {}", Arc::strong_count(&shared_string));

        {
            let query = Query::new(&data)
                .where_(SharedData::id_r(), |&id| id > 0);
            let results = query.all();
            println!("  Found {} items", results.len());
            println!("  Arc strong count during query: {}", Arc::strong_count(&shared_string));
        }

        println!("  Arc strong count after query: {}", Arc::strong_count(&shared_string));
    }
    
    println!("  ✅ Arc reference counting works correctly!\n");

    // ============================================================================
    // TEST 9: Large Data Without Clone - Zero Copy
    // ============================================================================
    println!("═══════════════════════════════════════════════════════════════");
    println!("Test 9: Large data without Clone - verify zero-copy");
    println!("═══════════════════════════════════════════════════════════════\n");

    #[derive(Keypaths)]  // NO Clone!
    struct LargeRecord {
        id: u32,
        // Simulate 1MB of data that we DON'T want to clone
        huge_data: Vec<u8>,
    }

    {
        println!("  Creating 10 records (1MB each = 10MB total)...");
        let large_records: Vec<LargeRecord> = (0..10)
            .map(|i| LargeRecord {
                id: i,
                huge_data: vec![i as u8; 1_000_000], // 1MB each
            })
            .collect();

        println!("  Total memory: ~10MB");

        {
            println!("\n  Running query without Clone...");
            let query = Query::new(&large_records)
                .where_(LargeRecord::id_r(), |&id| id < 5);
            let results = query.all();  // Vec<&LargeRecord> - NO CLONING!
            
            println!("  Found {} records", results.len());
            println!("  Memory copied: 0 bytes (just references)");
            println!("  ✅ Zero-copy achieved!");
        }

        println!("\n  Query dropped - no memory freed (no cloning happened)");
    }
    
    println!("  Records dropped - 10MB freed\n");

    // ============================================================================
    // TEST 10: Lifetime Safety
    // ============================================================================
    println!("═══════════════════════════════════════════════════════════════");
    println!("Test 10: Lifetime safety - compiler prevents dangling refs");
    println!("═══════════════════════════════════════════════════════════════\n");

    println!("  The following code WILL NOT COMPILE (by design):\n");
    println!("  ```rust");
    println!("  let query;");
    println!("  {{");
    println!("      let data = vec![...];");
    println!("      query = Query::new(&data);  // data borrowed here");
    println!("  }}  // data dropped");
    println!("  let results = query.all();  // ❌ ERROR: data doesn't live long enough");
    println!("  ```\n");
    println!("  ✅ Rust's borrow checker prevents use-after-free!");
    println!("  ✅ 'static bound + lifetimes = memory safety guaranteed!\n");

    // ============================================================================
    // Summary
    // ============================================================================
    println!("═══════════════════════════════════════════════════════════════");
    println!("Summary: Memory Safety Guarantees");
    println!("═══════════════════════════════════════════════════════════════\n");

    let (total_allocs, total_drops) = get_stats();
    let leaked = total_allocs.saturating_sub(total_drops);

    println!("Overall Statistics:");
    println!("  Total allocations: {}", total_allocs);
    println!("  Total drops: {}", total_drops);
    println!("  Memory leaks: {}", leaked);

    if leaked == 0 {
        println!("\n🎉 VERIFIED: Zero memory leaks!\n");
    } else {
        println!("\n⚠️  WARNING: Potential memory leak detected!\n");
    }

    println!("Guarantees Verified:");
    println!("  ✅ 'static doesn't cause data to live forever");
    println!("  ✅ All allocated memory is properly freed");
    println!("  ✅ No memory leaks from queries");
    println!("  ✅ Query only holds references, not ownership");
    println!("  ✅ Rust's borrow checker prevents dangling references");
    println!("  ✅ RAII ensures proper cleanup");
    println!("  ✅ Zero-copy operations don't allocate");
    println!("  ✅ Clone operations are explicit and controlled\n");

    println!("Performance Benefits:");
    println!("  ✅ Filtering: 0 bytes copied (v0.2.0) vs 10MB (v0.1.0)");
    println!("  ✅ Counting: 0 bytes copied");
    println!("  ✅ Aggregations: 0 bytes copied");
    println!("  ✅ Only ordering/grouping clone when needed\n");

    println!("Safety Guarantees:");
    println!("  ✅ Compile-time prevention of dangling references");
    println!("  ✅ No use-after-free possible");
    println!("  ✅ No double-free possible");
    println!("  ✅ Automatic cleanup via RAII\n");

    println!("✓ All memory safety tests PASSED!\n");
}

pub fn left_join<K, O, F>( &self, left_key: KeyPaths<L, K>, right_key: KeyPaths<R, K>, mapper: F, ) -> Vec<O>

where K: Eq + Hash + Clone + 'static, F: Fn(&L, Option<&R>) -> O,

Performs a left join between two collections.

Returns all items from the left collection with optional matching items from the right collection. If no match is found, the right item is None.

Arguments

• left_key - Key-path to the join field in the left collection
• right_key - Key-path to the join field in the right collection
• mapper - Function to transform pairs into the result type (right item may be None)

Example

let results = JoinQuery::new(&users, &orders)
    .left_join(
        User::id_r(),
        Order::user_id_r(),
        |user, order| match order {
            Some(o) => format!("{} has order {}", user.name, o.id),
            None => format!("{} has no orders", user.name),
        }
    );

Examples found in repository

examples/join_query_builder.rs (lines 263-267)

fn main() {
    println!("=== Join Query Builder Demo ===\n");

    let (users, orders, products) = create_sample_data();

    println!("Database:");
    println!("  Users: {}", users.len());
    println!("  Orders: {}", orders.len());
    println!("  Products: {}\n", products.len());

    // Join 1: Inner join Users and Orders
    println!("--- Join 1: Users with Their Orders ---");
    let user_orders = JoinQuery::new(&users, &orders).inner_join(
        User::id_r(),
        Order::user_id_r(),
        |user, order| UserOrder {
            user_name: user.name.clone(),
            user_email: user.email.clone(),
            order_id: order.id,
            quantity: order.quantity,
            total: order.total,
        },
    );

    for uo in &user_orders {
        println!(
            "  • {} - Order #{} - {} items - ${:.2}",
            uo.user_name, uo.order_id, uo.quantity, uo.total
        );
    }
    println!("Total: {} user-order pairs", user_orders.len());

    // Join 2: Three-way join (Orders -> Users, Orders -> Products)
    println!("\n--- Join 2: Complete Order Details (3-Way Join) ---");
    
    // First join: Orders with Users
    let orders_with_users = JoinQuery::new(&orders, &users).inner_join(
        Order::user_id_r(),
        User::id_r(),
        |order, user| (order.clone(), user.clone()),
    );

    // Second join: (Orders+Users) with Products
    let mut order_details = Vec::new();
    for (order, user) in &orders_with_users {
        for product in &products {
            if order.product_id == product.id {
                order_details.push(OrderDetail {
                    order_id: order.id,
                    user_name: user.name.clone(),
                    product_name: product.name.clone(),
                    quantity: order.quantity,
                    price: product.price,
                    total: order.total,
                });
            }
        }
    }

    for od in &order_details {
        println!(
            "  • Order #{}: {} bought {} x {} @ ${:.2} = ${:.2}",
            od.order_id, od.user_name, od.quantity, od.product_name, od.price, od.total
        );
    }

    // Join 3: Left join to show all users (including those without orders)
    println!("\n--- Join 3: All Users with Order Count (Left Join) ---");
    
    // Use left_join to get all users with their orders (or None)
    let user_order_pairs = JoinQuery::new(&users, &orders).left_join(
        User::id_r(),
        Order::user_id_r(),
        |user, order| (user.clone(), order.map(|o| o.clone())),
    );

    // Group by user to count orders
    let mut user_stats: HashMap<u32, (String, String, usize, f64)> = HashMap::new();
    for (user, order) in &user_order_pairs {
        let entry = user_stats
            .entry(user.id)
            .or_insert_with(|| (user.name.clone(), user.city.clone(), 0, 0.0));
        if let Some(order) = order {
            entry.2 += 1; // order count
            entry.3 += order.total; // total spent
        }
    }

    let mut user_order_stats: Vec<_> = user_stats
        .into_iter()
        .map(|(_, (name, city, count, total))| UserOrderCount {
            user_name: name,
            user_city: city,
            order_count: count,
            total_spent: total,
        })
        .collect();

    user_order_stats.sort_by(|a, b| a.user_name.cmp(&b.user_name));

    for stat in &user_order_stats {
        if stat.order_count > 0 {
            println!(
                "  • {} ({}) - {} orders - ${:.2} total",
                stat.user_name, stat.user_city, stat.order_count, stat.total_spent
            );
        } else {
            println!("  • {} ({}) - No orders yet", stat.user_name, stat.user_city);
        }
    }

    // Join 4: Aggregated join - Category sales analysis
    println!("\n--- Join 4: Sales by Product Category ---");

    // Join orders with products to get category information
    let order_products = JoinQuery::new(&orders, &products).inner_join(
        Order::product_id_r(),
        Product::id_r(),
        |order, product| (order.clone(), product.clone()),
    );

    // Aggregate by category
    let mut category_stats: HashMap<String, (Vec<u32>, f64, std::collections::HashSet<u32>)> =
        HashMap::new();

    for (order, product) in &order_products {
        let entry = category_stats
            .entry(product.category.clone())
            .or_insert_with(|| (Vec::new(), 0.0, std::collections::HashSet::new()));
        entry.0.push(order.id);
        entry.1 += order.total;
        entry.2.insert(order.user_id);
    }

    let mut category_sales: Vec<CategorySales> = category_stats
        .into_iter()
        .map(|(category, (orders, revenue, customers))| CategorySales {
            category,
            total_orders: orders.len(),
            total_revenue: revenue,
            unique_customers: customers.len(),
        })
        .collect();

    category_sales.sort_by(|a, b| {
        b.total_revenue
            .partial_cmp(&a.total_revenue)
            .unwrap_or(std::cmp::Ordering::Equal)
    });

    for cs in &category_sales {
        println!(
            "  • {}: {} orders - ${:.2} revenue - {} customers",
            cs.category, cs.total_orders, cs.total_revenue, cs.unique_customers
        );
    }

    // Join 5: Filtered join - High value orders
    println!("\n--- Join 5: High Value Orders (>$100) with User Details ---");
    let high_value_orders = JoinQuery::new(&orders, &users).inner_join_where(
        Order::user_id_r(),
        User::id_r(),
        |order, _user| order.total > 100.0,
        |order, user| (user.name.clone(), order.id, order.total),
    );

    for (name, order_id, total) in &high_value_orders {
        println!("  • {} - Order #{} - ${:.2}", name, order_id, total);
    }

    // Join 6: Users in same city analysis
    println!("\n--- Join 6: Users from Same City ---");
    let user_pairs = JoinQuery::new(&users, &users).inner_join_where(
        User::city_r(),
        User::city_r(),
        |u1, u2| u1.id < u2.id, // Avoid duplicates and self-pairs
        |u1, u2| (u1.name.clone(), u2.name.clone(), u1.city.clone()),
    );

    for (name1, name2, city) in &user_pairs {
        println!("  • {} and {} both live in {}", name1, name2, city);
    }

    // Join 7: Product popularity
    println!("\n--- Join 7: Product Popularity Ranking ---");
    
    // Join orders with products
    let product_order_pairs = JoinQuery::new(&products, &orders).inner_join(
        Product::id_r(),
        Order::product_id_r(),
        |product, order| (product.clone(), order.clone()),
    );

    // Aggregate by product
    let mut product_sales: HashMap<u32, (String, usize, u32, f64)> = HashMap::new();
    for (product, order) in &product_order_pairs {
        let entry = product_sales
            .entry(product.id)
            .or_insert_with(|| (product.name.clone(), 0, 0, 0.0));
        entry.1 += 1; // order count
        entry.2 += order.quantity; // total quantity
        entry.3 += order.total; // total revenue
    }

    let mut popularity: Vec<_> = product_sales.into_iter().collect();
    popularity.sort_by(|a, b| b.1 .1.cmp(&a.1 .1)); // sort by order count

    for (_, (name, order_count, total_qty, revenue)) in &popularity {
        println!(
            "  • {} - {} orders - {} units - ${:.2}",
            name, order_count, total_qty, revenue
        );
    }

    // Join 8: User spending by city
    println!("\n--- Join 8: Total Spending by City ---");
    
    // Join users with orders to get city and spending info
    let user_city_orders = JoinQuery::new(&users, &orders).inner_join(
        User::id_r(),
        Order::user_id_r(),
        |user, order| (user.city.clone(), order.total, user.id),
    );

    // Aggregate by city
    let mut city_spending: HashMap<String, (f64, std::collections::HashSet<u32>)> = HashMap::new();
    for (city, total, user_id) in &user_city_orders {
        let entry = city_spending
            .entry(city.clone())
            .or_insert_with(|| (0.0, std::collections::HashSet::new()));
        entry.0 += total;
        entry.1.insert(*user_id);
    }

    let mut city_stats: Vec<_> = city_spending
        .into_iter()
        .map(|(city, (total, customers))| (city, total, customers.len()))
        .collect();
    
    city_stats.sort_by(|a, b| {
        b.1.partial_cmp(&a.1).unwrap_or(std::cmp::Ordering::Equal)
    });

    for (city, total, customer_count) in &city_stats {
        println!(
            "  • {} - ${:.2} total - {} customers - ${:.2} avg",
            city,
            total,
            customer_count,
            total / *customer_count as f64
        );
    }

    // Statistics summary
    println!("\n=== Summary Statistics ===");
    println!("Total orders: {}", orders.len());
    
    let total_revenue: f64 = orders.iter().map(|o| o.total).sum();
    println!("Total revenue: ${:.2}", total_revenue);
    println!("Average order value: ${:.2}", total_revenue / orders.len() as f64);
    
    // Count unique customers using a join
    let unique_customers: std::collections::HashSet<u32> = 
        orders.iter().map(|o| o.user_id).collect();
    println!("Active customers: {}", unique_customers.len());
    println!(
        "Average orders per customer: {:.1}",
        orders.len() as f64 / unique_customers.len() as f64
    );

    println!("\n✓ Join query builder demo complete!");
}

examples/sql_comparison.rs (lines 499-503)

fn main() {
    let employees = create_employees();
    let departments = create_departments();
    let projects = create_projects();

    println!("\n╔════════════════════════════════════════════════════════════════════════╗");
    println!("║     SQL vs Rust Query Builder Comparison                              ║");
    println!("║     Demonstrating equivalent operations                               ║");
    println!("╚════════════════════════════════════════════════════════════════════════╝");

    // ============================================================================
    // EXAMPLE 1: Simple SELECT with WHERE
    // ============================================================================
    print_separator("Example 1: SELECT with WHERE clause");
    print_query(
        "SELECT * FROM employees WHERE department = 'Engineering';",
        "Filter employees by department"
    );

    let eng_query = Query::new(&employees)
        .where_(Employee::department_r(), |dept| dept == "Engineering");
    let engineering_employees = eng_query.all();

    for emp in &engineering_employees {
        println!("  ID: {}, Name: {}, Salary: ${:.2}", emp.id, emp.name, emp.salary);
    }
    println!("\nRust Query Builder:");
    println!("Query::new(&employees)");
    println!("    .where_(Employee::department_r(), |dept| dept == \"Engineering\")");
    println!("    .all()");
    println!("✓ Found {} employees", engineering_employees.len());

    // ============================================================================
    // EXAMPLE 2: SELECT specific columns
    // ============================================================================
    print_separator("Example 2: SELECT specific columns (Projection)");
    print_query(
        "SELECT name FROM employees WHERE salary > 80000;",
        "Get names of high-earning employees"
    );

    let high_earners = Query::new(&employees)
        .where_(Employee::salary_r(), |&salary| salary > 80000.0)
        .select(Employee::name_r());

    for name in &high_earners {
        println!("  {}", name);
    }
    println!("\nRust Query Builder:");
    println!("Query::new(&employees)");
    println!("    .where_(Employee::salary_r(), |&salary| salary > 80000.0)");
    println!("    .select(Employee::name_r())");
    println!("✓ Found {} employees", high_earners.len());

    // ============================================================================
    // EXAMPLE 3: COUNT
    // ============================================================================
    print_separator("Example 3: COUNT aggregation");
    print_query(
        "SELECT COUNT(*) FROM employees WHERE age < 30;",
        "Count young employees"
    );

    let young_count = Query::new(&employees)
        .where_(Employee::age_r(), |&age| age < 30)
        .count();

    println!("  Count: {}", young_count);
    println!("\nRust Query Builder:");
    println!("Query::new(&employees)");
    println!("    .where_(Employee::age_r(), |&age| age < 30)");
    println!("    .count()");
    println!("✓ Result: {}", young_count);

    // ============================================================================
    // EXAMPLE 4: SUM, AVG, MIN, MAX
    // ============================================================================
    print_separator("Example 4: Aggregate functions (SUM, AVG, MIN, MAX)");
    print_query(
        "SELECT \n\
         SUM(salary) as total_salary,\n\
         AVG(salary) as avg_salary,\n\
         MIN(salary) as min_salary,\n\
         MAX(salary) as max_salary\n\
         FROM employees WHERE department = 'Engineering';",
        "Engineering department salary statistics"
    );

    let eng_query = Query::new(&employees)
        .where_(Employee::department_r(), |dept| dept == "Engineering");

    let total = eng_query.sum(Employee::salary_r());
    let avg = eng_query.avg(Employee::salary_r()).unwrap_or(0.0);
    let min = eng_query.min_float(Employee::salary_r()).unwrap_or(0.0);
    let max = eng_query.max_float(Employee::salary_r()).unwrap_or(0.0);

    println!("  Total Salary: ${:.2}", total);
    println!("  Avg Salary:   ${:.2}", avg);
    println!("  Min Salary:   ${:.2}", min);
    println!("  Max Salary:   ${:.2}", max);

    println!("\nRust Query Builder:");
    println!("let query = Query::new(&employees)");
    println!("    .where_(Employee::department_r(), |dept| dept == \"Engineering\");");
    println!("query.sum(Employee::salary_r())  // ${:.2}", total);
    println!("query.avg(Employee::salary_r())  // ${:.2}", avg);
    println!("query.min_float(Employee::salary_r())  // ${:.2}", min);
    println!("query.max_float(Employee::salary_r())  // ${:.2}", max);

    // ============================================================================
    // EXAMPLE 5: GROUP BY with aggregation
    // ============================================================================
    print_separator("Example 5: GROUP BY with aggregation");
    print_query(
        "SELECT \n\
         department,\n\
         COUNT(*) as emp_count,\n\
         AVG(salary) as avg_salary\n\
         FROM employees\n\
         GROUP BY department;",
        "Statistics by department"
    );

    let by_dept = Query::new(&employees).group_by(Employee::department_r());

    for (dept, emps) in &by_dept {
        let dept_query = Query::new(emps);
        let count = emps.len();
        let avg_sal = dept_query.avg(Employee::salary_r()).unwrap_or(0.0);
        println!("  {}: {} employees, avg salary ${:.2}", dept, count, avg_sal);
    }

    println!("\nRust Query Builder:");
    println!("let by_dept = Query::new(&employees).group_by(Employee::department_r());");
    println!("for (dept, emps) in &by_dept {{");
    println!("    let dept_query = Query::new(emps);");
    println!("    dept_query.avg(Employee::salary_r())");
    println!("}}");

    // ============================================================================
    // EXAMPLE 6: ORDER BY
    // ============================================================================
    print_separator("Example 6: ORDER BY");
    print_query(
        "SELECT name, salary FROM employees\n\
         WHERE department = 'Sales'\n\
         ORDER BY salary DESC;",
        "Sales employees ordered by salary (descending)"
    );

    let sales_sorted = Query::new(&employees)
        .where_(Employee::department_r(), |dept| dept == "Sales")
        .order_by_float_desc(Employee::salary_r());

    for emp in &sales_sorted {
        println!("  {}: ${:.2}", emp.name, emp.salary);
    }

    println!("\nRust Query Builder:");
    println!("Query::new(&employees)");
    println!("    .where_(Employee::department_r(), |dept| dept == \"Sales\")");
    println!("    .order_by_float_desc(Employee::salary_r())");

    // ============================================================================
    // EXAMPLE 7: Multiple WHERE conditions (AND)
    // ============================================================================
    print_separator("Example 7: Multiple WHERE conditions (AND)");
    print_query(
        "SELECT * FROM employees\n\
         WHERE salary > 70000 AND age < 35;",
        "High-earning young employees"
    );

    let filter_query = Query::new(&employees)
        .where_(Employee::salary_r(), |&salary| salary > 70000.0)
        .where_(Employee::age_r(), |&age| age < 35);
    let filtered = filter_query.all();

    for emp in &filtered {
        println!("  {}: Age {}, Salary ${:.2}", emp.name, emp.age, emp.salary);
    }

    println!("\nRust Query Builder:");
    println!("Query::new(&employees)");
    println!("    .where_(Employee::salary_r(), |&salary| salary > 70000.0)");
    println!("    .where_(Employee::age_r(), |&age| age < 35)");
    println!("    .all()");
    println!("✓ Found {} employees", filtered.len());

    // ============================================================================
    // EXAMPLE 8: LIMIT (TOP N)
    // ============================================================================
    print_separator("Example 8: LIMIT / TOP N");
    print_query(
        "SELECT TOP 3 name, salary FROM employees\n\
         ORDER BY salary DESC;",
        "Top 3 highest paid employees"
    );

    let top_earners = Query::new(&employees)
        .order_by_float_desc(Employee::salary_r());

    for (i, emp) in top_earners.iter().take(3).enumerate() {
        println!("  {}. {}: ${:.2}", i + 1, emp.name, emp.salary);
    }

    println!("\nRust Query Builder:");
    println!("Query::new(&employees)");
    println!("    .order_by_float_desc(Employee::salary_r())");
    println!("    .into_iter().take(3)");

    // ============================================================================
    // EXAMPLE 9: OFFSET and LIMIT (Pagination)
    // ============================================================================
    print_separator("Example 9: OFFSET and LIMIT (Pagination)");
    print_query(
        "SELECT name FROM employees\n\
         ORDER BY name\n\
         LIMIT 3 OFFSET 3;",
        "Page 2 of employee names (3 per page)"
    );

    let page_2 = Query::new(&employees)
        .order_by(Employee::name_r())
        .into_iter()
        .skip(3)
        .take(3)
        .collect::<Vec<_>>();

    for emp in &page_2 {
        println!("  {}", emp.name);
    }

    println!("\nRust Query Builder:");
    println!("Query::new(&employees)");
    println!("    .order_by(Employee::name_r())");
    println!("    .into_iter().skip(3).take(3)");

    // ============================================================================
    // EXAMPLE 10: INNER JOIN
    // ============================================================================
    print_separator("Example 10: INNER JOIN");
    print_query(
        "SELECT e.name, d.name as dept_name, d.budget\n\
         FROM employees e\n\
         INNER JOIN departments d ON e.department = d.name;",
        "Employees with their department info"
    );

    let emp_dept = JoinQuery::new(&employees, &departments).inner_join(
        Employee::department_r(),
        Department::name_r(),
        |emp, dept| (emp.name.clone(), dept.name.clone(), dept.budget),
    );

    for (emp_name, dept_name, budget) in emp_dept.iter().take(5) {
        println!("  {} works in {} (Budget: ${:.0})", emp_name, dept_name, budget);
    }
    println!("  ... (showing first 5)");

    println!("\nRust Query Builder:");
    println!("JoinQuery::new(&employees, &departments).inner_join(");
    println!("    Employee::department_r(),");
    println!("    Department::name_r(),");
    println!("    |emp, dept| (emp.name.clone(), dept.name.clone(), dept.budget)");
    println!(")");
    println!("✓ Found {} matches", emp_dept.len());

    // ============================================================================
    // EXAMPLE 11: GROUP BY with HAVING equivalent
    // ============================================================================
    print_separator("Example 11: GROUP BY with filtering (HAVING equivalent)");
    print_query(
        "SELECT city, COUNT(*) as emp_count, AVG(salary) as avg_salary\n\
         FROM employees\n\
         GROUP BY city\n\
         HAVING COUNT(*) > 1;",
        "Cities with multiple employees"
    );

    let by_city = Query::new(&employees).group_by(Employee::city_r());
    let mut city_stats: Vec<_> = by_city
        .iter()
        .filter(|(_, emps)| emps.len() > 1) // HAVING equivalent
        .map(|(city, emps)| {
            let avg_sal = Query::new(emps).avg(Employee::salary_r()).unwrap_or(0.0);
            (city.clone(), emps.len(), avg_sal)
        })
        .collect();
    
    city_stats.sort_by(|a, b| b.1.cmp(&a.1)); // Sort by count

    for (city, count, avg_sal) in &city_stats {
        println!("  {}: {} employees, avg salary ${:.2}", city, count, avg_sal);
    }

    println!("\nRust Query Builder:");
    println!("let by_city = Query::new(&employees).group_by(Employee::city_r());");
    println!("by_city.iter()");
    println!("    .filter(|(_, emps)| emps.len() > 1)  // HAVING equivalent");
    println!("    .map(|(city, emps)| {{");
    println!("        let avg = Query::new(emps).avg(Employee::salary_r());");
    println!("        (city, emps.len(), avg)");
    println!("    }})");

    // ============================================================================
    // EXAMPLE 12: Complex query with multiple operations
    // ============================================================================
    print_separator("Example 12: Complex multi-operation query");
    print_query(
        "SELECT name, salary, age\n\
         FROM employees\n\
         WHERE department IN ('Engineering', 'Sales')\n\
         AND salary BETWEEN 80000 AND 100000\n\
         AND age >= 30\n\
         ORDER BY salary DESC;",
        "Experienced mid-to-senior level employees in core departments"
    );

    let complex_query = Query::new(&employees)
        .where_(Employee::department_r(), |dept| dept == "Engineering" || dept == "Sales")
        .where_(Employee::salary_r(), |&sal| sal >= 80000.0 && sal <= 100000.0)
        .where_(Employee::age_r(), |&age| age >= 30)
        .order_by_float_desc(Employee::salary_r());

    for emp in &complex_query {
        println!("  {}: Age {}, {} dept, ${:.2}", emp.name, emp.age, emp.department, emp.salary);
    }

    println!("\nRust Query Builder:");
    println!("Query::new(&employees)");
    println!("    .where_(Employee::department_r(), |dept| dept == \"Engineering\" || dept == \"Sales\")");
    println!("    .where_(Employee::salary_r(), |&sal| sal >= 80000.0 && sal <= 100000.0)");
    println!("    .where_(Employee::age_r(), |&age| age >= 30)");
    println!("    .order_by_float_desc(Employee::salary_r())");
    println!("✓ Found {} employees", complex_query.len());

    // ============================================================================
    // EXAMPLE 13: Three-table JOIN
    // ============================================================================
    print_separator("Example 13: Three-table JOIN");
    print_query(
        "SELECT p.name as project, d.name as department, d.location\n\
         FROM projects p\n\
         INNER JOIN departments d ON p.department_id = d.id;",
        "Projects with their department details"
    );

    let proj_dept = JoinQuery::new(&projects, &departments).inner_join(
        Project::department_id_r(),
        Department::id_r(),
        |proj, dept| {
            (proj.name.clone(), dept.name.clone(), dept.location.clone(), proj.budget)
        },
    );

    for (proj_name, dept_name, location, budget) in &proj_dept {
        println!("  {}: {} dept in {} (${:.0})", proj_name, dept_name, location, budget);
    }

    println!("\nRust Query Builder:");
    println!("JoinQuery::new(&projects, &departments).inner_join(");
    println!("    Project::department_id_r(),");
    println!("    Department::id_r(),");
    println!("    |proj, dept| (proj.name, dept.name, dept.location, proj.budget)");
    println!(")");

    // ============================================================================
    // EXAMPLE 14: Subquery equivalent (using intermediate results)
    // ============================================================================
    print_separator("Example 14: Subquery equivalent");
    print_query(
        "SELECT * FROM employees\n\
         WHERE salary > (SELECT AVG(salary) FROM employees);",
        "Employees earning above average"
    );

    let avg_salary = Query::new(&employees)
        .avg(Employee::salary_r())
        .unwrap_or(0.0);

    let above_avg_query = Query::new(&employees)
        .where_(Employee::salary_r(), move |&sal| sal > avg_salary);
    let above_avg = above_avg_query.all();

    println!("  Average salary: ${:.2}", avg_salary);
    for emp in &above_avg {
        println!("  {}: ${:.2} ({:.1}% above average)", 
            emp.name, emp.salary, ((emp.salary - avg_salary) / avg_salary * 100.0));
    }

    println!("\nRust Query Builder:");
    println!("let avg = Query::new(&employees).avg(Employee::salary_r()).unwrap_or(0.0);");
    println!("Query::new(&employees)");
    println!("    .where_(Employee::salary_r(), |&sal| sal > avg)");
    println!("    .all()");
    println!("✓ Found {} employees above average", above_avg.len());

    // ============================================================================
    // EXAMPLE 15: Advanced aggregation (revenue calculation)
    // ============================================================================
    print_separator("Example 15: Advanced aggregation");
    print_query(
        "SELECT \n\
         d.name,\n\
         d.budget as dept_budget,\n\
         SUM(p.budget) as total_project_budget,\n\
         (d.budget - SUM(p.budget)) as remaining_budget\n\
         FROM departments d\n\
         LEFT JOIN projects p ON d.id = p.department_id\n\
         GROUP BY d.name, d.budget;",
        "Department budget utilization"
    );

    // Join departments with projects
    let dept_projects = JoinQuery::new(&departments, &projects).left_join(
        Department::id_r(),
        Project::department_id_r(),
        |dept, proj| (dept.clone(), proj.map(|p| p.clone())),
    );

    // Aggregate by department
    let mut dept_budget_map: HashMap<String, (f64, Vec<f64>)> = HashMap::new();
    for (dept, proj) in dept_projects {
        let entry = dept_budget_map
            .entry(dept.name.clone())
            .or_insert((dept.budget, Vec::new()));
        if let Some(p) = proj {
            entry.1.push(p.budget);
        }
    }

    for (dept_name, (total_budget, project_budgets)) in dept_budget_map.iter() {
        let used: f64 = project_budgets.iter().sum();
        let remaining = total_budget - used;
        println!("  {}: Budget ${:.0}, Used ${:.0}, Remaining ${:.0} ({:.1}% utilized)",
            dept_name, total_budget, used, remaining, (used / total_budget * 100.0));
    }

    println!("\nRust Query Builder:");
    println!("let dept_projects = JoinQuery::new(&departments, &projects)");
    println!("    .left_join(Department::id_r(), Project::department_id_r(), ...)");
    println!("// Then aggregate using HashMap");

    // ============================================================================
    // Summary
    // ============================================================================
    print_separator("Summary");
    println!("\n✓ All 15 SQL queries successfully replicated with Rust Query Builder!");
    println!("\nDemonstrated equivalents for:");
    println!("  • SELECT with WHERE");
    println!("  • Projection (SELECT specific columns)");
    println!("  • Aggregations (COUNT, SUM, AVG, MIN, MAX)");
    println!("  • GROUP BY");
    println!("  • ORDER BY");
    println!("  • LIMIT and OFFSET");
    println!("  • INNER JOIN and LEFT JOIN");
    println!("  • Complex conditions (AND, OR, BETWEEN)");
    println!("  • Subqueries");
    println!("  • Multi-table operations");
    println!("\n🎯 Type-safe, compile-time checked, and zero runtime overhead!");
    println!();
}

pub fn inner_join_where<K, O, F, P>( &self, left_key: KeyPaths<L, K>, right_key: KeyPaths<R, K>, predicate: P, mapper: F, ) -> Vec<O>

where K: Eq + Hash + Clone + 'static, F: Fn(&L, &R) -> O, P: Fn(&L, &R) -> bool,

Performs an inner join with an additional filter predicate.

Like inner_join, but only includes pairs that satisfy both the join condition and the additional predicate.

Arguments

• left_key - Key-path to the join field in the left collection
• right_key - Key-path to the join field in the right collection
• predicate - Additional condition that must be true for pairs to be included
• mapper - Function to transform matching pairs into the result type

Example

// Join orders with products, but only high-value orders
let results = JoinQuery::new(&orders, &products)
    .inner_join_where(
        Order::product_id_r(),
        Product::id_r(),
        |order, _product| order.total > 100.0,
        |order, product| (product.name.clone(), order.total)
    );

Examples found in repository

examples/join_query_builder.rs (lines 352-357)

fn main() {
    println!("=== Join Query Builder Demo ===\n");

    let (users, orders, products) = create_sample_data();

    println!("Database:");
    println!("  Users: {}", users.len());
    println!("  Orders: {}", orders.len());
    println!("  Products: {}\n", products.len());

    // Join 1: Inner join Users and Orders
    println!("--- Join 1: Users with Their Orders ---");
    let user_orders = JoinQuery::new(&users, &orders).inner_join(
        User::id_r(),
        Order::user_id_r(),
        |user, order| UserOrder {
            user_name: user.name.clone(),
            user_email: user.email.clone(),
            order_id: order.id,
            quantity: order.quantity,
            total: order.total,
        },
    );

    for uo in &user_orders {
        println!(
            "  • {} - Order #{} - {} items - ${:.2}",
            uo.user_name, uo.order_id, uo.quantity, uo.total
        );
    }
    println!("Total: {} user-order pairs", user_orders.len());

    // Join 2: Three-way join (Orders -> Users, Orders -> Products)
    println!("\n--- Join 2: Complete Order Details (3-Way Join) ---");
    
    // First join: Orders with Users
    let orders_with_users = JoinQuery::new(&orders, &users).inner_join(
        Order::user_id_r(),
        User::id_r(),
        |order, user| (order.clone(), user.clone()),
    );

    // Second join: (Orders+Users) with Products
    let mut order_details = Vec::new();
    for (order, user) in &orders_with_users {
        for product in &products {
            if order.product_id == product.id {
                order_details.push(OrderDetail {
                    order_id: order.id,
                    user_name: user.name.clone(),
                    product_name: product.name.clone(),
                    quantity: order.quantity,
                    price: product.price,
                    total: order.total,
                });
            }
        }
    }

    for od in &order_details {
        println!(
            "  • Order #{}: {} bought {} x {} @ ${:.2} = ${:.2}",
            od.order_id, od.user_name, od.quantity, od.product_name, od.price, od.total
        );
    }

    // Join 3: Left join to show all users (including those without orders)
    println!("\n--- Join 3: All Users with Order Count (Left Join) ---");
    
    // Use left_join to get all users with their orders (or None)
    let user_order_pairs = JoinQuery::new(&users, &orders).left_join(
        User::id_r(),
        Order::user_id_r(),
        |user, order| (user.clone(), order.map(|o| o.clone())),
    );

    // Group by user to count orders
    let mut user_stats: HashMap<u32, (String, String, usize, f64)> = HashMap::new();
    for (user, order) in &user_order_pairs {
        let entry = user_stats
            .entry(user.id)
            .or_insert_with(|| (user.name.clone(), user.city.clone(), 0, 0.0));
        if let Some(order) = order {
            entry.2 += 1; // order count
            entry.3 += order.total; // total spent
        }
    }

    let mut user_order_stats: Vec<_> = user_stats
        .into_iter()
        .map(|(_, (name, city, count, total))| UserOrderCount {
            user_name: name,
            user_city: city,
            order_count: count,
            total_spent: total,
        })
        .collect();

    user_order_stats.sort_by(|a, b| a.user_name.cmp(&b.user_name));

    for stat in &user_order_stats {
        if stat.order_count > 0 {
            println!(
                "  • {} ({}) - {} orders - ${:.2} total",
                stat.user_name, stat.user_city, stat.order_count, stat.total_spent
            );
        } else {
            println!("  • {} ({}) - No orders yet", stat.user_name, stat.user_city);
        }
    }

    // Join 4: Aggregated join - Category sales analysis
    println!("\n--- Join 4: Sales by Product Category ---");

    // Join orders with products to get category information
    let order_products = JoinQuery::new(&orders, &products).inner_join(
        Order::product_id_r(),
        Product::id_r(),
        |order, product| (order.clone(), product.clone()),
    );

    // Aggregate by category
    let mut category_stats: HashMap<String, (Vec<u32>, f64, std::collections::HashSet<u32>)> =
        HashMap::new();

    for (order, product) in &order_products {
        let entry = category_stats
            .entry(product.category.clone())
            .or_insert_with(|| (Vec::new(), 0.0, std::collections::HashSet::new()));
        entry.0.push(order.id);
        entry.1 += order.total;
        entry.2.insert(order.user_id);
    }

    let mut category_sales: Vec<CategorySales> = category_stats
        .into_iter()
        .map(|(category, (orders, revenue, customers))| CategorySales {
            category,
            total_orders: orders.len(),
            total_revenue: revenue,
            unique_customers: customers.len(),
        })
        .collect();

    category_sales.sort_by(|a, b| {
        b.total_revenue
            .partial_cmp(&a.total_revenue)
            .unwrap_or(std::cmp::Ordering::Equal)
    });

    for cs in &category_sales {
        println!(
            "  • {}: {} orders - ${:.2} revenue - {} customers",
            cs.category, cs.total_orders, cs.total_revenue, cs.unique_customers
        );
    }

    // Join 5: Filtered join - High value orders
    println!("\n--- Join 5: High Value Orders (>$100) with User Details ---");
    let high_value_orders = JoinQuery::new(&orders, &users).inner_join_where(
        Order::user_id_r(),
        User::id_r(),
        |order, _user| order.total > 100.0,
        |order, user| (user.name.clone(), order.id, order.total),
    );

    for (name, order_id, total) in &high_value_orders {
        println!("  • {} - Order #{} - ${:.2}", name, order_id, total);
    }

    // Join 6: Users in same city analysis
    println!("\n--- Join 6: Users from Same City ---");
    let user_pairs = JoinQuery::new(&users, &users).inner_join_where(
        User::city_r(),
        User::city_r(),
        |u1, u2| u1.id < u2.id, // Avoid duplicates and self-pairs
        |u1, u2| (u1.name.clone(), u2.name.clone(), u1.city.clone()),
    );

    for (name1, name2, city) in &user_pairs {
        println!("  • {} and {} both live in {}", name1, name2, city);
    }

    // Join 7: Product popularity
    println!("\n--- Join 7: Product Popularity Ranking ---");
    
    // Join orders with products
    let product_order_pairs = JoinQuery::new(&products, &orders).inner_join(
        Product::id_r(),
        Order::product_id_r(),
        |product, order| (product.clone(), order.clone()),
    );

    // Aggregate by product
    let mut product_sales: HashMap<u32, (String, usize, u32, f64)> = HashMap::new();
    for (product, order) in &product_order_pairs {
        let entry = product_sales
            .entry(product.id)
            .or_insert_with(|| (product.name.clone(), 0, 0, 0.0));
        entry.1 += 1; // order count
        entry.2 += order.quantity; // total quantity
        entry.3 += order.total; // total revenue
    }

    let mut popularity: Vec<_> = product_sales.into_iter().collect();
    popularity.sort_by(|a, b| b.1 .1.cmp(&a.1 .1)); // sort by order count

    for (_, (name, order_count, total_qty, revenue)) in &popularity {
        println!(
            "  • {} - {} orders - {} units - ${:.2}",
            name, order_count, total_qty, revenue
        );
    }

    // Join 8: User spending by city
    println!("\n--- Join 8: Total Spending by City ---");
    
    // Join users with orders to get city and spending info
    let user_city_orders = JoinQuery::new(&users, &orders).inner_join(
        User::id_r(),
        Order::user_id_r(),
        |user, order| (user.city.clone(), order.total, user.id),
    );

    // Aggregate by city
    let mut city_spending: HashMap<String, (f64, std::collections::HashSet<u32>)> = HashMap::new();
    for (city, total, user_id) in &user_city_orders {
        let entry = city_spending
            .entry(city.clone())
            .or_insert_with(|| (0.0, std::collections::HashSet::new()));
        entry.0 += total;
        entry.1.insert(*user_id);
    }

    let mut city_stats: Vec<_> = city_spending
        .into_iter()
        .map(|(city, (total, customers))| (city, total, customers.len()))
        .collect();
    
    city_stats.sort_by(|a, b| {
        b.1.partial_cmp(&a.1).unwrap_or(std::cmp::Ordering::Equal)
    });

    for (city, total, customer_count) in &city_stats {
        println!(
            "  • {} - ${:.2} total - {} customers - ${:.2} avg",
            city,
            total,
            customer_count,
            total / *customer_count as f64
        );
    }

    // Statistics summary
    println!("\n=== Summary Statistics ===");
    println!("Total orders: {}", orders.len());
    
    let total_revenue: f64 = orders.iter().map(|o| o.total).sum();
    println!("Total revenue: ${:.2}", total_revenue);
    println!("Average order value: ${:.2}", total_revenue / orders.len() as f64);
    
    // Count unique customers using a join
    let unique_customers: std::collections::HashSet<u32> = 
        orders.iter().map(|o| o.user_id).collect();
    println!("Active customers: {}", unique_customers.len());
    println!(
        "Average orders per customer: {:.1}",
        orders.len() as f64 / unique_customers.len() as f64
    );

    println!("\n✓ Join query builder demo complete!");
}

pub fn right_join<K, O, F>( &self, left_key: KeyPaths<L, K>, right_key: KeyPaths<R, K>, mapper: F, ) -> Vec<O>

where K: Eq + Hash + Clone + 'static, F: Fn(Option<&L>, &R) -> O,

Performs a right join between two collections.

Returns all items from the right collection with optional matching items from the left collection. If no match is found, the left item is None.

Arguments

• left_key - Key-path to the join field in the left collection
• right_key - Key-path to the join field in the right collection
• mapper - Function to transform pairs into the result type (left item may be None)

Example

let results = JoinQuery::new(&users, &orders)
    .right_join(
        User::id_r(),
        Order::user_id_r(),
        |user, order| match user {
            Some(u) => format!("Order {} by {}", order.id, u.name),
            None => format!("Order {} by unknown user", order.id),
        }
    );

pub fn cross_join<O, F>(&self, mapper: F) -> Vec<O>

where F: Fn(&L, &R) -> O,

Performs a cross join (Cartesian product) between two collections.

Returns all possible pairs of items from both collections. Warning: This can produce very large result sets (size = left.len() * right.len()).

Arguments

• mapper - Function to transform pairs into the result type

Example

let all_combinations = JoinQuery::new(&colors, &sizes)
    .cross_join(|color, size| ProductVariant {
        color: color.clone(),
        size: size.clone(),
    });