Documentation

QueryBuilder
in package

Fluent query builder for constructing SQL queries.

Supports both traditional escaped queries and parameterized prepared statements.

Usage (Traditional - escaped):

// SELECT query
$users = QueryBuilder::table('words')
    ->select(['WoID', 'WoText'])
    ->where('WoLgID', '=', 1)
    ->orderBy('WoText')
    ->limit(10)
    ->get();

// INSERT query
$id = QueryBuilder::table('words')
    ->insert(['WoText' => 'hello', 'WoLgID' => 1]);

Usage (Prepared Statements - recommended):

// SELECT with prepared statement
$users = QueryBuilder::table('words')
    ->select(['WoID', 'WoText'])
    ->where('WoLgID', '=', 1)
    ->orderBy('WoText')
    ->limit(10)
    ->getPrepared();

// INSERT with prepared statement
$id = QueryBuilder::table('words')
    ->insertPrepared(['WoText' => 'hello', 'WoLgID' => 1]);

// UPDATE with prepared statement
QueryBuilder::table('words')
    ->where('WoID', '=', 5)
    ->updatePrepared(['WoStatus' => 2]);

// DELETE with prepared statement
QueryBuilder::table('words')
    ->where('WoID', '=', 5)
    ->deletePrepared();
Tags
since
3.0.0

Table of Contents

Constants

USER_SCOPED_TABLES  = ['languages' => 'LgUsID', 'texts' => 'TxUsID', 'words' => 'WoUsID', 'tags' => 'TgUsID', 'text_tags' => 'T2UsID', 'news_feeds' => 'NfUsID', 'settings' => 'StUsID', 'local_dictionaries' => 'LdUsID']
Mapping of user-scoped tables to their user ID column.

Properties

$baseTableName  : string
$bindings  : array<int, mixed>
$columns  : array<int, string>
$distinct  : bool
$groups  : array<int, string>
$joins  : array<int, array{type: string, table: string, first: string, operator: string, second: string}>
$limitValue  : int|null
$offsetValue  : int|null
$orders  : array<int, array{column: string, direction: string}>
$table  : string
$userScopeEnabled  : bool
$wheres  : array<int, array{column: string, operator: string, value: mixed, boolean: string}>

Methods

__construct()  : mixed
Create a new query builder instance for a table.
count()  : int
Get the count of matching rows.
countPrepared()  : int
Get the count of matching rows using prepared statements.
delete()  : int
Delete matching rows.
deletePrepared()  : int
Delete matching rows using prepared statement.
distinct()  : static
Add DISTINCT to the query.
exists()  : bool
Check if any rows exist matching the query.
existsPrepared()  : bool
Check if any rows exist using prepared statements.
first()  : array<string|int, float|int|null|string>|null
Execute the query and return the first result.
firstPrepared()  : array<string, mixed>|null
Execute the query using prepared statements and return the first result.
get()  : array<string|int, array<string|int, float|int|null|string>>
Execute the query and return all results.
getBindings()  : array<int, mixed>
Get the bindings for prepared statements.
getPrepared()  : array<int, array<string, mixed>>
Execute the query using prepared statements and return all results.
groupBy()  : static
Add a GROUP BY clause.
insert()  : int|string
Insert a new row.
insertMany()  : int
Insert multiple rows.
insertManyPrepared()  : int
Insert multiple rows using prepared statement.
insertPrepared()  : int|string
Insert a new row using prepared statement.
join()  : static
Add a JOIN clause.
leftJoin()  : static
Add a LEFT JOIN clause.
limit()  : static
Set the LIMIT value.
offset()  : static
Set the OFFSET value.
orderBy()  : static
Add an ORDER BY clause.
orderByDesc()  : static
Add a descending ORDER BY clause.
orWhere()  : static
Add an OR WHERE clause.
rightJoin()  : static
Add a RIGHT JOIN clause.
select()  : static
Set the columns to select.
selectRaw()  : static
Set the columns to select using raw SQL expression.
table()  : self
Create a new query builder for a table.
toSql()  : string
Build the SELECT SQL query.
toSqlPrepared()  : string
Build the SELECT SQL query with placeholders for prepared statements.
truncate()  : void
Truncate the table.
update()  : int
Update matching rows.
updatePrepared()  : int
Update matching rows using prepared statement.
value()  : mixed
Execute the query and return a single column value.
valuePrepared()  : mixed
Execute the query using prepared statements and return a single column value.
where()  : static
Add a WHERE clause.
whereIn()  : static
Add a WHERE IN clause.
whereNotIn()  : static
Add a WHERE NOT IN clause.
whereNotNull()  : static
Add a WHERE NOT NULL clause.
whereNull()  : static
Add a WHERE NULL clause.
whereRaw()  : static
Add a raw WHERE clause.
withoutUserScope()  : static
Disable automatic user scope filtering for this query.
applyUserScope()  : void
Apply user scope filtering to the query if applicable.
compileWheres()  : string
Compile WHERE clauses into SQL.
compileWheresPrepared()  : string
Compile WHERE clauses into SQL with placeholders.
getUserIdColumn()  : string|null
Get the user ID column name for this table.
getUserScopeInsertData()  : array<string, int>
Get data to auto-inject for INSERT operations.
quoteValue()  : string
Quote a value for use in SQL.
resetBindings()  : void
Reset bindings array.

