Query Builder Methods

Revision: 2026/01/28

The Model provides a fluent query builder interface for constructing and executing database queries. All query building methods return a Query instance, allowing you to chain operations together.

💡 Method Chaining: Query methods can be chained together to build complex queries. The final method in the chain (getResults(), getRow(), etc.) executes the query and returns a Model instance with results.

Basic Query Building

`where(string $condition, array $params = []): Query`

Adds a WHERE clause to the query. Multiple WHERE clauses are combined with AND by default.

/**
 * @param string $condition SQL condition with placeholders (?)
 * @param array $params Parameters to bind (prevents SQL injection)
 * @return Query Query instance for method chaining
 */
public function where(string $condition, array $params = []): Query;

// Example: Single condition
$products = $model->where('price > ?', [100])->getResults();

// Example: Multiple conditions (AND)
$products = $model
    ->where('in_stock = ?', [true])
    ->where('price > ?', [10])
    ->where('category_id = ?', [5])
    ->getResults();

// Example: LIKE search
$products = $model
    ->where('name LIKE ?', ['%laptop%'])
    ->getResults();

// Example: Complex conditions
$products = $model
    ->where('(price > ? OR discount > ?)', [100, 20])
    ->where('status = ?', ['active'])
    ->getResults();

⚠️ SQL Injection Protection: Always use placeholders (?) and pass values via the $params array. Never concatenate user input directly into the condition string.

`whereIn(string $field, array $values, string $operator = 'AND'): Query`

Adds a WHERE IN clause to filter records where a field matches any value in an array.

/**
 * @param string $field Field name to check
 * @param array $values Array of values for IN clause
 * @param string $operator 'AND' or 'OR' to combine with previous conditions
 * @return Query Query instance for method chaining
 */
public function whereIn(string $field, array $values, string $operator = 'AND'): Query;

// Example: Filter by multiple IDs
$products = $model
    ->whereIn('id', [1, 5, 10, 15])
    ->getResults();

// Example: Filter by categories
$products = $model
    ->whereIn('category_id', [1, 2, 3])
    ->order('name', 'asc')
    ->getResults();

// Example: String values
$products = $model
    ->whereIn('status', ['active', 'pending', 'featured'])
    ->getResults();

// Example: Combine with other conditions
$products = $model
    ->where('in_stock = ?', [true])
    ->whereIn('category_id', [1, 2, 3])
    ->where('price > ?', [10])
    ->getResults();

`whereHas(string $relationAlias, string $condition, array $params = []): Query`

Filters records based on the existence of related records matching a condition. Uses an EXISTS subquery for optimal performance.

/**
 * @param string $relationAlias Relationship alias defined in configure()
 * @param string $condition WHERE condition for related records
 * @param array $params Parameters for the condition
 * @return Query Query instance for method chaining
 */
public function whereHas(string $relationAlias, string $condition, array $params = []): Query;

// Example: Find authors with books published after 2020
$authors = $authorsModel
    ->whereHas('books', 'published_year > ?', [2020])
    ->getResults();

// Example: Find books with high-rated reviews
$books = $booksModel
    ->whereHas('reviews', 'rating > ?', [4])
    ->getResults();

// Example: Find products with recent orders
$products = $productsModel
    ->whereHas('orders', 'created_at > ?', ['2025-01-01'])
    ->getResults();

// Example: Combine with regular WHERE
$authors = $authorsModel
    ->where('country = ?', ['USA'])
    ->whereHas('books', 'published_year > ?', [2020])
    ->getResults();

📖 Relationships Required: The relationship must be defined in the model's configure() method using hasOne(), hasMany(), or belongsTo().

Ordering and Limiting

`order(string|array $field = '', string $dir = 'asc'): Query`

Adds an ORDER BY clause to sort query results.

/**
 * @param string|array $field Field name or array of fields
 * @param string $dir Sort direction: 'asc' or 'desc'
 * @return Query Query instance for method chaining
 */
public function order(string|array $field = '', string $dir = 'asc'): Query;

// Example: Simple ordering
$products = $model->order('name', 'asc')->getResults();

// Example: Order by multiple fields
$products = $model
    ->order(['category_id', 'price'], ['asc', 'desc'])
    ->getResults();

// Example: Order with WHERE
$products = $model
    ->where('in_stock = ?', [true])
    ->order('price', 'desc')
    ->getResults();

`limit(int $start, int $limit = -1): Query`

Adds a LIMIT clause for pagination or restricting result count.

/**
 * @param int $start Offset (number of records to skip) OR limit if $limit is -1
 * @param int $limit Number of records to retrieve (default: -1)
 * @return Query Query instance for method chaining
 */
public function limit(int $start, int $limit = -1): Query;

// Example: Limit to 10 records
$products = $model->limit(10)->getResults();

// Example: Pagination (skip 20, take 10)
$products = $model->limit(20, 10)->getResults();

// Example: Paginated query
$page = $_GET['page'] ?? 1;
$perPage = 20;
$offset = ($page - 1) * $perPage;

