API Query

Caligrafy\Query

Caligrafy Quill 2.26.0. Generated from the source by php qa/build-api-reference.php; the guides explain how to use these classes, this reference says exactly what each public method takes and returns. Back to the API reference.

Part of Data.

namespace Caligrafy;

class Query

Source: framework/core/Query.php:29

Class Query Builds one SQL statement with bound values only. Table and column names are validated against IDENTIFIER and quoted with backticks, operators come from an allow-list, values are never written into the SQL.

Database::table('books')->where('price', '<', 20)->orderBy('title')->limit(10)->get();   // arrays
Book::where('author_id', 3)->with('author', 'tags')->paginate(20);                       // Book models

Eager loading (with(), withCount()) runs one more query per relation (WHERE key IN (...), in chunks of 1000 keys) and attaches its rows under the relation's name; it never joins in a way that repeats the main rows. In development (APP_ENV other than production) the same SELECT shape run 10 times in one request is logged once as a probable N+1 query (error_log), with the file and line that ran it. The models of a result of several rows are guarded (2.26.0): a relation method run on one of them throws [ERROR 2004] in development and logs a warning in production (Model::strictRelations(), STRICT_RELATIONS=false, $model->lazy()).

Constants

• IDENTIFIER = '/^[A-Za-z_][A-Za-z0-9_]*(\.[A-Za-z_][A-Za-z0-9_]*)?$/'
• NAME = '/^[A-Za-z_][A-Za-z0-9_]*$/'
• IN_CHUNK = 1000
• NO_LIMIT = '9223372036854775807'

Public properties

• public static $threshold = 10: int identical SELECT shapes in a request before the N+1 warning
• public static $logger = null: callable|null receives the N+1 warning instead of error_log()
• public static $watching = null: bool|null null until the first query decides it from APP_ENV (see watching())

Methods

• ->__construct()
• Query::useExecutor()
• Query::watching()
• Query::watch()
• Query::forgetShapes()
• Query::observe()
• Query::shape()
• Query::caller()
• Query::quote()
• Query::plural()
• Query::singular()
• ->select()
• ->where()
• ->orWhere()
• ->whereIn()
• ->whereNotIn()
• ->whereNull()
• ->whereNotNull()
• ->whereBetween()
• ->search()
• ->orderBy()
• ->latest()
• ->oldest()
• ->limit()
• ->offset()
• ->input()
• ->models()
• ->arrays()
• ->with()
• ->withCount()
• Query::relation()
• ->toSql()
• ->getBindings()
• ->get()
• ->first()
• ->find()
• ->value()
• ->pluck()
• ->count()
• ->exists()
• ->paginate()
• ->chunk()
• ->insert()
• ->update()
• ->delete()
• ->increment()
• ->decrement()

->__construct()

public function __construct($table)

Parameters

• $table string: a table name (letters, digits, _; one optional schema prefix: db.table)

Source: framework/core/Query.php:78

Query::useExecutor()

public static function useExecutor($executor = null)

Sends the queries of the builder to $executor instead of Database (tests, another connection)

Parameters

• $executor object|null, default null: execute(string $sql, array $bindings): array of rows; optional lastInsertId() and rowCount(); null goes back to Database

Source: framework/core/Query.php:93

Query::watching()

public static function watching()

Whether the N+1 detection is on: decided once, from APP_ENV (off with production); watch() forces it

Source: framework/core/Query.php:104

Query::watch()

public static function watch($on = null)

Turns the N+1 detection on or off (null: from APP_ENV again) and forgets the shapes counted so far

Parameters

• $on, default null

Source: framework/core/Query.php:119

Query::forgetShapes()

public static function forgetShapes()

Forgets the shapes counted so far, keeping watch() as it is: a new request (Caligrafy\Testing\TestCase calls it before each test, so queries of earlier tests never add up to a warning)

Source: framework/core/Query.php:131

Query::observe()

public static function observe($sql)

Counts a SELECT by its shape (values, numbers and IN lists folded) and logs one warning when the same shape reaches $threshold in the request. Called by Database::execute() and by the builder's own executor.

Parameters

• $sql

Source: framework/core/Query.php:142

