Struct JoinQuery 

pub struct JoinQuery<'a, 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: Clone, R: Clone> JoinQuery<'a, L, R>

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

Creates a new join query from two collections.

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/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!();
}

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/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!();
}

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(),
    });