$products = $model
    ->where('in_stock = ?', [true])
    ->order('created_at', 'desc')
    ->limit($offset, $perPage)
    ->getResults();

$total = $model->total();

`select(array|string $fields): Query`

Specifies which columns to select. By default, all columns (*) are selected.

/**
 * @param array|string $fields Fields to select (string or array)
 * @return Query Query instance for method chaining
 */
public function select(array|string $fields): Query;

// Example: Select specific fields (string)
$products = $model
    ->select('id, name, price')
    ->getResults();

// Example: Select specific fields (array)
$products = $model
    ->select(['id', 'name', 'price'])
    ->getResults();

// Example: With WHERE and ORDER
$products = $model
    ->select(['id', 'name', 'price'])
    ->where('in_stock = ?', [true])
    ->order('price', 'asc')
    ->getResults();

Query Execution Methods

`getResults(): static`

Executes the query and returns a Model instance containing all matching records.

/**
 * @return static Model instance with ResultInterface containing records
 */
public function getResults(): static;

// Example
$products = $model
    ->where('price > ?', [10])
    ->order('name', 'asc')
    ->limit(0, 20)
    ->getResults();

// Access results
echo "Found: " . $products->count() . " products\n";

foreach ($products as $product) {
    echo $product->name . ": €" . $product->price . "\n";
}

`getRow(): ?static`

Executes the query and returns a Model instance with a single record. Automatically adds LIMIT 1.

/**
 * @return static|null Model instance with one record or null
 */
public function getRow(): ?static;

// Example
$product = $model
    ->where('id = ?', [1])
    ->getRow();

if ($product && $product->count() > 0) {
    echo $product->name;
}

`getVar(): mixed`

Executes the query and returns a single value (first column of first row).

/**
 * @return mixed Single value from first row, first column
 */
public function getVar(): mixed;

// Example: Get count
$count = $model
    ->query()
    ->select('COUNT(*)')
    ->where('in_stock = ?', [true])
    ->getVar();

echo "Total in stock: $count";

Helper Methods

`getAll(): static`

Retrieves all records without any LIMIT (removes any previously set limit).

/**
 * @return static Model instance with all records
 */
public function getAll(): static;

// Example
$allProducts = $model->getAll();

// Example: With WHERE but no LIMIT
$activeProducts = $model
    ->where('status = ?', ['active'])
    ->getAll();

`getFirst(string $order_field = '', string $order_dir = 'asc'): ?static`

Retrieves the first record, optionally ordered by a specific field.

/**
 * @param string $order_field Field to order by
 * @param string $order_dir Sort direction
 * @return static|null Model instance with first record
 */
public function getFirst(string $order_field = '', string $order_dir = 'asc'): ?static;

// Example: First product by ID
$first = $model->getFirst('id', 'asc');

// Example: Latest product
$latest = $model->getFirst('created_at', 'desc');

// Example: With WHERE
$model->where('status = ?', ['active']);
$first = $model->getFirst('name', 'asc');

`total(): int`

Returns the total count of records matching the current query conditions (ignoring LIMIT).

/**
 * @return int Total number of matching records
 */
public function total(): int;

// Example: Total products
$total = $model->total();

// Example: With pagination
$products = $model
    ->where('in_stock = ?', [true])
    ->limit(0, 20)
    ->getResults();

$totalMatching = $model
    ->where('in_stock = ?', [true])
    ->total();

echo "Showing " . $products->count() . " of $totalMatching products";

Query State Management

Understanding how query state accumulates and resets is critical when building queries incrementally or reusing a model across multiple operations.

WHERE Clauses Accumulate

Each call to where() adds a condition to the same Query instance until the query is executed. Conditions are combined with AND.

// Conditions accumulate on the same query
$this->model->where('category_id = ?', [4]);
$query = $this->model->query();
$query_txt = $query->toSql();
// SELECT * FROM `products` WHERE (category_id = 4)

$query2 = $this->model->where('price > ?', [99]);
$query_txt2 = $query2->toSql();
// SELECT * FROM `products` WHERE (category_id = 4) AND (price > 99)
// ↑ both conditions are present

Automatic Reset After Execution

When a query is executed (via getRow(), getResults(), getFirst(), total()), the current_query is reset. The next call to where() or query() will create a fresh instance.

// First query: only category_id
$this->model->where('category_id = ?', [4]);
$query = $this->model->query();
$query_txt = $query->toSql();
// SELECT * FROM `products` WHERE (category_id = 4)

// Execute the query → this resets the state
$this->model->getRow();

// Second query: starts fresh, price is the only condition
$query2 = $this->model->where('price > ?', [99]);
$query_txt2 = $query2->toSql();
// SELECT * FROM `products` WHERE (price > 99)
// ↑ category_id is gone, query was reset after getRow()

⚠️ Warning: If you don't execute the query between two where() blocks, the conditions will stack up. The reset only happens after an execution (getRow, getResults, getFirst, total) or by explicitly calling newQuery().

Force a New Query with `newQuery()`

If you need to reset the query without executing it, use newQuery():

$this->model->where('status = ?', ['active']);

