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 Cycle\Database\Injection\Expression
object:
$select->where('time_created', '>', new \Cycle\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.
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
$select->where(
'credits',
'>',
new \Cycle\Database\Injection\Expression($qb->resolve('balance'))
);
Such a query will produce similar SQL:
SELECT
...
FROM "users" AS "user" WHERE
"user"."credits" > "user"."balance"
You can also resolve names of related entities by using the entity path:
$select->distinct()
->where('balance', '>', new \Cycle\Database\Injection\Expression($qb->resolve('orders.total')))
->andWhere('orders.status', 'pending');
Example SQL:
SELECT DISTINCT
...
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 yourSelect
object to create more complex conditions.
If you want to run complex selection or select only particular columns you can modify the underlying query directly:
$query = $select->buildQuery();
$query
->columns('id', new \Cycle\Database\Injection\Expression('SUM(balance)'))
->groupBy('id');
print_r($query->fetchAll());
The produced query will look like:
SELECT
"id", SUM("balance")
FROM "users" AS "user"
GROUP BY "id"
Use the
resolve
method to obtain fully qualified column names.
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 Cycle\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') .')'));
$users->where(
$sumOrders,
'>=',
new Expression($users->getBuilder()->resolve('balance'))
);
The produced SQL will look like:
SELECT
...
FROM "users" AS "user"
WHERE (
SELECT
SUM("order"."total")
FROM "orders" AS "order"
WHERE "order"."user_id" = "user"."id"
) >= "user"."balance"
Both entities must be located within one physical database.