# Cycle - сравние кода и генерируемого SQL

Включение логирования не вызывает никаких проблем. Можем писать их куда хотим. Логгер должен соответствовать [PSR Logger Interface](https://www.php-fig.org/psr/psr-3/#3-psrlogloggerinterface):

$config = Bootstrap\Config::forDatabase(
    'mysql:host=palace-db.x340.org;dbname=repetitors',
    'user',
    's3cretpassw0rd')
->withLogger(new testLogger()); // PSR Logger

$orm = Bootstrap\Bootstrap::fromConfig($config);

Сделал тестовую entity `Vuz` на нашей таблице **ri_vuzs**. Запустим и посмотрим, что будет в логах.

$ php -f test.php

-- Первый запуск - получаем информацию о таблицах, полях и индексах
SELECT COUNT(*) 
FROM `information_schema`.`tables` 
WHERE `table_schema` = 'repetitors' 
AND `table_name` = 'ri_vuzs'

SHOW FULL COLUMNS FROM `ri_vuzs`

SHOW INDEXES FROM `ri_vuzs`

SELECT * 
FROM `information_schema`.`referential_constraints`
WHERE `constraint_schema` = 'repetitors' 
AND `table_name` = 'ri_vuzs'

SHOW INDEXES FROM `ri_vuzs`

SHOW TABLE STATUS WHERE `Name` = 'ri_vuzs'

$orm
->getRepository(Vuz::class)
->findByPK(100);

SELECT `vuz`.`id` AS `c0`, `vuz`.`name` AS `c1`, `vuz`.`alt` AS `c2`
FROM `ri_vuzs` AS `vuz`
WHERE `vuz`.`id` = 100
LIMIT 1

$orm
->getRepository(Vuz::class)
->findAll()

SELECT `vuz`.`id` AS `c0`, `vuz`.`name` AS `c1`, `vuz`.`alt` AS `c2`
FROM `ri_vuzs` AS `vuz`

$orm
->getRepository(Vuz::class)
->findAll(['alt' => 'mgafk']);

SELECT `vuz`.`id` AS `c0`, `vuz`.`name` AS `c1`, `vuz`.`alt` AS `c2`
FROM `ri_vuzs` AS `vuz`
WHERE `vuz`.`alt` = 'mgafk'

$orm
->getRepository(Vuz::class)
->findAll(['name' => 'Ассоциация Профессионалов Фитнеса'])

SELECT `vuz`.`id` AS `c0`, `vuz`.`name` AS `c1`, `vuz`.`alt` AS `c2`
FROM `ri_vuzs` AS `vuz`
WHERE `vuz`.`name` = 'Ассоциация Профессионалов Фитнеса'

$orm
->getRepository(Vuz::class)
->select()
->where('alt', 'mgafk')
->orWhere('alt', 'pifk')
->fetchAll();

SELECT `vuz`.`id` AS `c0`, `vuz`.`name` AS `c1`, `vuz`.`alt` AS `c2`
FROM `ri_vuzs` AS `vuz`
WHERE `vuz`.`alt` = 'mgafk' OR `vuz`.`alt` = 'pifk'

Немного примеров посложнее (из доков Cycle ORM):

$select
->distinct()
->where('balance', '>', new Expression($qb->resolve('orders.total')))
->andWhere('orders.status', 'pending');

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'

Как можно сделать low level запросы:

$query = $select->buildQuery();

$query
->columns('id', new Expression('SUM(balance)'))
->groupBy('id');

$query->fetchAll();

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

Пример с вложенными селектами:

$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'))
);

SELECT
...
FROM "users" AS "user"
  WHERE (
    SELECT
    SUM("order"."total")
    FROM "orders" AS "order"
    WHERE "order"."user_id" = "user"."id"
  ) >= "user"."balance"

$users->load('posts', [
    'where' => function($qb) {
        $qb->distinct()->where('comments.id', '!=', null);
    }
]);

SELECT
"user"."id" AS "c0", "user"."email" AS "c1", "user"."balance" AS "c2"
FROM "user" AS "user"

SELECT DISTINCT
"user_posts"."id" AS "c0", "user_posts"."user_id" AS "c1", "user_posts"."title" AS "c2"
FROM "post" AS "user_posts"
INNER JOIN "comment" AS "user_posts_comments"
ON "user_posts_comments"."post_id" = "user_posts"."id"
WHERE "user_posts"."user_id" IN (1, 2) 
AND ("user_posts_comments"."id" IS NOT NULL)