Query::shape()

public static function shape($sql)

The shape of a query: string and number literals become ?, IN lists one ?, spaces one space, lower case

Parameters

• $sql

Source: framework/core/Query.php:168

Query::caller()

public static function caller()

The file and line of the first frame outside framework/ ("the framework" when there is none)

Source: framework/core/Query.php:180

Query::quote()

public static function quote($name)

Validates an identifier (column, table, or table.column) and returns it quoted: books.title

Parameters

• $name

Source: framework/core/Query.php:194

Query::plural()

public static function plural($name)

The simple plural of a name: author -> authors, category -> categories, box/class/church/wish -> ...es

Parameters

• $name

Source: framework/core/Query.php:214

Query::singular()

public static function singular($name)

The singular that plural() inverts: authors -> author, categories -> category, boxes -> box

Parameters

• $name

Source: framework/core/Query.php:229

->select()

public function select(...$columns)

The columns to read: select('id', 'title'), select(['id', 'title']), 'books.*', 'title as name'

Parameters

• ...$columns

Source: framework/core/Query.php:251

->where()

public function where($column, $operator = null, $value = null)

where('price', '<', 20), where('author_id', 3) (=), where(['status' => 'new', 'author_id' => 3]), where(function ($q) { $q->where(...)->orWhere(...); }) for a group in parentheses. A null value with = / != becomes IS NULL / IS NOT NULL.

Parameters

• $column
• $operator, default null
• $value, default null

Source: framework/core/Query.php:288

->orWhere()

public function orWhere($column, $operator = null, $value = null)

No doc comment.

Parameters

• $column
• $operator, default null
• $value, default null

Source: framework/core/Query.php:298

->whereIn()

public function whereIn($column, array $values)

whereIn('id', [1, 2, 3]); an empty list matches no row (0 = 1)

Parameters

• $column
• $values array

Source: framework/core/Query.php:380

->whereNotIn()

public function whereNotIn($column, array $values)

whereNotIn('id', [1, 2]); an empty list adds no condition

Parameters

• $column
• $values array

Source: framework/core/Query.php:396

->whereNull()

public function whereNull($column)

No doc comment.

Parameters

• $column

Source: framework/core/Query.php:409

->whereNotNull()

public function whereNotNull($column)

No doc comment.

Parameters

• $column

Source: framework/core/Query.php:414

->whereBetween()

public function whereBetween($column, array $range)

whereBetween('price', [10, 20]): both ends included

Parameters

• $column
• $range array

Source: framework/core/Query.php:422

->search()

public function search(array $columns, $keywords)

search(['title', 'summary'], 'php mvc'): (title LIKE ? OR summary LIKE ?) with %php mvc% bound, % and _ of the keywords escaped so they match themselves. Empty keywords add no condition.

Parameters

• $columns array
• $keywords

Source: framework/core/Query.php:435

->orderBy()

public function orderBy($column, $direction = 'asc')

orderBy('title'), orderBy('created_at', 'desc')

Parameters

• $column
• $direction, default 'asc'

Source: framework/core/Query.php:457

->latest()

public function latest($column = 'created_at')

No doc comment.

Parameters

• $column, default 'created_at'

Source: framework/core/Query.php:469

->oldest()

public function oldest($column = 'created_at')

No doc comment.

Parameters

• $column, default 'created_at'

Source: framework/core/Query.php:474

->limit()

public function limit($count)

No doc comment.

Parameters

• $count

Source: framework/core/Query.php:479

->offset()

public function offset($count)

No doc comment.

Parameters

• $count

Source: framework/core/Query.php:485

->input()

public function input(array $input)

The input paginate() reads the page from (instead of $_GET): ->input($request->parameters)

Parameters

• $input array

Source: framework/core/Query.php:505

->models()

public function models($class = Model::class)

Rows become instances of $class (a Model) through its arrayToModel()

Parameters

• $class, default Model::class

Source: framework/core/Query.php:514

->arrays()

public function arrays()

Rows stay associative arrays (the default of Database::table(), and undoes models())

Source: framework/core/Query.php:526

->with()

public function with(...$relations)