Constants

USER_SCOPED_TABLES

Mapping of user-scoped tables to their user ID column.

private array<string, string> USER_SCOPED_TABLES = ['languages' => 'LgUsID', 'texts' => 'TxUsID', 'words' => 'WoUsID', 'tags' => 'TgUsID', 'text_tags' => 'T2UsID', 'news_feeds' => 'NfUsID', 'settings' => 'StUsID', 'local_dictionaries' => 'LdUsID']

Tables listed here will automatically have user_id filtering applied when multi-user mode is enabled and a user is authenticated.

Properties

$baseTableName

private string $baseTableName

The base table name (without prefix) for user scope lookup

$bindings

private array<int, mixed> $bindings = []

Parameters for prepared statements

$columns

private array<int, string> $columns = ['*']

Columns to select

$distinct

private bool $distinct = false

Whether to use DISTINCT

$groups

private array<int, string> $groups = []

GROUP BY columns

$joins

private array<int, array{type: string, table: string, first: string, operator: string, second: string}> $joins = []

JOIN clauses

$limitValue

private int|null $limitValue = null

LIMIT value

$offsetValue

private int|null $offsetValue = null

OFFSET value

$orders

private array<int, array{column: string, direction: string}> $orders = []

ORDER BY clauses

$table

private string $table

The table name (with prefix)

$userScopeEnabled

private bool $userScopeEnabled = true

Whether user scope filtering is enabled for this query

$wheres

private array<int, array{column: string, operator: string, value: mixed, boolean: string}> $wheres = []

WHERE conditions

Methods

__construct()

Create a new query builder instance for a table.

public __construct(string $tableName) : mixed
Parameters
$tableName : string

The table name

count()

Get the count of matching rows.

public count([string $column = '*' ]) : int
Parameters
$column : string = '*'

The column to count (default: *)

Return values
int —

The count

countPrepared()

Get the count of matching rows using prepared statements.

public countPrepared([string $column = '*' ]) : int
Parameters
$column : string = '*'

The column to count (default: *)

Return values
int —

The count

delete()

Delete matching rows.

public delete() : int

Supports WHERE, ORDER BY, and LIMIT clauses for MySQL.

Return values
int —

Number of deleted rows

deletePrepared()

Delete matching rows using prepared statement.

public deletePrepared() : int
Return values
int —

Number of deleted rows

distinct()

Add DISTINCT to the query.

public distinct() : static
Return values
static

exists()

Check if any rows exist matching the query.

public exists() : bool
Return values
bool —

True if rows exist

existsPrepared()

Check if any rows exist using prepared statements.

public existsPrepared() : bool
Return values
bool —

True if rows exist

first()

Execute the query and return the first result.

public first() : array<string|int, float|int|null|string>|null
Tags
psalm-return

array<string, float|int|null|string>|null

Return values
array<string|int, float|int|null|string>|null —

The first row or null

firstPrepared()

Execute the query using prepared statements and return the first result.

public firstPrepared() : array<string, mixed>|null
Return values
array<string, mixed>|null —

The first row or null

get()

Execute the query and return all results.

public get() : array<string|int, array<string|int, float|int|null|string>>
Tags
psalm-return

list<non-empty-array<string, float|int|null|string>>

Return values
array<string|int, array<string|int, float|int|null|string>> —

Array of rows

getBindings()

Get the bindings for prepared statements.

public getBindings() : array<int, mixed>
Return values
array<int, mixed> —

The parameter bindings

getPrepared()

