Query Builder

For developers who prefer communicating directly with the database instead of using an ORM, SigmaPHP provides a fast and lightweight Query Builder. It converts simple PHP method calls into SQL statements that run directly on your database and return the result.

To start using the Query Builder, simply retrieve an instance from the container using container('query_builder').


<?php

$queryBuilder = container('query_builder');

$queryBuilder->table('users')
    ->select(['name', 'age'])
    ->get();

SigmaPHP Query Builder returns results as associative arrays. For example, the query above would return a structure similar to the following:


[
    'name' => 'john doe',
    'age' => 30
]

Below is the list for all available methods:

table(string $tableName)

The table method defines the table on which the query will be executed. Every query should start by specifying the target table. You can also define an alias for the table if needed.


<?php

// getting all users
$queryBuilder->table('users')->getAll();

// setting a table alias
$queryBuilder->table('users as u')->getAll();

select(array $fields)

The select method defines the columns to be returned by the query. Fields may also include aliases or aggregate functions.


<?php

// select specific fields
$queryBuilder
    ->table('users')
    ->select(['name', 'email'])
    ->getAll();

// alias fields
$queryBuilder
    ->table('users')
    ->select([
        'name as n',
        'email as e'
    ])
    ->getAll();

// aggregate functions
$queryBuilder
    ->table('users')
    ->select(['count(*) as users_count'])
    ->get();

$queryBuilder
    ->table('users')
    ->select(['avg(age) as users_age_avg'])
    ->get();

where(string $column, string $operator, string $value)

The where method adds a condition to the query. Only one base where clause can be used, while additional conditions should be chained using andWhere or orWhere.


<?php

// basic condition
$queryBuilder
    ->table('users')
    ->select(['name', 'email', 'address'])
    ->where('id', '=', 5)
    ->get();

// numeric condition
$queryBuilder
    ->table('users')
    ->where('age', '>=', 18)
    ->getAll();

// like condition
$queryBuilder
    ->table('users')
    ->where('name', 'like', '%test%')
    ->getAll();

// null condition
$queryBuilder
    ->table('users')
    ->where('address', 'is not', 'null')
    ->getAll();

// date condition
$queryBuilder
    ->table('users')
    ->where(
        'date(joined_at)', '=', 'date_sub(now(),interval 3 year)'
    )
    ->getAll();

andWhere(string $column, string $operator, string $value)

Adds an additional condition to the query using the logical AND operator.


<?php

$queryBuilder
    ->table('users')
    ->select(['name', 'email', 'address'])
    ->where('age', '>=', 18)
    ->andWhere('address', 'is not', 'null')
    ->getAll();

orWhere(string $column, string $operator, string $value)

Adds an additional condition using the logical OR operator.


<?php

$queryBuilder
    ->table('users')
    ->select(['name', 'email', 'address'])
    ->where('address', 'is not', 'null')
    ->orWhere('city', '=', 'test')
    ->getAll();

whereBetween(string $column, int $min, int $max)

Filters results where a column value falls between two numbers.


<?php

$queryBuilder
    ->table('users')
    ->whereBetween('age', 10, 15)
    ->getAll();

whereIn(string $column, array $values)

Filters records where the column value matches one of the provided values.


<?php

$queryBuilder
    ->table('users')
    ->select(['name', 'email'])
    ->whereIn('city', ['test1', 'test2'])
    ->getAll();

having(string $column, string $operator, string $value)

Adds a HAVING clause to filter grouped results, usually used with aggregate functions.


<?php

$queryBuilder
    ->table('users')
    ->select(['name', 'email', 'avg(age) as age_avg'])
    ->having('age_avg', '<', '20')
    ->getAll();

distinct()

Removes duplicated records from the result set.


<?php

$queryBuilder
    ->table('users')
    ->select(['name'])
    ->distinct()
    ->getAll();

limit(int $count, int $offset)

Limits the number of records returned. You can optionally provide an offset to control where the results start.


<?php

// fetch first 5 users
$queryBuilder
    ->table('users')
    ->select(['name', 'email'])
    ->limit(5)
    ->getAll();

// fetch 10 users starting from offset 15
$queryBuilder
    ->table('users')
    ->select(['name', 'email'])
    ->limit(10, 15)
    ->getAll();

orderBy(array $columns)

Sorts results based on one or more columns in ascending or descending order.


<?php

$queryBuilder
    ->table('users')
    ->orderBy(['id asc', 'name desc'])
    ->getAll();

groupBy(array $columns)

Groups records by one or more columns.


<?php

$queryBuilder
    ->table('users')
    ->groupBy(['id', 'name'])
    ->getAll();

union(QueryBuilder $query, bool $all)

Combines the result of two queries. The second parameter controls whether duplicate values are included.


<?php

$query1 = new QueryBuilder($connection);

$query1
    ->table('customers')
    ->select(['id', 'name']);

$query2 = new QueryBuilder($connection);

$query2
    ->table('users')
    ->select(['id', 'name'])
    ->union($query1, true)
    ->getAll();

join(string $table, string $column1, string $operator, string $column2, string $type)

Joins multiple tables together. The default join type is INNER JOIN, but other join types can be specified using the $type parameter.


<?php

$this->queryBuilder
    ->table('users')
    ->select(['users.name as username', 'roles.name as role'])
    ->join('roles', 'users.role_id', '=', 'roles.id')
    ->getAll();

$this->queryBuilder
    ->table('users as u')
    ->select(['u.name', 'r.name'])
    ->join('roles as r', 'u.role_id', '=', 'r.id')
    ->getAll();

get()

Fetches a single record from the query.


<?php

$queryBuilder->table('users')->get();

getAll()

Fetches all matching records from the query.


<?php

$queryBuilder->table('users')->getAll();

print()

The print method outputs the SQL query that will be executed. This is useful for debugging, testing, or optimizing queries.


<?php

echo $queryBuilder->table('users')->print();

// output example
SELECT * FROM test;

↑ Back to top