Skip to content

Query Builder Eager Loading

Dory Azar edited this page Nov 15, 2026 · 2 revisions

Eager loading reads the related rows of a query in one more query per relation, instead of one query per row. Listing 50 books and then reading each one's author runs 51 queries (the N+1 problem); with('author') runs two: one for the books, one for all their authors (WHERE id IN (...)). withCount() adds a count per row in one GROUP BY query. While you develop, Caligrafy warns you when the same query runs 10 times in a request. Since 2.19.0.

Since 2.26.0 a relation method called on one of several rows read by a query throws [ERROR 2004] Lazy loading prevented in development (a warning in production): load the relation with with() and read it as a property. $book->lazy()->author() or #[Caligrafy\Lazy] on the method allow a call that is meant; STRICT_RELATIONS=false makes it a warning.

$books   = Book::with('author', ['tags' => ['pivot' => true]])->get();   // author: a row or null; tags: a list
$authors = Author::withCount('books')->get();                            // books_count on each
$loan    = Database::table('loans')->with('book.author')->first();       // the loan, its book, the book's author

With Claude

/caligrafy-quill:new-capability get_book books:read read-only: one book with its author, its loans and its tags

Claude reads the rows with Book::with(...), and never queries in a loop.

Check before you say OK:

  • related rows come from with() or withCount(), not a query inside a foreach;
  • many-to-many relations say ['pivot' => true] (or the pivot's name);
  • php caligrafer.php call get_book id=3 shows the related rows under their names (author, loans, tags).

By hand

  1. Name the relations. with() follows the name you give and the table it is called on, so the models need no code:
    $book = Book::with('author', 'loans', array('tags' => array('pivot' => true)))->find($input['id']);
    Four queries run: the book, then its author, its loans and its tags, each with WHERE ... IN (...).
  2. Count instead of loading.
    return Author::withCount('books')->orderBy('name')->get();   // each author with books_count
  3. Narrow the related rows with a function:
    Book::with(['loans' => function ($q) { $q->whereNull('returned_at'); }])->get();

Reference: how with() reads a name

Name Called on Kind It reads You get
author (singular) books belongs to books.author_id -> authors.id one row, or null
books (plural) authors has many books.author_id = authors.id a list, or []
['tags' => ['pivot' => true]] books many to many books_tags.book_id = books.id, books_tags.tag_id = tags.id a list, or []
book.author loans nested loans.book_id -> books.id, then books.author_id -> authors.id book with its author
['current_loan' => ['type' => 'hasOne', 'table' => 'loans']] books has one loans.book_id = books.id the first row, or null
  • The related rows are arrays, also under models: $book->author['name'], $book->tags[0]['name'].
  • The key must be in the rows: Book::select('id', 'title')->with('author') has no author_id, so author is null.
  • The pivot of books and tags is books_tags; when it does not exist but tags_books does, with() uses that one.
  • Keys are read in chunks of 1000 (Query::IN_CHUNK); the main rows are never repeated by a join.

Reference: the settings of a relation

Book::with(['writer' => ['table' => 'authors', 'foreign_key' => 'author_id']])->find(3);   // $book->writer
Book::with(['current_loan' => ['type' => 'hasOne', 'table' => 'loans', 'query' => function ($q) { $q->whereNull('returned_at'); }]])->get();
Setting Meaning Default
type belongsTo, hasOne, hasMany or belongsToMany singular name: belongsTo; plural: hasMany; with pivot: belongsToMany
table the related table the name in the plural (belongsTo, hasOne), the name itself (hasMany, belongsToMany)
foreign_key belongs to: the column of this table; has one/many: the column of the related table; many to many: the pivot's column toward this table <name>_id (belongs to), <this table in the singular>_id (the others)
local_key the column of this table the others point at id
owner_key the column of the related table that is pointed at id
pivot true (the conventional pivot) or its name none
related_key the pivot's column toward the related table <name in the singular>_id
query a function that narrows the related rows none

An unknown setting, or a name that is not letters, digits and _, is refused before any query runs. All of it: Relationships.

withCount()

Database::table('tags')->withCount(['books' => ['pivot' => true]])->pluck('books_count', 'name');
Author::withCount(['books' => function ($q) { $q->where('published_year', '<', 1970); }])->pluck('books_count', 'name');

It counts plural relations only (has many, many to many): withCount('author') is refused. A row with nothing to count gets 0.


Reference: the N+1 warning

While you develop (APP_ENV other than production), when the same SELECT shape runs 10 times in one request, Caligrafy writes one line in the PHP error log, with the first file and line outside framework/:

[Caligrafy] Probable N+1 query: the same SELECT ran 10 times in this request (select * from `books` where `id` = ? limit ?), from <the file>:<the line>. Load the rows once with with(): Database::table('books')->with('author')->get() or Book::with('author')->get().
  • It counts every query run through Database::execute(), models included. Production does not count.
  • Query::$threshold (10) is the count; Query::$logger (a callable) receives the line instead of error_log(); Query::watch(true) or watch(false) forces it on or off, watch(null) decides from APP_ENV again and forgets the shapes counted (API).

Common mistakes

  • Relations follow names, not the database (G124): irregular plurals (person/people) need table or type; a limit() in a query constraint limits the whole relation query, not each parent; a constraint on a many-to-many relation runs on the join: qualify columns (tags.label).
  • select() without the key column: the relation is null.
  • The N+1 warning in a worker (G127): APP_ENV is read once per process, so a long worker counts across jobs; call Query::watch(null) between jobs.
  • A pivot inside Database::transaction() on PostgreSQL: a failed query aborts the transaction; name the pivot (G124).

Related pages

Caligrafy: Describe it. Approve it. Ship it. Your idea turned into a complete app: every feature built once, for web pages, Vue apps, a REST API and AI agents, and online.

Start here

Why Caligrafy: the benefits, the measurements, and what it is not yet

  1. Quickstart with Claude: describe it, approve it, ship it

    or the Quickstart: the same three steps by hand, in 5 minutes

  2. How Caligrafy works: the ideas in 5 minutes
  3. Your first capability: build a feature, page included

Capabilities

Experiences

Data and people

  1. Models
  2. Relationships
  3. Queries: filters, paging, related rows
  4. Sign-in, users and tokens

Built-in features

Put it online

Build with AI

Reference

Clone this wiki locally