// Start fresh without executing
$query = $this->model->newQuery();
$query->where('category_id = ?', [5]);
$results = $query->getResults();
// SELECT * FROM `products` WHERE (category_id = 5)
// ↑ 'status = active' was discarded

Joins with from()

The from() method on Query allows adding joins or additional tables to the query. It supports full JOIN syntax including LEFT JOIN.

// LEFT JOIN: combine two tables
$results = $this->model->query()
    ->from('LEFT JOIN orders ON products.id = orders.product_id')
    ->where('orders.created_at > ?', ['2025-01-01'])
    ->getResults();

// Multiple JOINs
$results = $this->model->query()
    ->from('JOIN categories ON products.cat_id = categories.id')
    ->from('LEFT JOIN images ON products.id = images.product_id')
    ->select('products.*, categories.name as category, images.url')
    ->getResults();

// Subquery with from (additional table syntax)
$results = $this->model->query()
    ->from('(SELECT product_id, COUNT(*) as cnt FROM orders GROUP BY product_id) AS order_counts')
    ->where('order_counts.cnt > ?', [5])
    ->getResults();

💡 Alternative to JOINs: For filtering based on relationships defined in the model, prefer whereHas() which uses a more performant EXISTS subquery and doesn't require writing JOIN syntax manually.

Testing with ArrayDb

To test queries without hitting the real database, you can convert a model to an ArrayDb via setDbType('array'). Queries are then executed in memory on mock data.

// Convert the model to ArrayDb for testing
$model = new ProductModel();
$model->setDbType('array');

// Queries are now executed in memory
$results = $model->where('category_id = ?', [4])->getResults();

// Also useful to inspect the generated SQL without hitting the DB
$model->where('status = ?', ['active']);
$query = $model->query();
echo $query->toSql(); // See the built query without executing it

💡 Available DB types: setDbType('db') for the main database, setDbType('db2') for the secondary database, setDbType('array') for in-memory ArrayDb.

Results and isEmpty()

Execution methods like getById(), getRow() and getResults() always return a new model with the loaded data. If the query finds no results, the model is still returned but will be empty.

// getById returns a new model with the data
$product = $model->getById(123);

// isEmpty() checks whether the model has data
if (!$product->isEmpty()) {
    echo "Found: " . $product->name;
} else {
    echo "Product not found";
}

// Same behavior with getRow()
$row = $model->where('code = ?', ['ABC'])->getRow();
if ($row->isEmpty()) {
    echo "No results for code ABC";
}

// And with getResults() for multiple results
$results = $model->where('status = ?', ['active'])->getResults();
if (!$results->isEmpty()) {
    echo "Found " . $results->count() . " active products";
    foreach ($results as $item) {
        echo $item->name . "\n";
    }
}

⚠️ Don't check for null: Query methods always return a Model instance, never null. Always use isEmpty() or count() > 0 to verify whether data was found.

Advanced Examples

Complex Search with Multiple Conditions

$search = $_GET['search'] ?? '';
$category = $_GET['category'] ?? '';
$minPrice = $_GET['min_price'] ?? 0;

$query = $model->query();

if ($search) {
    $query->where('name LIKE ?', ['%' . $search . '%']);
}

if ($category) {
    $query->where('category_id = ?', [$category]);
}

if ($minPrice > 0) {
    $query->where('price >= ?', [$minPrice]);
}

$products = $query
    ->where('in_stock = ?', [true])
    ->order('created_at', 'desc')
    ->limit(0, 20)
    ->getResults();

$total = $model->total();

Filtering with Relationships

// Find authors who have books with high-rated reviews
$authors = $authorsModel
    ->whereHas('books', 'id IN (
        SELECT book_id FROM #__reviews WHERE rating > 4
    )', [])
    ->order('name', 'asc')
    ->getResults();

// Find products that have been ordered recently
$products = $productsModel
    ->whereHas('orders', 'created_at > ?', [date('Y-m-d', strtotime('-30 days'))])
    ->order('name', 'asc')
    ->getResults();

Dynamic Filtering

// Build query based on filters array
$filters = [
    'status' => 'active',
    'categories' => [1, 2, 3],
    'min_price' => 10,
    'search' => 'laptop'
];

$query = $model->query();

if (isset($filters['status'])) {
    $query->where('status = ?', [$filters['status']]);
}

if (!empty($filters['categories'])) {
    $query->whereIn('category_id', $filters['categories']);
}

if (isset($filters['min_price'])) {
    $query->where('price >= ?', [$filters['min_price']]);
}

if (isset($filters['search'])) {
    $query->where('name LIKE ?', ['%' . $filters['search'] . '%']);
}

$results = $query->getResults();

Milk Admin

Getting started

Abstracts Class

Builders

Advanced

Modules

Expressions

Extensions

Form

List

Model

Related Content

Table

Query Builder Methods

Basic Query Building

where(string $condition, array $params = []): Query

whereIn(string $field, array $values, string $operator = 'AND'): Query

whereHas(string $relationAlias, string $condition, array $params = []): Query