with('author', 'tags', 'author.country'), with(['author' => ['table' => 'people', 'foreign_key' => 'writer_id']]), with(['tags' => ['pivot' => 'tag_links']]), with(['books' => function (Query $q) { $q->where('published', 1); }])

Parameters

• ...$relations

Source: framework/core/Query.php:536

->withCount()

public function withCount(...$relations)

withCount('books'): adds books_count to each row (hasMany or belongsToMany), one GROUP BY query

Parameters

• ...$relations

Source: framework/core/Query.php:555

Query::relation()

public static function relation($parent, $name, $config = null)

How a relation of $parent is read, by convention unless $config says otherwise:

• singular name (author) on books: belongsTo, books.author_id -> authors.id
• plural name (books) on authors: hasMany, books.author_id = authors.id
• ['pivot' => true|'name'] or type belongsToMany (tags on books): the pivot Model::belongsToMany() creates, books_tags (<this table>_<related table>), or tags_books when only that one exists (tried when books_tags fails, then remembered); books_tags.book_id = books.id, books_tags.tag_id = tags.id

Parameters

• $parent
• $name
• $config, default null

Returns array: type, table, foreign_key, local_key, owner_key, pivot, related_key, query

Source: framework/core/Query.php:641

->toSql()

public function toSql()

The SELECT this query runs (placeholders for the values: see getBindings())

Source: framework/core/Query.php:696

->getBindings()

public function getBindings()

No doc comment.

Source: framework/core/Query.php:715

->get()

public function get()

The rows: arrays, or models after models() (Book::query() hydrates Book by default)

Source: framework/core/Query.php:738

->first()

public function first()

No doc comment.

Source: framework/core/Query.php:760

->find()

public function find($id, $column = 'id')

No doc comment.

Parameters

• $id
• $column, default 'id'

Source: framework/core/Query.php:768

->value()

public function value($column)

The value of one column of the first row, or null

Parameters

• $column

Source: framework/core/Query.php:776

->pluck()

public function pluck($column, $key = null)

pluck('title') -> ['A', 'B']; pluck('title', 'id') -> [1 => 'A', 2 => 'B']

Parameters

• $column
• $key, default null

Source: framework/core/Query.php:788

->count()

public function count()

No doc comment.

Source: framework/core/Query.php:811

->exists()

public function exists()

No doc comment.

Source: framework/core/Query.php:822

->paginate()

public function paginate($perPage = 20, $page = null)

paginate(20): ['data' => rows, 'total' => n, 'page' => p, 'per_page' => 20, 'pages' => m] $page null reads 'page' from input() or $_GET; anything but a whole number >= 1 is page 1. A page past the last one returns no rows (and runs no second query).

Parameters

• $perPage, default 20
• $page, default null

Source: framework/core/Query.php:837

->chunk()

public function chunk($size, callable $callback)

Runs $callback($rows, $chunkNumber) on chunks of $size rows (ordered by id unless the query has an order); a callback that returns false stops. Returns false when stopped, true otherwise.

Parameters

• $size
• $callback callable

Source: framework/core/Query.php:871

->insert()

public function insert(array $values)

insert(['title' => 'A', 'price' => 10]) or insert(...], [...) (one INSERT for every row, same columns). Arrays and objects are saved as JSON, booleans as 0/1. Returns the last insert id.

Parameters

• $values array

Source: framework/core/Query.php:918

->update()

public function update(array $values)

update(['price' => 12]): needs a where() (LogicException otherwise). Returns the number of rows changed.

Parameters

• $values array

Source: framework/core/Query.php:948

->delete()

public function delete()

delete(): needs a where() (LogicException otherwise). Returns the number of rows deleted.

Source: framework/core/Query.php:966

->increment()

public function increment($column, $amount = 1, array $extra = array())

increment('views'), increment('stock', 5, ['restocked_at' => date('Y-m-d H:i:s')]): needs a where()

Parameters

• $column
• $amount, default 1
• $extra array, default array()

Source: framework/core/Query.php:975

->decrement()

public function decrement($column, $amount = 1, array $extra = array())

No doc comment.

Parameters

• $column
• $amount, default 1
• $extra array, default array()

Source: framework/core/Query.php:980