Execute the query using prepared statements and return all results.

public getPrepared() : array<int, array<string, mixed>>
Return values
array<int, array<string, mixed>> —

Array of rows

groupBy()

Add a GROUP BY clause.

public groupBy(string|array<int, string> $columns) : static
Parameters
$columns : string|array<int, string>

The column(s) to group by

Return values
static

insert()

Insert a new row.

public insert(array<string, mixed> $data) : int|string

Automatically injects user_id when multi-user mode is enabled.

Parameters
$data : array<string, mixed>

Column => value pairs to insert

Return values
int|string —

The last insert ID

insertMany()

Insert multiple rows.

public insertMany(array<int, array<string, mixed>> $rows) : int

Automatically injects user_id when multi-user mode is enabled.

Parameters
$rows : array<int, array<string, mixed>>

Array of column => value pairs

Return values
int —

Number of inserted rows

insertManyPrepared()

Insert multiple rows using prepared statement.

public insertManyPrepared(array<int, array<string, string|int|float|bool|null>> $rows) : int

Automatically injects user_id when multi-user mode is enabled.

Parameters
$rows : array<int, array<string, string|int|float|bool|null>>

Array of column => value pairs

Return values
int —

Number of inserted rows

insertPrepared()

Insert a new row using prepared statement.

public insertPrepared(array<string, mixed> $data) : int|string

Automatically injects user_id when multi-user mode is enabled.

Parameters
$data : array<string, mixed>

Column => value pairs to insert

Return values
int|string —

The last insert ID

join()

Add a JOIN clause.

public join(string $table, string $first[, string $operator = '=' ][, string $second = '' ][, string $type = 'INNER' ]) : static
Parameters
$table : string

The table to join

$first : string

The first column

$operator : string = '='

The join operator

$second : string = ''

The second column

$type : string = 'INNER'

The join type (INNER, LEFT, RIGHT)

Return values
static

leftJoin()

Add a LEFT JOIN clause.

public leftJoin(string $table, string $first[, string $operator = '=' ][, string $second = '' ]) : static
Parameters
$table : string

The table to join

$first : string

The first column

$operator : string = '='

The join operator

$second : string = ''

The second column

Return values
static

limit()

Set the LIMIT value.

public limit(int $limit) : static
Parameters
$limit : int

The maximum number of rows

Return values
static

offset()

Set the OFFSET value.

public offset(int $offset) : static
Parameters
$offset : int

The number of rows to skip

Return values
static

orderBy()

Add an ORDER BY clause.

public orderBy(string $column[, string $direction = 'ASC' ]) : static
Parameters
$column : string

The column to order by

$direction : string = 'ASC'

The sort direction (ASC/DESC)

Return values
static

orderByDesc()

Add a descending ORDER BY clause.

public orderByDesc(string $column) : static
Parameters
$column : string

The column to order by

Return values
static

orWhere()

Add an OR WHERE clause.

public orWhere(string $column[, mixed $operator = '=' ][, mixed $value = null ]) : static
Parameters
$column : string

The column name

$operator : mixed = '='

The comparison operator or value (if using 2-arg form)

$value : mixed = null

The value to compare against

Return values
static

rightJoin()

Add a RIGHT JOIN clause.

public rightJoin(string $table, string $first[, string $operator = '=' ][, string $second = '' ]) : static
Parameters
$table : string

The table to join

$first : string

The first column

$operator : string = '='

The join operator

$second : string = ''

The second column

Return values
static

select()

Set the columns to select.

public select([array<int, string>|string $columns = ['*'] ]) : static
Parameters
$columns : array<int, string>|string = ['*']

Columns to select

Return values
static

selectRaw()

Set the columns to select using raw SQL expression.

public selectRaw(string $expression) : static

This method accepts a raw SQL string for the SELECT clause, allowing complex expressions like function calls, aliases, and computed columns.

Parameters
$expression : string

Raw SQL expression for SELECT clause

Return values
static

table()

Create a new query builder for a table.

public static table(string $tableName) : self
Parameters
$tableName : string

The table name (without prefix)

Return values
self

toSql()

Build the SELECT SQL query.

public toSql() : string
Return values
string —

The SQL query

toSqlPrepared()

Build the SELECT SQL query with placeholders for prepared statements.

public toSqlPrepared() : string
Return values
string —

The SQL query with ? placeholders

truncate()

Truncate the table.

public truncate() : void

update()

