Query Builder

from framefox.core.orm.query_builder import QueryBuilder
from framefox.core.orm.entity_manager import EntityManager

class QueryBuilder:
    def __init__(self, model: Type[Any], entity_manager: EntityManager):
        self.entity_manager = entity_manager
        self.model = model

user_repo = UserRepository()
qb = user_repo.get_query_builder()

from framefox.core.orm.query_builder import QueryBuilder
from framefox.core.orm.entity_manager import EntityManager
from myapp.models import User

entity_manager = EntityManager()
qb = QueryBuilder(model=User, entity_manager=entity_manager)

def select(self) -> "QueryBuilder":
    self._select = select(self.model)
    return self

qb = user_repo.get_query_builder()

# Simple select
users = qb.select().execute()

# Select with conditions
users = qb.select() \
    .where(User.is_active == True) \
    .execute()

def update(self, values: Dict[str, Any]) -> "QueryBuilder":
    self._update = sql_update(self.model).values(**values)
    return self

# Update multiple records
qb = user_repo.get_query_builder()
result = qb.update({"is_active": False}) \
    .where(User.last_login < cutoff_date) \
    .execute()

print(f"Updated {result} records")

def delete(self) -> "QueryBuilder":
    self._delete = sql_delete(self.model)
    return self

# Delete old records
qb = user_repo.get_query_builder()
result = qb.delete() \
    .where(User.created_at < one_year_ago) \
    .execute()

print(f"Deleted {result} records")

def where(self, condition: Any) -> "QueryBuilder":
    self._where.append(condition)
    return self

from sqlmodel import or_, and_

qb = user_repo.get_query_builder()

# Single condition
users = qb.select() \
    .where(User.age >= 18) \
    .execute()

# Multiple conditions (AND)
users = qb.select() \
    .where(User.age >= 18) \
    .where(User.is_active == True) \
    .execute()

# OR conditions
users = qb.select() \
    .where(or_(User.role == "admin", User.role == "moderator")) \
    .execute()

# Complex conditions
users = qb.select() \
    .where(and_(
        User.age >= 18,
        or_(User.country == "US", User.country == "CA")
    )) \
    .execute()

# Comparison operators
users = qb.select() \
    .where(User.age > 21) \
    .where(User.age <= 65) \
    .where(User.name.like("%John%")) \
    .execute()

def having(self, condition: Any) -> "QueryBuilder":
    self._having.append(condition)
    return self

from sqlmodel import func

# Group by with HAVING
result = qb.select() \
    .where(User.is_active == True) \
    .having(func.count(User.id) > 5) \
    .execute()

def order_by(self, *conditions: Any) -> "QueryBuilder":
    self._order_by.extend(conditions)
    return self

from sqlmodel import asc, desc

qb = user_repo.get_query_builder()

# Ascending order
users = qb.select() \
    .order_by(User.name.asc()) \
    .execute()

# Descending order
users = qb.select() \
    .order_by(User.created_at.desc()) \
    .execute()

# Multiple sort fields
users = qb.select() \
    .order_by(User.last_name.asc(), User.first_name.asc()) \
    .execute()

# Mixed sorting
users = qb.select() \
    .order_by(User.is_active.desc(), User.name.asc()) \
    .execute()

def limit(self, limit: int) -> "QueryBuilder":
    self._limit = limit
    return self

def offset(self, offset: int) -> "QueryBuilder":
    self._offset = offset
    return self

qb = user_repo.get_query_builder()

# First 10 users
users = qb.select() \
    .limit(10) \
    .execute()

# Pagination (page 2, 10 per page)
users = qb.select() \
    .order_by(User.id.asc()) \
    .limit(10) \
    .offset(10) \
    .execute()

# Function for pagination
def get_users_page(page: int, per_page: int = 20):
    qb = user_repo.get_query_builder()
    return qb.select() \
        .order_by(User.id.asc()) \
        .limit(per_page) \
        .offset((page - 1) * per_page) \
        .execute()

# Get page 3
users = get_users_page(page=3, per_page=20)

def join(self, *joins: Any) -> "QueryBuilder":
    if self._select is not None:
        self._select = self._select.join(*joins)
    elif self._delete is not None:
        self._delete = self._delete.join(*joins)
    elif self._update is not None:
        self._update = self._update.join(*joins)
    else:
        raise ValueError("No query (select, delete, update) has been initiated.")
    return self

# Inner join
results = qb.select() \
    .join(Profile) \
    .where(Profile.bio.contains("developer")) \
    .execute()

# Multiple joins
results = qb.select() \
    .join(Profile) \
    .join(Company) \
    .where(Company.name == "Acme Corp") \
    .execute()

