Query Class

TODO: To be tested

Fluent interface for programmatic SQL query building. Works standalone or integrated with Models.

Usage Modes

Through Model (Recommended)

use Modules\Products\ProductsModel;

$products = new ProductsModel();

$results = $products
    ->where('in_stock = ?', [true])
    ->whereIn('category_id', [1, 2, 3])
    ->order('price', 'asc')
    ->limit(0, 10)
    ->getResults();  // Returns ProductsModel with results

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

Standalone

use App\Database\Query;
use App\Get;

$query = new Query('#__products', Get::db());
$query->select('id, name, price')
      ->where('in_stock = ?', [true])
      ->order('price', 'asc')
      ->limit(0, 10);

list($sql, $params) = $query->get();
$result = Get::db()->getResults($sql, $params);

foreach ($result as $row) {
    echo $row->name;
}

Query Building Methods

__construct(string $table, $db = null, $model = null)

Creates Query instance for specified table.

// Through Model (automatic)
$query = $model->query();

// Standalone
$query = new Query('#__products', Get::db());

select(string $fields) : Query

Specifies columns to select.

$results = $model
    ->select('id, name, price')
    ->getResults();

// Aggregate
$total = $model->query()
    ->select('COUNT(*) as total')
    ->getVar();

Use clean('select') to reset the SELECT clause on a Query instance.

from(string $from) : Query

Adds FROM or JOIN clauses.

$query->from('LEFT JOIN categories ON products.category_id = categories.id')
      ->from('LEFT JOIN brands ON products.brand_id = brands.id');

where($condition, $params = [], $operator = 'AND'): Query

Adds a WHERE condition to the query.

/**
 * @param string $condition SQL condition with placeholders (?)
 * @param array $params Values to bind
 * @param string $operator 'AND' or 'OR' to combine with previous conditions
 * @return Query
 */
public function where($condition, $params = [], $operator = 'AND'): Query;

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

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

// Example: OR condition
$results = $model
    ->where('status = ?', ['active'])
    ->where('status = ?', ['featured'], 'OR')
    ->getResults();

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

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

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

Adds a WHERE IN clause to filter by multiple values.

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

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

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

// 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();

// Example: Empty array (returns all records)
$products = $model
    ->whereIn('id', [])  // No filtering applied
    ->getResults();

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

Filters records based on related data using an EXISTS subquery. The relationship must be defined in the Model's configure() method.

/**
 * @param string $relationAlias Relationship alias from configure()
 * @param string $condition WHERE condition for related records
 * @param array $params Parameters for the condition
 * @param string $operator 'AND' or 'OR'
 * @return Query
 */
public function whereHas($relationAlias, $condition, $params = [], $operator = 'AND'): Query;

// Example 1: Find authors with books published after 2020
// Assumes AuthorsModel has: ->id()->hasMany('books', BooksModel::class, 'author_id')
$authors = $authorsModel
    ->whereHas('books', 'published_year > ?', [2020])
    ->getResults();

echo "Authors with recent books:\n";
foreach ($authors as $author) {
    echo "- " . $author->name . "\n";
}

// Example 2: Find books with high-rated reviews
// Assumes BooksModel has: ->id()->hasMany('reviews', ReviewsModel::class, 'book_id')
$books = $booksModel
    ->whereHas('reviews', 'rating > ?', [4])
    ->order('title', 'asc')
    ->getResults();

// Example 3: Find products ordered in the last 30 days
// Assumes ProductsModel has: ->id()->hasMany('orders', OrdersModel::class, 'product_id')
$recentProducts = $productsModel
    ->whereHas('orders', 'created_at > ?', [date('Y-m-d', strtotime('-30 days'))])
    ->getResults();

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

// Example 5: Multiple whereHas (AND logic)
$authors = $authorsModel
    ->whereHas('books', 'published_year > ?', [2020])
    ->whereHas('books', 'price > ?', [20])
    ->getResults();

Relationship Definition Required

Before using whereHas(), you must define the relationship in your Model's configure() method:

// AuthorsModel.php
class AuthorsModel extends AbstractModel {
    protected function configure($rule): void {
        $rule->table('#__authors')
            ->id()
            ->hasMany('books', BooksModel::class, 'author_id')  // Define relationship
            ->string('name', 100)->required()
            ->string('country', 50)->nullable();
    }
}

// BooksModel.php
class BooksModel extends AbstractModel {
    protected function configure($rule): void {
        $rule->table('#__books')
            ->id()
            ->hasMany('reviews', ReviewsModel::class, 'book_id')  // Define relationship
            ->int('author_id')->belongsTo('author', AuthorsModel::class, 'id')
            ->string('title', 200)->required()
            ->int('published_year');
    }
}

// Now you can use whereHas:
$authors = $authorsModel
    ->whereHas('books', 'published_year > ?', [2020])
    ->getResults();

order($field = '', $dir = 'asc'): Query

Adds ORDER BY clause to sort results.

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

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

// Example: Descending
$products = $model
    ->order('created_at', 'desc')
    ->getResults();

// Example: Multiple calls (accumulates order on the same Query)
$products = $model->query()
    ->order('category_id', 'asc')
    ->order('price', 'desc')
    ->getResults();

limit($start, $limit): Query

Limits the number of results (pagination).

/**
 * @param int $start Offset (records to skip)
 * @param int $limit Number of records to return
 * @return Query
 */
public function limit($start, $limit): Query;

// Example: First 10 records
$products = $model
    ->limit(0, 10)
    ->getResults();

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

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