Update matching rows.

public update(array<string, string|int|float|bool|null> $data) : int
Parameters
$data : array<string, string|int|float|bool|null>

Column => value pairs to update

Return values
int —

Number of affected rows

updatePrepared()

Update matching rows using prepared statement.

public updatePrepared(array<string, string|int|float|bool|null> $data) : int
Parameters
$data : array<string, string|int|float|bool|null>

Column => value pairs to update

Return values
int —

Number of affected rows

value()

Execute the query and return a single column value.

public value(string $column) : mixed
Parameters
$column : string

The column to retrieve

Return values
mixed —

The value or null

valuePrepared()

Execute the query using prepared statements and return a single column value.

public valuePrepared(string $column) : mixed
Parameters
$column : string

The column to retrieve

Return values
mixed —

The value or null

where()

Add a WHERE clause.

public where(string $column[, mixed $operator = '=' ][, mixed $value = null ][, string $boolean = 'AND' ]) : static
Parameters
$column : string

The column name

$operator : mixed = '='

The comparison operator or value (if using 2-arg form)

$value : mixed = null

The value to compare against

$boolean : string = 'AND'

The boolean connector (AND/OR)

Return values
static

whereIn()

Add a WHERE IN clause.

public whereIn(string $column, array<string|int, mixed> $values[, string $boolean = 'AND' ][, bool $not = false ]) : static
Parameters
$column : string

The column name

$values : array<string|int, mixed>

The values to check against

$boolean : string = 'AND'

The boolean connector

$not : bool = false

Whether to use NOT IN

Return values
static

whereNotIn()

Add a WHERE NOT IN clause.

public whereNotIn(string $column, array<string|int, mixed> $values) : static
Parameters
$column : string

The column name

$values : array<string|int, mixed>

The values to check against

Return values
static

whereNotNull()

Add a WHERE NOT NULL clause.

public whereNotNull(string $column) : static
Parameters
$column : string

The column name

Return values
static

whereNull()

Add a WHERE NULL clause.

public whereNull(string $column[, string $boolean = 'AND' ][, bool $not = false ]) : static
Parameters
$column : string

The column name

$boolean : string = 'AND'

The boolean connector

$not : bool = false

Whether to use IS NOT NULL

Return values
static

whereRaw()

Add a raw WHERE clause.

public whereRaw(string $sql[, string $boolean = 'AND' ]) : static
Parameters
$sql : string

Raw SQL for the WHERE clause

$boolean : string = 'AND'

The boolean connector

Return values
static

withoutUserScope()

Disable automatic user scope filtering for this query.

public withoutUserScope() : static

Use this for admin operations, migrations, or queries that need to access data across all users. Requires admin privileges when multi-user mode is enabled.

Usage:

// Get all words across all users (admin only)
$allWords = QueryBuilder::table('words')
    ->withoutUserScope()
    ->get();
Tags
throws
AuthException

If multi-user mode is enabled and user is not admin

since
3.0.0
Return values
static

applyUserScope()

Apply user scope filtering to the query if applicable.

private applyUserScope() : void

This method is called automatically before executing SELECT, UPDATE, and DELETE queries. It adds a WHERE clause filtering by the current user's ID when:

  • Multi-user mode is enabled
  • A user is authenticated
  • The table is user-scoped
  • User scope hasn't been disabled via withoutUserScope()

compileWheres()

Compile WHERE clauses into SQL.

private compileWheres() : string
Return values
string —

The WHERE clause SQL

compileWheresPrepared()

Compile WHERE clauses into SQL with placeholders.

private compileWheresPrepared() : string
Return values
string —

The WHERE clause SQL with ? placeholders

getUserIdColumn()

Get the user ID column name for this table.

private getUserIdColumn() : string|null
Return values
string|null —

The column name or null if not user-scoped

getUserScopeInsertData()

Get data to auto-inject for INSERT operations.

private getUserScopeInsertData() : array<string, int>

When multi-user mode is enabled and a user is authenticated, this returns the user_id column and value to add to inserts.

Return values
array<string, int> —

Column => value pairs to inject

quoteValue()

Quote a value for use in SQL.

private quoteValue(mixed $value) : string
Parameters
$value : mixed

The value to quote

Return values
string —

The quoted value

resetBindings()

Reset bindings array.

private resetBindings() : void 

        

        
    




    

    

                                

                

                                
    

        On this page

        
    


                

                            

            

    

        

            
Search results