1. Go to this page and download the library: Download utopia-php/query library. Choose the download type require.
2. Extract the ZIP file and open the index.php.
3. Add this code to the index.php.
<?php
require_once('vendor/autoload.php');
/* Start to develop here. Best regards https://php-download.com/ */
use Utopia\Query\Builder\MySQL as Builder;
$result = (new Builder())
->from('users')
->whereRaw('LENGTH(`bio`) > ?', [100])
->build();
// SELECT * FROM `users` WHERE LENGTH(`bio`) > ?
// Correlated subquery for a lateral join
$topOrder = (new Builder())
->from('orders')
->select(['product', 'amount'])
->whereColumn('orders.user_id', '=', 'u.id')
->sortDesc('amount')
->limit(1);
$result = (new Builder())
->from('users', 'u')
->joinLateral($topOrder, 'top_order')
->build();
$result = (new Builder())
->from('orders')
->count('*', 'total')
->sum('price', 'total_price')
->select(['status'])
->groupBy(['status'])
->having([Query::greaterThan('total', 5)])
->build();
// SELECT COUNT(*) AS `total`, SUM(`price`) AS `total_price`, `status`
// FROM `orders` GROUP BY `status` HAVING `total` > ?
$result = (new Builder())
->from('users')
->distinct()
->select(['country'])
->build();
// SELECT DISTINCT `country` FROM `users`
use Utopia\Query\Builder\PostgreSQL as Builder;
$result = (new Builder())
->from('measurements')
->stddev('value', 'std_dev')
->stddevPop('value', 'pop_std_dev')
->stddevSamp('value', 'samp_std_dev')
->variance('value', 'var')
->varPop('value', 'pop_var')
->varSamp('value', 'samp_var')
->build();
use Utopia\Query\Builder\PostgreSQL as Builder;
$result = (new Builder())
->from('orders')
->countWhen('status = ?', 'active_count', 'active')
->sumWhen('amount', 'status = ?', 'active_total', 'active')
->build();
// PostgreSQL: COUNT(*) FILTER (WHERE status = ?) AS "active_count", SUM("amount") FILTER (WHERE status = ?) AS "active_total"
// MySQL: COUNT(CASE WHEN status = ? THEN 1 END) AS `active_count`, SUM(CASE WHEN status = ? THEN `amount` END) AS `active_total`
// ClickHouse: countIf(status = ?) AS `active_count`, sumIf(`amount`, status = ?) AS `active_total`
use Utopia\Query\Builder\MySQL as Builder;
// Concatenate values into a string
$result = (new Builder())
->from('tags')
->select(['post_id'])
->groupConcat('name', ', ', 'tag_list', orderBy: ['name'])
->groupBy(['post_id'])
->build();
// MySQL: GROUP_CONCAT(`name` ORDER BY `name` ASC SEPARATOR ', ')
// PostgreSQL: STRING_AGG("name", ', ' ORDER BY "name" ASC)
// ClickHouse: arrayStringConcat(groupArray(`name`), ', ')
// JSON array aggregation
$result = (new Builder())
->from('items')
->jsonArrayAgg('name', 'names_json')
->build();
// JSON object aggregation from key/value pairs
$result = (new Builder())
->from('settings')
->jsonObjectAgg('key', 'value', 'settings_json')
->build();
use Utopia\Query\Builder\MySQL as Builder;
// WITH ROLLUP — adds subtotal and grand total rows
$result = (new Builder())
->from('sales')
->select(['region', 'product'])
->sum('amount', 'total')
->groupBy(['region', 'product'])
->withRollup()
->build();
// WITH CUBE — adds subtotals for all dimension combinations (MySQL 8.0.1+, PostgreSQL, ClickHouse)
$result = (new Builder())
->from('sales')
->select(['region', 'product'])
->sum('amount', 'total')
->groupBy(['region', 'product'])
->withCube()
->build();
// WITH TOTALS (ClickHouse) — adds a totals row
use Utopia\Query\Builder\ClickHouse as ChBuilder;
$result = (new ChBuilder())
->from('events')
->select(['event_type'])
->count('*', 'cnt')
->groupBy(['event_type'])
->withTotals()
->build();
use Utopia\Query\Builder\PostgreSQL as Builder;
// Advance the sequence and return the next value
$result = (new Builder())
->nextVal('order_seq', 'next_id')
->build();
// PostgreSQL: SELECT nextval('order_seq') AS "next_id"
// MariaDB: SELECT NEXTVAL(`order_seq`) AS `next_id`
// Return the session-local current value
$result = (new Builder())
->currVal('order_seq', 'current_id')
->build();
$result = (new Builder())
->from('users')
->join('orders', 'users.id', 'orders.user_id')
->leftJoin('profiles', 'users.id', 'profiles.user_id')
->rightJoin('notes', 'users.id', 'notes.user_id')
->crossJoin('colors')
->naturalJoin('defaults')
->build();
// SELECT * FROM `users`
// JOIN `orders` ON `users`.`id` = `orders`.`user_id`
// LEFT JOIN `profiles` ON `users`.`id` = `profiles`.`user_id`
// RIGHT JOIN `notes` ON `users`.`id` = `notes`.`user_id`
// CROSS JOIN `colors`
// NATURAL JOIN `defaults`
use Utopia\Query\Builder\JoinType;
$result = (new Builder())
->from('users')
->joinWhere('orders', function ($join) {
$join->on('users.id', 'orders.user_id')
->where('orders.status', '=', 'active');
}, JoinType::Left)
->build();
use Utopia\Query\Builder\PostgreSQL as Builder;
$result = (new Builder())
->from('left_table')
->fullOuterJoin('right_table', 'left_table.id', 'right_table.id')
->build();
// SELECT * FROM "left_table" FULL OUTER JOIN "right_table" ON "left_table"."id" = "right_table"."id"
$sub = (new Builder())
->from('orders')
->whereColumn('orders.user_id', '=', 'users.id')
->limit(3);
$result = (new Builder())
->from('users')
->joinLateral($sub, 'recent_orders')
->build();
$admins = (new Builder())->from('admins')->filter([Query::equal('role', ['admin'])]);
$result = (new Builder())
->from('users')
->filter([Query::equal('status', ['active'])])
->union($admins)
->build();
// SELECT * FROM `users` WHERE `status` IN (?)
// UNION SELECT * FROM `admins` WHERE `role` IN (?)
$activeUsers = (new Builder())->from('users')->filter([Query::equal('status', ['active'])]);
$result = (new Builder())
->with('active_users', $activeUsers)
->from('active_users')
->select(['name'])
->build();
// WITH `active_users` AS (SELECT * FROM `users` WHERE `status` IN (?))
// SELECT `name` FROM `active_users`
$seed = (new Builder())->from('employees')->filter([Query::isNull('manager_id')]);
$step = (new Builder())->from('employees')->join('org', 'employees.manager_id', 'org.id');
$result = (new Builder())
->withRecursiveSeedStep('org', $seed, $step)
->from('org')
->build();
$result = (new Builder())
->from('sales')
->select(['employee', 'amount'])
->selectWindow('ROW_NUMBER()', 'row_num', partitionBy: ['department'], orderBy: ['amount'])
->selectWindow('SUM(amount)', 'running_total', partitionBy: ['department'], orderBy: ['date'])
->build();
// SELECT `employee`, `amount`,
// ROW_NUMBER() OVER (PARTITION BY `department` ORDER BY `amount` ASC) AS `row_num`,
// SUM(amount) OVER (PARTITION BY `department` ORDER BY `date` ASC) AS `running_total`
// FROM `sales`
$result = (new Builder())
->from('sales')
->select(['employee', 'amount'])
->window('w', partitionBy: ['department'], orderBy: ['date'])
->selectWindow('ROW_NUMBER()', 'row_num', windowName: 'w')
->selectWindow('SUM(amount)', 'running_total', windowName: 'w')
->build();
// SELECT `employee`, `amount`,
// ROW_NUMBER() OVER `w` AS `row_num`,
// SUM(amount) OVER `w` AS `running_total`
// FROM `sales`
// WINDOW `w` AS (PARTITION BY `department` ORDER BY `date` ASC)
use Utopia\Query\Builder\Case\Expression as CaseExpression;
use Utopia\Query\Builder\Case\Operator;
$case = (new CaseExpression())
->when('amount', Operator::GreaterThan, 1000, 'high')
->when('amount', Operator::GreaterThan, 100, 'medium')
->else('low')
->alias('priority');
$result = (new Builder())
->from('orders')
->select(['id'])
->selectCase($case)
->build();
// SELECT `id`, CASE WHEN `amount` > ? THEN ? WHEN `amount` > ? THEN ? ELSE ? END AS `priority`
// FROM `orders`
// Single row
$result = (new Builder())
->into('users')
->set(['name' => 'Alice', 'email' => '[email protected]'])
->insert();
// Batch insert — one set() call per row
$result = (new Builder())
->into('users')
->set(['name' => 'Alice', 'email' => '[email protected]'])
->set(['name' => 'Bob', 'email' => '[email protected]'])
->insert();
// INSERT ... SELECT
$source = (new Builder())->from('archived_users')->filter([Query::equal('status', ['active'])]);
$result = (new Builder())
->into('users')
->fromSelect(['name', 'email'], $source)
->insertSelect();
$result = (new Builder())
->from('users')
->set(['status' => 'inactive'])
->setRaw('updated_at', 'NOW()')
->filter([Query::equal('id', [42])])
->update();
// UPDATE `users` SET `status` = ?, `updated_at` = NOW() WHERE `id` IN (?)
$result = (new Builder())
->from('users')
->filter([Query::equal('status', ['deleted'])])
->delete();
// DELETE FROM `users` WHERE `status` IN (?)
// MySQL — ON DUPLICATE KEY UPDATE
$result = (new Builder())
->into('counters')
->set(['key' => 'visits', 'value' => 1])
->onConflict(['key'], ['value'])
->upsert();
// PostgreSQL — ON CONFLICT (...) DO UPDATE SET
use Utopia\Query\Builder\PostgreSQL as PgBuilder;
$result = (new PgBuilder())
->into('counters')
->set(['key' => 'visits', 'value' => 1])
->onConflict(['key'], ['value'])
->upsert();
$result = (new Builder())
->into('counters')
->set(['key' => 'visits', 'value' => 1])
->onConflict(['key'], [])
->insertOrIgnore();
// MySQL: INSERT IGNORE INTO `counters` ...
// PostgreSQL: INSERT INTO "counters" ... ON CONFLICT ("key") DO NOTHING
// SQLite: INSERT OR IGNORE INTO `counters` ...
$source = (new Builder())->from('staging')->select(['key', 'value']);
$result = (new Builder())
->into('counters')
->fromSelect(['key', 'value'], $source)
->onConflict(['key'], ['value'])
->upsertSelect();
$result = (new Builder())
->from('accounts')
->filter([Query::equal('id', [1])])
->forUpdate()
->build();
// SELECT * FROM `accounts` WHERE `id` IN (?) FOR UPDATE
$builder = new Builder();
$builder->begin(); // BEGIN
$builder->savepoint('sp1'); // SAVEPOINT `sp1`
$builder->rollbackToSavepoint('sp1');
$builder->commit(); // COMMIT
$builder->rollback(); // ROLLBACK
use Utopia\Query\Builder\MySQL as Builder;
// Basic explain
$result = (new Builder())
->from('users')
->filter([Query::equal('status', ['active'])])
->explain();
// MySQL — with format
$result = (new Builder())
->from('users')
->explain(analyze: true, format: 'JSON');
// PostgreSQL — with analyze, verbose, buffers, format
use Utopia\Query\Builder\PostgreSQL as PgBuilder;
$result = (new PgBuilder())
->from('users')
->explain(analyze: true, verbose: true, buffers: true, format: 'JSON');
use Utopia\Query\Builder\Statement;
$result = (new Builder())
->from('users')
->beforeBuild(fn (Builder $b) => $b->filter([Query::isNotNull('email')]))
->afterBuild(fn (Statement $s) => new Statement("/* traced */ {$s->query}", $s->bindings, $s->readOnly))
->build();
$sql = (new Builder())
->from('users')
->filter([Query::equal('status', ['active'])])
->limit(10)
->toRawSql();
// SELECT * FROM `users` WHERE `status` IN ('active') LIMIT 10
use Utopia\Query\Hook\Attribute\Map;
$result = (new Builder())
->from('users')
->addHook(new Map([
'$id' => '_uid',
'$createdAt' => '_createdAt',
]))
->filter([Query::equal('$id', ['abc'])])
->build();
// SELECT * FROM `users` WHERE `_uid` IN (?)
use Utopia\Query\Hook\Filter\Tenant;
$result = (new Builder())
->from('users')
->addHook(new Tenant(['tenant_abc']))
->filter([Query::equal('status', ['active'])])
->build();
// SELECT * FROM `users`
// WHERE `status` IN (?) AND `tenant_id` IN (?)
use Utopia\Query\Builder\Condition;
use Utopia\Query\Hook\Filter;
class SoftDeleteHook implements Filter
{
public function filter(string $table): Condition
{
return new Condition('deleted_at IS NULL');
}
}
use Utopia\Query\Builder\Condition;
use Utopia\Query\Builder\JoinType;
use Utopia\Query\Hook\Join\Filter as JoinFilter;
use Utopia\Query\Hook\Join\Placement;
class ActiveJoinFilter implements JoinFilter
{
public function filterJoin(string $table, JoinType $joinType): ?Condition
{
return new Condition(
'active = ?',
[1],
match ($joinType) {
JoinType::Left, JoinType::Right => Placement::On,
default => Placement::Where,
},
);
}
}
use Utopia\Query\Hook\Write;
class AuditHook implements Write
{
public function decorateRow(array $row, array $metadata = []): array { /* ... */ }
public function afterCreate(string $table, array $metadata, mixed $context): void { /* ... */ }
public function afterUpdate(string $table, array $metadata, mixed $context): void { /* ... */ }
public function afterBatchUpdate(string $table, array $updateData, array $metadata, mixed $context): void { /* ... */ }
public function afterDelete(string $table, array $ids, mixed $context): void { /* ... */ }
}
// Filtering
$result = (new Builder())
->from('products')
->filterJsonContains('tags', 'sale')
->filterJsonPath('metadata', 'color', '=', 'red')
->build();
// WHERE JSON_CONTAINS(`tags`, ?) AND JSON_EXTRACT(`metadata`, '$.color') = ?
// Mutations (in UPDATE) — combine with set() or setRaw() as needed
$result = (new Builder())
->from('products')
->filter([Query::equal('id', [1])])
->setJsonAppend('tags', ['new-tag'])
->update();
// Set a JSON path to a typed value — JSON_SET on MySQL, jsonb_set on PostgreSQL, json_set on SQLite
$result = (new Builder())
->from('products')
->filter([Query::equal('id', [1])])
->setJsonPath('metadata', '$.level', 42)
->update();
$result = (new Builder())
->from('users')
->hint('NO_INDEX_MERGE(users)')
->maxExecutionTime(5000)
->build();
// SELECT /*+ NO_INDEX_MERGE(users) max_execution_time(5000) */ * FROM `users`
$result = (new Builder())
->from('articles')
->filter([Query::search('content', 'hello world')])
->build();
// WHERE MATCH(`content`) AGAINST(? IN BOOLEAN MODE)
$result = (new Builder())
->from('events')
->distinctOn(['user_id'])
->select(['user_id', 'event_type', 'created_at'])
->sortDesc('created_at')
->build();
// SELECT DISTINCT ON ("user_id") "user_id", "event_type", "created_at"
// FROM "events" ORDER BY "created_at" DESC
$result = (new Builder())
->from('orders')
->selectAggregateFilter('COUNT(*)', 'status = ?', 'active_count', ['active'])
->selectAggregateFilter('SUM("amount")', 'status = ?', 'active_total', ['active'])
->build();
// SELECT COUNT(*) FILTER (WHERE status = ?) AS "active_count",
// SUM("amount") FILTER (WHERE status = ?) AS "active_total"
// FROM "orders"
$result = (new Builder())
->from('salaries')
->arrayAgg('name', 'all_names')
->percentileCont(0.5, 'salary', 'median_salary')
->percentileDisc(0.9, 'salary', 'p90_salary')
->mode('city', 'top_city')
->boolAnd('is_active', 'all_active')
->boolOr('is_admin', 'any_admin')
->every('is_verified', 'all_verified')
->build();
// mode() emits `mode() WITHIN GROUP (ORDER BY "city") AS "top_city"` — returns the most
// frequent value in the column (ties broken arbitrarily).
$source = (new Builder())->from('staging');
$result = (new Builder())
->mergeInto('target')
->using($source, 's')
->on('"target"."id" = "s"."id"')
->whenMatched('UPDATE SET "name" = "s"."name"')
->whenNotMatched('INSERT ("id", "name") VALUES ("s"."id", "s"."name")')
->executeMerge();
// UPDATE ... FROM
$result = (new Builder())
->from('users')
->set(['status' => 'premium'])
->updateFrom('orders', 'o')
->updateFromWhere('"users"."id" = "o"."user_id"')
->update();
// DELETE ... USING — PostgreSQL semantics differ from MySQL's deleteJoin
$result = (new Builder())
->from('users')
->deleteUsing('old_users', '"users"."id" = "old_users"."id"')
->delete();
$result = (new Builder())
->from('large_table')
->tablesample(10.0, 'BERNOULLI')
->count('*', 'approx')
->build();
// SELECT COUNT(*) AS "approx" FROM "large_table" TABLESAMPLE BERNOULLI (10)
use Utopia\Query\Builder\SQLite as Builder;
use Utopia\Query\Builder\ClickHouse as Builder;
$result = (new Builder())
->from('events')
->final()
->build();
// SELECT * FROM `events` FINAL
$result = (new Builder())
->from('events')
->sample(0.1)
->count('*', 'approx_total')
->build();
// SELECT COUNT(*) AS `approx_total` FROM `events` SAMPLE 0.1
$result = (new Builder())
->from('events')
->prewhere([Query::equal('event_type', ['click'])])
->filter([Query::greaterThan('count', 5)])
->build();
// SELECT * FROM `events` PREWHERE `event_type` IN (?) WHERE `count` > ?
$result = (new Builder())
->from('events')
->settings(['max_threads' => '4', 'optimize_read_in_order' => '1'])
->build();
// SELECT * FROM `events` SETTINGS max_threads=4, optimize_read_in_order=1
$result = (new Builder())
->from('events')
->select(['user_id', 'event_type'])
->limitBy(3, ['user_id'])
->build();
// SELECT `user_id`, `event_type` FROM `events` LIMIT 3 BY `user_id`
$result = (new Builder())
->from('events')
->select(['name'])
->arrayJoin('tags', 'tag')
->build();
// SELECT `name`, `tags` AS `tag` FROM `events` ARRAY JOIN `tags` AS `tag`
// LEFT variant preserves rows with empty arrays
$result = (new Builder())
->from('events')
->leftArrayJoin('tags', 'tag')
->build();
use Utopia\Query\Builder\ClickHouse\AsofOperator;
// For each trade, find the most recent quote with the same symbol
$result = (new Builder())
->from('trades', 't')
->select(['t.symbol', 't.ts', 't.price', 'q.bid'])
->asofJoin(
table: 'quotes',
equiPairs: ['t.symbol' => 'q.symbol'],
leftInequality: 't.ts',
operator: AsofOperator::GreaterThanEqual,
rightInequality: 'q.ts',
alias: 'q',
)
->sortAsc('t.ts')
->build();
// SELECT `t`.`symbol`, `t`.`ts`, `t`.`price`, `q`.`bid` FROM `trades` AS `t`
// ASOF JOIN `quotes` AS `q`
// ON `t`.`symbol` = `q`.`symbol` AND `t`.`ts` >= `q`.`ts`
// ORDER BY `t`.`ts` ASC
$result = (new Builder())
->from('daily_stats')
->select(['date', 'count'])
->orderWithFill('date', 'ASC', from: '2024-01-01', to: '2024-01-31', step: 1)
->build();
// SELECT `date`, `count` FROM `daily_stats` ORDER BY `date` ASC WITH FILL FROM '2024-01-01' TO '2024-01-31' STEP 1
$result = (new Builder())
->from('events')
->selectRaw('toStartOfHour(`time`) AS `bucket`')
->count('*', 'cnt')
->groupByTimeBucket('time', '1h')
->orderByRaw('`bucket` ASC')
->build();
// SELECT COUNT(*) AS `cnt`, toStartOfHour(`time`) AS `bucket`
// FROM `events`
// GROUP BY toStartOfHour(`time`)
// ORDER BY `bucket` ASC
$result = (new Builder())
->useNamedBindings()
->withParamTypes([
'time' => 'DateTime64(3)',
'tenant' => 'String',
'value' => 'Int64',
])
->from('events')
->filter([
Query::greaterThan('time', '2024-01-01 00:00:00'),
Query::equal('tenant', ['acme']),
Query::lessThanEqual('value', 100),
])
->build();
// SELECT * FROM `events`
// WHERE `time` > {param0:DateTime64(3)}
// AND `tenant` IN ({param1:String})
// AND `value` <= {param2:Int64}
$result->namedBindings;
// ['param0' => '2024-01-01 00:00:00', 'param1' => 'acme', 'param2' => 100]
$result = (new Builder())
->from('events')
->set(['status' => 'archived'])
->filter([Query::lessThan('created_at', '2024-01-01')])
->update();
// ALTER TABLE `events` UPDATE `status` = ? WHERE `created_at` < ?
// Lightweight (default) — pair with `lightweight_deletes_sync = 0` for async
$result = (new Builder())
->from('audit_log')
->settings(['lightweight_deletes_sync' => '0'])
->filter([Query::lessThan('time', '2024-01-01 00:00:00')])
->delete();
// DELETE FROM `audit_log` WHERE `time` < ? SETTINGS lightweight_deletes_sync=0
// Mutation — opt in. Pair with `mutations_sync = 0` for async
$result = (new Builder())
->from('audit_log')
->deleteMode(Builder::DELETE_MODE_MUTATION)
->settings(['mutations_sync' => '0'])
->filter([Query::lessThan('time', '2024-01-01 00:00:00')])
->delete();
// ALTER TABLE `audit_log` DELETE WHERE `time` < ? SETTINGS mutations_sync=0
use Utopia\Query\Builder\MongoDB as Builder;
$result = (new Builder())
->from('users')
->filter([
Query::equal('status', ['active']),
Query::greaterThan('age', 18),
])
->sortAsc('name')
->limit(25)
->build();
// Generates a find operation with filter, sort, limit, and projection
php
use Utopia\Query\Builder\MongoDB as Builder;
$query = (new Builder())->from('users')->filter([Query::equal('active', [true])]);
$result = $schema->createView('active_users', $query);
php
use Utopia\Query\Compiler;
use Utopia\Query\Query;
use Utopia\Query\Method;
class MyCompiler implements Compiler
{
public function compileFilter(Query $query): string { /* ... */ }
public function compileOrder(Query $query): string { /* ... */ }
public function compileLimit(Query $query): string { /* ... */ }
public function compileOffset(Query $query): string { /* ... */ }
public function compileSelect(Query $query): string { /* ... */ }
public function compileCursor(Query $query): string { /* ... */ }
public function compileAggregate(Query $query): string { /* ... */ }
public function compileGroupBy(Query $query): string { /* ... */ }
public function compileJoin(Query $query): string { /* ... */ }
}
Loading please wait ...
Before you can download the PHP files, the dependencies should be resolved. This can take some minutes. Please be patient.