Query Operations

def select(gen):
    """Execute SELECT query using generator expression.
    
    Args:
        gen: Generator expression defining query criteria
        
    Returns:
        Query object with results (iterable)
        
    Usage:
        select(p for p in Person if p.age > 18)
        select(p.name for p in Person if p.age > 18)
        select((p.name, p.age) for p in Person if p.age > 18)
    """

def get(gen):
    """Get single entity using generator expression.
    
    Args:
        gen: Generator expression that should match exactly one entity
        
    Returns:
        Single entity instance
        
    Raises:
        ObjectNotFound: No entities match criteria
        MultipleObjectsFoundError: Multiple entities match criteria
        
    Usage:
        get(p for p in Person if p.email == "alice@example.com")
    """

def exists(gen):
    """Check if any entities match generator expression criteria.
    
    Args:
        gen: Generator expression defining criteria
        
    Returns:
        bool: True if any entities match
        
    Usage:
        exists(p for p in Person if p.age > 100)
    """

def delete(gen):
    """Delete entities matching generator expression criteria.
    
    Args:
        gen: Generator expression defining entities to delete
        
    Returns:
        int: Number of deleted entities
        
    Usage:
        delete(p for p in Person if p.age < 0)
    """

def left_join(gen):
    """Execute LEFT JOIN query using generator expression.
    
    Args:
        gen: Generator expression with left join criteria
        
    Returns:
        Query object with left join results
        
    Usage:
        left_join((p, o) for p in Person for o in p.orders if o.total > 100)
    """

class Query:
    def count(self):
        """Get count of query results without fetching all data."""
        
    def limit(self, n, offset=0):
        """Limit query results.
        
        Args:
            n: Maximum number of results
            offset: Number of results to skip
        """
        
    def order_by(self, *args):
        """Order query results.
        
        Args:
            *args: Attributes or expressions to order by
        """
        
    def distinct(self):
        """Remove duplicate results."""
        
    def filter(self, lambda_expr):
        """Add additional filter to query.
        
        Args:
            lambda_expr: Lambda expression for filtering
        """
        
    def first(self):
        """Get first result or None if no results."""
        
    def random(self, n=1):
        """Get random results.
        
        Args:
            n: Number of random results
        """
        
    def page(self, page_num, page_size=10):
        """Get specific page of results.
        
        Args:
            page_num: Page number (1-based)
            page_size: Results per page
        """
        
    def prefetch(self, *attrs):
        """Prefetch related attributes to avoid N+1 queries.
        
        Args:
            *attrs: Attribute names to prefetch
        """
        
    def show(self):
        """Print SQL query for debugging."""
        
    def get_sql(self):
        """Get SQL query string."""

from pony.orm import *

with db_session:
    # Simple selection
    adults = select(p for p in Person if p.age >= 18)
    
    # Select specific attributes
    names = select(p.name for p in Person if p.age >= 18)
    
    # Select multiple attributes as tuples
    name_age_pairs = select((p.name, p.age) for p in Person if p.age >= 18)
    
    # Iterate over results
    for person in adults:
        print(f"{person.name} is {person.age} years old")
    
    # Convert to list
    adult_list = list(adults)
    
    # Get single entity
    alice = get(p for p in Person if p.email == "alice@example.com")
    
    # Check existence
    has_minors = exists(p for p in Person if p.age < 18)
    
    # Delete entities
    deleted_count = delete(p for p in Person if p.last_login < date(2020, 1, 1))

with db_session:
    # Join with related entities
    big_orders = select(o for o in Order 
                       if o.customer.country == "USA" and o.total > 1000)
    
    # Multiple joins
    tech_employees = select(e for e in Employee 
                           if e.company.industry == "Technology" 
                           and e.department.name == "Engineering")
    
    # Left join for optional relationships
    all_customers_with_orders = left_join(
        (c, o) for c in Customer for o in c.orders
    )
    
    # Select from join results
    customer_order_data = select(
        (c.name, o.total if o else 0) 
        for c in Customer 
        for o in c.orders
    )

with db_session:
    # String operations
    gmail_users = select(p for p in Person if p.email.endswith("@gmail.com"))
    john_variants = select(p for p in Person if p.name.startswith("John"))
    
    # Numeric comparisons
    millennials = select(p for p in Person if 1981 <= p.birth_year <= 1996)
    
    # Date operations
    recent_orders = select(o for o in Order 
                          if o.date >= datetime.now() - timedelta(days=30))
    
    # Collection operations
    prolific_authors = select(a for a in Author if len(a.books) > 5)
    
    # Complex boolean logic
    target_customers = select(c for c in Customer 
                             if (c.age > 25 and c.income > 50000) 
                             or (c.loyalty_years > 5))
    
    # IN operations using collections
    tech_companies = ["Google", "Microsoft", "Apple"]
    tech_employees = select(e for e in Employee 
                           if e.company.name in tech_companies)

with db_session:
    # Get query object (not executed yet)
    query = select(p for p in Person if p.age > 18)
    
    # Count results without fetching
    adult_count = query.count()
    
    # Ordering
    ordered_adults = query.order_by(Person.name)
    reverse_ordered = query.order_by(desc(Person.age))
    
    # Limiting results  
    first_10_adults = query.limit(10)
    page_2_adults = query.limit(10, offset=10)
    
    # Pagination
    page_2 = query.page(2, page_size=20)
    
    # Get first result
    oldest_adult = query.order_by(desc(Person.age)).first()
    
    # Random selection
    random_adults = query.random(5)
    
    # Remove duplicates
    unique_ages = select(p.age for p in Person).distinct()
    
    # Additional filtering
    senior_adults = query.filter(lambda p: p.age > 65)
    
    # Prefetch related data to avoid N+1 queries
    people_with_orders = select(p for p in Person).prefetch('orders')

with db_session:
    # Subqueries
    avg_age = select(avg(p.age) for p in Person).get()
    above_average = select(p for p in Person if p.age > avg_age)
    
    # Correlated subqueries
    customers_with_big_orders = select(c for c in Customer 
                                      if exists(o for o in c.orders 
                                               if o.total > 1000))
    
    # Query with calculations
    order_summaries = select(
        (o.customer.name, o.total, o.total * 0.1) 
        for o in Order 
        if o.total > 100
    )
    
    # Using raw SQL in queries
    complex_calculation = select(
        p for p in Person 
        if raw_sql("EXTRACT(YEAR FROM $p.birth_date)") > 1990
    )
    
    # Multiple entity selection
    customer_product_pairs = select(
        (c, p) for c in Customer for p in Product 
        if p in c.favorite_products
    )

with db_session:
    # Show generated SQL
    query = select(p for p in Person if p.age > 18)
    query.show()  # Prints the SQL query
    
    # Get SQL string
    sql_string = query.get_sql()
    print(f"Generated SQL: {sql_string}")
    
    # Enable SQL debugging globally
    set_sql_debug(True)
    
    # All queries will now print SQL
    adults = select(p for p in Person if p.age > 18)
    
    # Disable SQL debugging
    set_sql_debug(False)