Reading Data with PormG

This section covers the read side of PormG — querying, filtering, joining, aggregating, and transforming data from your database. Every query starts from Model.objects and uses a Django-inspired chainable API.

Section Map

Query Execution and Outputs

PormG provides several terminal methods to execute a query and return data in different formats:

Choosing an Output Format

query = M.Result.objects
query.filter("driverid__nationality" => "Brazilian", "positionorder" => 1)
query.values("driverid__surname", "raceid__name")

# As model-aware rows — best for ORM-style iteration
results = query.list()
for row in results
    println(row[:driverid__surname], " won at ", row[:raceid__name])
end

# As plain dictionaries — useful when another framework requires Dict values
dicts = query.list(:dict)

# As a DataFrame — best for analysis
df = query.values("driverid__surname", "raceid__year") |> DataFrame

# As JSON — best for API responses
json_str = query.list(:json)

# Just the count
n = query.count()      # => 42

# Just a boolean check
has_any = query.exists()  # => true

Rows returned by .list(), .first(), .last(), .earliest(), .latest(), .get(), .create(), .get_or_create(), and .update_or_create() are PormGRow values. They support property access, indexed access, many-to-many relationship accessors, and dirty tracking for row.save() and row.delete():

driver = M.Driver.objects.get("driverref" => "hamilton")

println(driver.forename, " ", driver.surname)

driver.nationality = "British"
driver.save()

For framework integrations that require plain dictionaries, use .list(:dict). For tabular analysis, pipe the query to DataFrame.

# ✗ raises: driverid was not projected, and PormG won't lazily load it
row = M.Result.objects.values("resultid", "points").first()
driver_name = row.driverid.forename

# ✓ project the related column, then read it
row = M.Result.objects.values("resultid", "driverid__forename").first()
driver_name = row[:driverid__forename]

# ✓ or project the raw foreign-key value
row = M.Result.objects.values("resultid", "driverid").first()
driver_id = row[:driverid]

Query Styles

PormG supports both a fluent interface (recommended) and a legacy pipe style.

Fluent Interface (Recommended)

Chain methods directly and finish with a terminal call:

# Full chain with terminal call
drivers = M.Driver.objects.
    filter("nationality" => "Brazilian").
    order_by("surname").
    limit(10).
    list()

# Route a query to another configured database pool
results = M.Result.objects.
    db("client_42").
    filter("points__@gt" => 10).
    list()

Pipe Style (Legacy)

The pipe style is still supported but the fluent form is preferred in docs and user-facing code:

query = M.Driver.objects |> filter("nationality" => "Brazilian")
df = query |> DataFrame

Handler Mutation Model

Every query handler follows four rules. They are where PormG deliberately differs from Django's clone-per-call querysets, so they are worth internalizing once:

1. Model.objects returns a fresh handler on every access. Two mentions of M.Driver.objects are two independent queries — state never leaks between them.
2. Chain methods mutate the handler in place and return that same handler (not a copy). Assigning a chain to a second variable aliases the same query.
3. .copy() is the branching escape hatch — deep-copy a base query, then extend each copy independently (example below).
4. Terminal methods never mutate the handler. count, exists, list, first, get, and the show_query/inspect_query inspection paths all execute on an internal copy of the handler. A handler stays reusable after any read terminal — q.first() does not leave a limit(1) behind, inline filters passed to q.get("field" => v) do not persist, and q.update(...) after q.first() is valid.

Re-call semantics: which methods accumulate

Calling the same chain method twice is not always the same operation. The semantics follow Django: filter accumulates, while values/order_by replace their previous call (Django documents this as "each order_by() call will clear any previous ordering").

q = M.Result.objects
q.filter("raceid__year" => 2019)
q.filter("positionorder" => 1)           # accumulates: year = 2019 AND positionorder = 1

q.values("driverid__surname")
q.values("driverid__surname", "points")  # replaces: only surname + points are selected

q.order_by("points")
q.order_by("-points")                    # replaces: ORDER BY points DESC only

Branching with .copy()

base_query = M.Result.objects.filter("positionorder" => 1)

# Reuse for different projections — each copy evolves independently
winners_by_driver = base_query.copy().values("driverid__surname", "wins" => Count("resultid"))
winners_by_team   = base_query.copy().values("constructorid__name", "wins" => Count("resultid"))

Chainable Methods Reference

These methods modify the query builder and return the handler for further chaining. The On re-call column states what a second call of the same method does (see the handler mutation model above):

Basic Retrieval Examples

Simple Filter and List

# Return model-aware rows
data = M.Status.objects.filter("status" => "Engine").list()

# Return a DataFrame
df = M.Status.objects.filter("status" => "Engine") |> DataFrame

Count and Existence Checks

count  = M.Status.objects.filter("status" => "Engine").count()
exists = M.Status.objects.filter("status" => "Engine").exists()

Pagination

# Page 1: first 20 results
page1 = M.Driver.objects.order_by("surname").limit(20).list()

# Page 2: skip 20, take 20
page2 = M.Driver.objects.order_by("surname").limit(20).offset(20).list()

# Same thing in one call — .page(limit, offset)
page2_alt = M.Driver.objects.order_by("surname").page(20, 20).list()

# .page(n) is limit-only: it sets LIMIT and leaves any offset already on the handler alone
top20 = M.Driver.objects.order_by("surname").page(20).list()

Distinct Results

nationalities = M.Driver.objects.values("nationality").distinct().list()

# ✗ raises: surname is not in the SELECT DISTINCT projection
M.Driver.objects.values("nationality").distinct().order_by("surname").list()

# ✓ include the sort key in values() (distinct over both columns) …
M.Driver.objects.values("nationality", "surname").distinct().order_by("surname").list()

# ✓ … or drop distinct() if you meant "one row per nationality, ordered by an aggregate"

Query Inspection

You can inspect the generated SQL without executing the query:

query = M.Result.objects.
    filter("driverid__nationality" => "Brazilian").
    values("driverid__surname", "points").
    order_by("-points")

# Get just the SQL string
sql = query.list(show_query=:sql)

# Get full metadata (SQL, parameters, dialect, operation)
meta = query.list(show_query=:dict)

# Benchmark the builder with zero overhead
@time query.list(show_query=:none)

# Dedicated inspection API with heuristic intent detection
inspection = query.inspect()
println(inspection[:sql_text])
println(inspection[:operation])  # => :select

show_query is supported on terminal methods such as list(), first(), get(), count(), exists(), delete(), update(), bulk_insert(), and bulk_update().

Database Routing

If you use multiple configured pools, select the target database per query:

# Route to a staging database
q = M.Driver.objects.db("staging").filter("code" => "SEN")

# Route to a tenant database (with lazy resolution)
results = M.Result.objects.db("client_42").filter("positionorder" => 1).list()

See Configuration: Dynamic Multi-Tenancy for setting up connection resolvers.

Reading Roadmap

If you are learning the API from scratch, the recommended order is:

1. Values and Joins — Start with column selection and __ join traversal.
2. Filters and Aggregates — Add lookup operators, grouping, and HAVING.
3. Functions and Dates — Use SQL functions, date extraction, and Case/When.
4. Subqueries and CTEs — Decompose complex queries with IN subqueries and WITH.
5. Field Expressions — Reach for F() when you need column-to-column logic or arithmetic.
6. Q Objects — Use Q()/Qor() only when plain filter pairs stop being expressive enough.