Complex Queries

You can use the the query builder to compose more complex queries and expressions.


It is possible to inject custom SQL logic into the query using Spiral\Database\Injection\Expression object:

$select->where('time_created', '>', new \Spiral\Database\Injection\Expression("NOW()"));

You can use expressions in place of operators or column names. Please note that, since a column name might not necessarily be identical to the actual property name, you must resolve its identity first.

Name Resolver

To resolve the name of column you must gain access to QueryBuilder instance available through the getBuilder method of the Select object:

$qb = $select->getBuilder();

print_r($qb->resolve('id')); // table.column_name

You can use this identification inside your expressions:

$qb = $select->getBuilder();

// to compare 2 columns
  new \Spiral\Database\Injection\Expression($qb->resolve('balance'))

Such a query will produce similar SQL:

FROM "users" AS "user" WHERE
  "user"."credits" > "user"."balance"

You can also resolve names of related entities by using the entity path:

    ->where('balance', '>', new \Spiral\Database\Injection\Expression($qb->resolve('')))
    ->andWhere('orders.status', 'pending');

Example SQL:

FROM "users" AS "user"
INNER JOIN "orders" AS "user_orders"
    ON "user_orders"."user_id" = "user"."id"
WHERE "user"."balance" > "user_orders"."total" AND "user_orders"."status" = 'pending'

You can also use the method groupBy on your Select object to create more complex conditions.

Low level queries

If you want to run complex selection or select only particular columns you can modify the underlying query directly:

$query = $select->buildQuery();

    ->columns('id', new \Spiral\Database\Injection\Expression('SUM(balance)'))


The produced query will look like:

  "id", SUM("balance")
FROM "users" AS "user"
  GROUP BY "id"

Use the resolve method to obtain fully qualified column names.

Injecting Queries

It is possible to inject a query into another query. In this case, you must obtain an instance of the entity query first. It can be done by calling the method buildQuery() of Select object.

For example:

use Spiral\Database\Injection\Expression;

$users = $orm->getRepository(User::class)->select();
$orders = $orm->getRepository(Order::class)->select();

// only orders of specific user (fallback to native column name)
$sumOrders = $orders->where('user_id', new Expression($users->getBuilder()->resolve('id')))->buildQuery();

$sumOrders->columns(new Expression('SUM('. $orders->getBuilder()->resolve('total') .')'));

    new Expression($users->getBuilder()->resolve('balance'))

The produced SQL will look like:

FROM "users" AS "user"
    FROM "orders" AS "order"
    WHERE "order"."user_id" = "user"."id"
  ) >= "user"."balance"

Both entities must be located within one physical database.

Edit this page