// Get total for pagination
$total = $model
    ->where('in_stock = ?', [true])
    ->total();

group($group): Query

Adds GROUP BY clause.

/**
 * @param string $group Fields to group by
 * @return Query
 */
public function group($group): Query;

// Example: Group by category
$query = $model->query();
$query->select('category_id, COUNT(*) as total')
      ->group('category_id');

list($sql, $params) = $query->get();
$results = $db->getResults($sql, $params);

having($condition, $params = []): Query

Adds HAVING clause (used with GROUP BY).

/**
 * @param string $condition HAVING condition
 * @param array $params Parameters
 * @return Query
 */
public function having($condition, $params = []): Query;

// Example: Groups with more than 5 items
$query = $model->query();
$query->select('category_id, COUNT(*) as total')
      ->group('category_id')
      ->having('COUNT(*) > ?', [5]);

Query Execution Methods

Through Model

When using Query through a Model, these methods execute the query and return Model instances:

// getResults() - Returns Model with all matching records
$products = $model
    ->where('in_stock = ?', [true])
    ->getResults();  // Returns ProductsModel instance

echo $products->count();  // Number of results

// getRow() - Returns Model with single record
$product = $model
    ->where('id = ?', [1])
    ->getRow();  // Returns ProductsModel instance with 1 record

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

// getVar() - Returns single value
$count = $model->query()
    ->select('COUNT(*)')
    ->where('in_stock = ?', [true])
    ->getVar();  // Returns integer

Standalone

When using Query standalone, you manually execute with the database:

$query = new Query('#__products', $db);
$query->where('in_stock = ?', [true]);

// Get SQL and params
list($sql, $params) = $query->get();

// Execute manually
$db = Get::db();
$results = $db->getResults($sql, $params);  // Returns array of stdClass

// Or for single row
$row = $db->getRow($sql, $params);  // Returns stdClass

// Or for single value
$value = $db->getVar($sql, $params);  // Returns mixed

Helper Methods

clean($part = ''): Query

Removes specific parts of the query or resets it entirely.

// Clean specific part
$query->clean('limit');   // Remove LIMIT
$query->clean('where');   // Remove WHERE conditions
$query->clean('order');   // Remove ORDER BY
$query->clean('select');  // Remove SELECT clause

// Clean everything
$query->clean();

// Example: Reuse query without limit for counting
$query = $model->query()
    ->where('in_stock = ?', [true])
    ->limit(0, 10);

$results = $query->getResults();  // With limit

$query->clean('limit');
$total = $query->getVar();  // Without limit

Check Methods

// Check if query parts are set
$query->hasSelect();   // Has SELECT clause?
$query->hasWhere();    // Has WHERE conditions?
$query->hasOrder();    // Has ORDER BY?
$query->hasLimit();    // Has LIMIT?
$query->hasGroup();    // Has GROUP BY?

Complete Examples

Example 1: Product Listing with Filters (Through Model)

use Modules\Products\ProductsModel;

$products = new ProductsModel();

// Get filters from request
$search = $_GET['search'] ?? '';
$category = $_GET['category'] ?? 0;
$minPrice = $_GET['min_price'] ?? 0;
$page = $_GET['page'] ?? 1;
$perPage = 20;

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

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

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

// Execute with pagination
$offset = ($page - 1) * $perPage;
$results = $products
    ->where('in_stock = ?', [true])
    ->order('created_at', 'desc')
    ->limit($offset, $perPage)
    ->getResults();  // Returns ProductsModel

// Get total for pagination
$total = $products->total();

// Display results
echo "Showing " . $results->count() . " of $total products\n\n";

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

Example 2: Complex Query with JOINs (Standalone)

use App\Database\Query;
use App\Get;

$db = Get::db();
$query = new Query('#__products', $db);

// Build complex query
$query->select('products.*, categories.name as category_name, brands.name as brand_name')
      ->from('LEFT JOIN categories ON products.category_id = categories.id')
      ->from('LEFT JOIN brands ON products.brand_id = brands.id')
      ->where('products.in_stock = ?', [true])
      ->where('categories.active = ?', [1])
      ->order('products.name', 'asc')
      ->limit(0, 10);

// Execute
list($sql, $params) = $query->get();
$results = $db->getResults($sql, $params);

foreach ($results as $row) {
    echo "{$row->name} - {$row->category_name} ({$row->brand_name})\n";
}

Example 3: Using whereHas for Filtering by Relationships

// Find authors who have popular books (books with many high-rated reviews)

// Step 1: Define relationships in Models
class AuthorsModel extends AbstractModel {
    protected function configure($rule): void {
        $rule->table('#__authors')
            ->id()->hasMany('books', BooksModel::class, 'author_id')
            ->string('name', 100)->required();
    }
}

class BooksModel extends AbstractModel {
    protected function configure($rule): void {
        $rule->table('#__books')
            ->id()->hasMany('reviews', ReviewsModel::class, 'book_id')
            ->int('author_id')->belongsTo('author', AuthorsModel::class, 'id')
            ->string('title', 200)->required();
    }
}

// Step 2: Query authors with whereHas
$authors = $authorsModel
    ->whereHas('books', 'published_year > ?', [2020])
    ->order('name', 'asc')
    ->getResults();

echo "Authors with recent books:\n";
foreach ($authors as $author) {
    echo "- {$author->name}\n";

    // Can still access relationships via lazy loading
    $recentBooks = $author->books;  // Lazy loads books
    echo "  Books: " . count($recentBooks) . "\n";
}

Key Differences: Model vs Standalone

See Also