Query Builder & relations

1. Setup

Generate repositories with tpy build / tpy crud so each repo exposes query(). No extra install beyond your database driver.

from app.repositories.user_repository import UserRepository

repo = UserRepository()
rows = repo.query().where("email", "asha@example.com").get()
one = repo.query().find(user_id)

2. How the Query Builder works

1. repo.query() returns a fluent builder bound to the model table.
2. You chain filters (where), sorts (order_by), limits, etc.
3. Terminal methods (get, first, find, paginate) execute parameterized SQL (or Mongo helpers).

page = (
    repo.query()
    .where("published", True)
    .order_by("created_at", "desc")
    .paginate(page=1, per_page=20)
)

3. Relations in schema.tpy

model Post {
  id: uuid primary
  user_id: uuid references User on_delete cascade
  title: string required
  relations {
    belongs_to User as author via user_id
  }
}

model User {
  id: uuid primary
  email: string unique required
  relations {
    has_many Post as posts
  }
}

4. Eager loading

posts = repo.query().with_("author").get()
# Avoids N+1: loads authors in a follow-up query, then attaches them

5. Common mistakes

• String-concatenating SQL instead of using the builder (injection risk).
• Calling related attributes in a loop without with_().