def execute(self) -> List[Any]:
    query = self.get_query()
    with self.entity_manager.session as session:
        if isinstance(query, (Delete, Update)):
            result = session.exec(query)
            session.commit()
            return result
        else:
            result = session.exec(query).all()
            return result

# Returns a list of all matching records
users = qb.select() \
    .where(User.is_active == True) \
    .execute()

for user in users:
    print(user.name)

def first(self) -> Optional[Any]:
    query = self.get_query()
    with self.entity_manager.session as session:
        if isinstance(query, (Delete, Update)):
            raise ValueError("The 'first' method is not applicable for delete or update queries.")
        result = session.exec(query).first()
        return result

# Returns first matching record or None
user = qb.select() \
    .where(User.email == "[email protected]") \
    .first()

if user:
    print(f"Found: {user.name}")
else:
    print("User not found")

def last(self) -> Optional[Any]:
    query = self.get_query()
    
    if not self._order_by:
        raise ValueError("A sort order must be defined to use the 'last' method.")
    
    reversed_order = []
    for condition in self._order_by:
        if hasattr(condition, "desc"):
            reversed_condition = condition.asc()
        elif hasattr(condition, "asc"):
            reversed_condition = condition.desc()
        else:
            raise ValueError(f"Unsupported order condition: {condition}")
        reversed_order.append(reversed_condition)
    
    query = query.order_by(*reversed_order).limit(1)
    
    with self.entity_manager.session as session:
        if isinstance(query, (Delete, Update)):
            raise ValueError("The 'last' method is not applicable for delete or update queries.")
        result = session.exec(query).first()
        return result

# Requires order_by to be set
user = qb.select() \
    .order_by(User.created_at.asc()) \
    .last()

# This gets the most recently created user
if user:
    print(f"Last user: {user.name}")

from sqlmodel import or_, and_, func
from datetime import datetime, timedelta

qb = user_repo.get_query_builder()

# Find active users who:
# - Are admins or moderators
# - Registered in the last 30 days
# - Have more than 10 posts
thirty_days_ago = datetime.now() - timedelta(days=30)

users = qb.select() \
    .where(User.is_active == True) \
    .where(or_(User.role == "admin", User.role == "moderator")) \
    .where(User.created_at >= thirty_days_ago) \
    .order_by(User.created_at.desc()) \
    .limit(50) \
    .execute()

from sqlmodel import func, select

# Count active users
qb = user_repo.get_query_builder()
count_query = select(func.count(User.id)).where(User.is_active == True)
count = qb.entity_manager.exec_statement(count_query)[0]

# Average age
avg_query = select(func.avg(User.age)).where(User.is_active == True)
avg_age = qb.entity_manager.exec_statement(avg_query)[0]

# Group by with count
stats_query = select(
    User.country,
    func.count(User.id).label('user_count')
).group_by(User.country)
stats = qb.entity_manager.exec_statement(stats_query)

from sqlmodel import select

# Users who have more orders than average
avg_orders = select(func.avg(func.count(Order.id)))

users = qb.select() \
    .join(Order) \
    .having(func.count(Order.id) > avg_orders) \
    .execute()

# Deactivate old inactive users
qb = user_repo.get_query_builder()
result = qb.update({"is_active": False, "deactivated_at": datetime.now()}) \
    .where(User.last_login < one_year_ago) \
    .where(User.is_active == True) \
    .execute()

print(f"Deactivated {result} users")

# Delete unverified users older than 30 days
qb = user_repo.get_query_builder()
result = qb.delete() \
    .where(User.is_verified == False) \
    .where(User.created_at < thirty_days_ago) \
    .execute()

print(f"Deleted {result} unverified users")

def search_users(filters: dict, page: int = 1, per_page: int = 20):
    qb = user_repo.get_query_builder()
    query = qb.select()
    
    # Add filters dynamically
    if filters.get('name'):
        query = query.where(User.name.contains(filters['name']))
    
    if filters.get('email'):
        query = query.where(User.email == filters['email'])
    
    if filters.get('is_active') is not None:
        query = query.where(User.is_active == filters['is_active'])
    
    if filters.get('min_age'):
        query = query.where(User.age >= filters['min_age'])
    
    if filters.get('role'):
        query = query.where(User.role == filters['role'])
    
    # Add sorting
    sort_field = filters.get('sort', 'created_at')
    sort_dir = filters.get('direction', 'desc')
    
    if sort_dir == 'asc':
        query = query.order_by(getattr(User, sort_field).asc())
    else:
        query = query.order_by(getattr(User, sort_field).desc())
    
    # Add pagination
    query = query.limit(per_page).offset((page - 1) * per_page)
    
    return query.execute()

# Usage
filters = {
    'is_active': True,
    'min_age': 21,
    'role': 'admin',
    'sort': 'name',
    'direction': 'asc'
}

users = search_users(filters, page=2, per_page=10)