Advanced Usage — Aquilia Documentation
Comprehensive guide and documentation for Advanced Usage in the Aquilia framework. View API reference, examples, and implementation patterns.
Docs / Models / Advanced Advanced Transactions, expressions, database functions, SQL builders, constraints, and choices — the full power of Aquilia's ORM. Expression System Aquilia's expression system lets you build complex SQL expressions in Python. All expressions implement as_sql() for SQL generation. Expression Purpose Example SQL ))} When / Case Expressions Build SQL CASE WHEN expressions for conditional logic in queries. from aquilia.models.expression import When, Case, Value, F # Simple CASE WHEN users = await ( User.objects .annotate( tier=Case( When(points__gte=1000, then=Value("gold")), When(points__gte=500, then=Value("silver")), When(points__gte=100, then=Value("bronze")), default=Value("basic"), ) ) .all() ) # CASE with expressions orders = await ( Order.objects .annotate( discount_price=Case( When(quantity__gte=100, then=F("price") * Value(0.8)), When(quantity__gte=50, then=F("price") * Value(0.9)), default=F("price"), ) ) .all() ) # Conditional update await ( Product.objects .update( status=Case( When(stock=0, then=Value("out_of_stock")), When(stock__lt=10, then=Value("low_stock")), default=Value("in_stock"), ) ) ) Subqueries from aquilia.models.expression import Subquery, Exists, OuterRef # Subquery — embed a QuerySet as a scalar subquery latest_comment = ( Comment.objects .filter(post_id=OuterRef("id")) .order("-created_at") .values("text") .limit(1) ) posts = await ( Post.objects .annotate(latest_comment=Subquery(latest_comment)) .all() ) # Exists — boolean subquery for filtering has_comments = Exists( Comment.objects.filter(post_id=OuterRef("id")) ) posts_with_comments = await ( Post.objects .filter(has_comments) .all() ) # OuterRef — reference a column from the outer query # Used inside Subquery/Exists to correlate with the parent query Database Functions Built-in SQL function wrappers for use in annotations, filters, and updates: Category Functions Comparison Coalesce, Greatest, Least, NullIf String Length, Upper, Lower, Trim, LTrim, RTrim, Concat, Substr, Replace Math Abs, Round, Power Date/Time Now Type Cast, Func (custom), ExpressionWrapper from aquilia.models.expression import ( Coalesce, Greatest, Least, NullIf, Length, Upper, Lower, Trim, Concat, Substr, Replace, Abs, Round, Power, Now, Cast, Func, ExpressionWrapper, F, Value, ) # Coalesce — first non-NULL value users = await ( User.objects .annotate(display_name=Coalesce(F("nickname"), F("name"), Value("Anonymous"))) .all() ) # String functions users = await ( User.objects .annotate( name_len=Length(F("name")), upper_name=Upper(F("name")), initials=Concat(Substr(F("first_name"), 1, 1), Substr(F("last_name"), 1, 1)), ) .all() ) # Math functions products = await ( Product.objects .annotate( rounded_price=Round(F("price"), 2), abs_diff=Abs(F("price") - F("cost")), ) .all() ) # Cast — type conversion users = await ( User.objects .annotate(age_text=Cast(F("age"), "TEXT")) .all() ) # Now() — current timestamp await User.objects.filter(id=1).update(last_seen=Now()) # Greatest / Least await Product.objects.annotate( effective_price=Least(F("price"), F("sale_price")), ).all() # NullIf — return NULL if equal await User.objects.annotate( real_name=NullIf(F("name"), Value("")), ).all() SQL Builder API Low-level fluent SQL builders for when you need full control over query construction. Used internally by the Q class. from aquilia.models.sql_builder import ( SQLBuilder, InsertBuilder, UpdateBuilder, DeleteBuilder, CreateTableBuilder, AlterTableBuilder, UpsertBuilder, ) # SELECT builder sql, params = ( SQLBuilder("users") .select("id", "name", "email") .where("active = ?", True) .where("age >= ?", 18) .order_by("name ASC") .limit(10) .offset(20) .build() ) # → ("SELECT id, name, email FROM users WHERE active = ? AND age >= ? # ORDER BY name ASC LIMIT 10 OFFSET 20", [True, 18]) # INSERT builder sql, params = ( InsertBuilder("users") .columns("name", "email", "active") .values("Alice", "alice@co.com", True) .returning("id") .build() ) # UPDATE builder sql, params = ( UpdateBuilder("users") .set(name="Bob", email="bob@co.com") .where("id = ?", 42) .build() ) # DELETE builder sql, params = ( DeleteBuilder("users") .where("active = ?", False) .build() ) # CREATE TABLE builder sql = ( CreateTableBuilder("products") .column("id", "BIGSERIAL PRIMARY KEY") .column("name", "VARCHAR(200) NOT NULL") .column("price", "DECIMAL(10,2)") .if_not_exists() .build() ) # ALTER TABLE builder sql = ( AlterTableBuilder("users") .add_column("phone", "VARCHAR(20)") .build() ) # UPSERT builder (INSERT ... ON CONFLICT) sql, params = ( UpsertBuilder("users") .columns("email", "name") .values("alice@co.com", "Alice Updated") .conflict_columns("email") .update_columns("name") .build() ) Constraints from aquilia.models.constraint import CheckConstraint, ExclusionConstraint, Deferrable class Product(Model): table = "products" name = CharField(max_length=200) price = DecimalField(max_digits=10, decimal_places=2) sale_price = DecimalField(max_digits=10, decimal_places=2, null=True) class Meta: constraints = [ # Check constraint — arbitrary SQL condition CheckConstraint( check="price > 0", name="positive_price", ), # Sale price must be less than regular price CheckConstraint( check="sale_price IS NULL OR sale_price Choices — TextChoices & IntegerChoices Enum-like classes that generate (value, label) pairs for field choices. {`from aquilia.models.enums import Choices, TextChoices, IntegerChoices class Status(TextChoices): DRAFT = "draft", "Draft" REVIEW = "review", "In Review" PUBLISHED = "published", "Published" ARCHIVED = "archived", "Archived" class Priority(IntegerChoices): LOW = 1, "Low" MEDIUM = 2, "Medium" HIGH = 3, "High" CRITICAL = 4, "Critical" class Article(Model): table = "articles" title = CharField(max_length=200) status = CharField(max_length=20, choices=Status.choices, default=Status.DRAFT) priority = IntegerField(choices=Priority.choices, default=Priority.MEDIUM) # Usage article = Article(title="Test", status=Status.PUBLISHED) # Access choices Status.choices # → [("draft", "Draft"), ("review", "In Review"), ...] Status.values # → ["draft", "review", "published", "archived"] Status.labels # → ["Draft", "In Review", "Published", "Archived"] Status.names # → ["DRAFT", "REVIEW", "PUBLISHED", "ARCHIVED"] # Membership testing Status.DRAFT in Status.values # → True # Custom Choices base class class Choices: """Base class providing .choices, .values, .labels, .names properties.""" Custom Database Functions Extend the expression system with your own SQL functions using the Func base class. from aquilia.models.expression import Func, F, Value # Custom function — wraps any SQL function class DateTrunc(Func): function = "DATE_TRUNC" # Usage users = await ( User.objects .annotate( signup_month=DateTrunc(Value("month"), F("created_at")) ) .group_by("signup_month") .annotate(count=Count("id")) .order("signup_month") .values("signup_month", "count") .all() ) # Generic Func usage class JSONExtract(Func): function = "JSON_EXTRACT" data = await ( Config.objects .annotate(theme=JSONExtract(F("settings"), Value("$.theme"))) .values("id", "theme") .all() ) Aggregation Serializers );
Go to Homepage