Remove dependent data

m160512_055401_users_delete.php

<?php

use yii\db\Migration;

class m160512_055401_users_delete extends Migration
{
    public function safeUp()
    {
        $sql = <<<SQL
SET @acolinEmail = '[email protected]';
SET @coachId = (SELECT id FROM user_account WHERE email = @acolinEmail);

DROP TABLE IF EXISTS coachGroups, notCoachGroup, coachUsers, usersForDelete,
                      activityUploadForDelete, activityForDelete, eventCodeFaildForDelete, groupForDelete, groupStudentForDelete, userProfileForDelete;

CREATE TEMPORARY TABLE IF NOT EXISTS coachGroups AS (SELECT id FROM `group` WHERE `group`.ownerId = @coachId);
CREATE TEMPORARY TABLE IF NOT EXISTS notCoachGroup AS (SELECT id FROM `group` WHERE `group`.ownerId != @coachId);
CREATE TEMPORARY TABLE IF NOT EXISTS coachUsers AS (SELECT gs.studentId FROM group_student gs WHERE gs.groupId IN (SELECT * FROM coachGroups));
CREATE TEMPORARY TABLE IF NOT EXISTS usersForDelete AS (SELECT id FROM user_account WHERE id NOT IN (SELECT * FROM coachUsers) AND id NOT IN (SELECT @coachId UNION SELECT 1 UNION SELECT 2 UNION SELECT 95)); #1 - root, 2 - student, 95 - coach

CREATE TEMPORARY TABLE IF NOT EXISTS activityUploadForDelete AS (SELECT id FROM activity_upload WHERE coachId IN (SELECT id FROM usersForDelete));
CREATE TEMPORARY TABLE IF NOT EXISTS activityForDelete AS (SELECT id FROM activity WHERE activityUploadId IN (SELECT id FROM activityUploadForDelete) OR studentId IN (SELECT id FROM usersForDelete) OR groupId IN (SELECT id FROM notCoachGroup));
CREATE TEMPORARY TABLE IF NOT EXISTS eventCodeFaildForDelete AS (SELECT id FROM event_code_failed_attempt ecfa WHERE ecfa.userId IN (SELECT id FROM usersForDelete));
CREATE TEMPORARY TABLE IF NOT EXISTS groupForDelete AS (SELECT id FROM `group` WHERE `group`.ownerId IN (SELECT id FROM usersForDelete));
CREATE TEMPORARY TABLE IF NOT EXISTS groupStudentForDelete AS (SELECT id FROM group_student WHERE studentId IN (SELECT id FROM usersForDelete));
CREATE TEMPORARY TABLE IF NOT EXISTS userProfileForDelete AS (SELECT id FROM user_profile WHERE ownerId IN (SELECT id FROM usersForDelete));

DELETE FROM event_code_failed_attempt WHERE id IN (SELECT * FROM eventCodeFaildForDelete);
DELETE FROM activity WHERE id IN (SELECT * FROM activityForDelete);
DELETE FROM activity_upload WHERE id IN (SELECT * FROM activityUploadForDelete);
DELETE FROM group_student WHERE id IN (SELECT * FROM groupStudentForDelete);
DELETE FROM event_code_failed_attempt WHERE id IN (SELECT * FROM eventCodeFaildForDelete);
DELETE FROM `group` WHERE id IN (SELECT * FROM groupForDelete);
DELETE FROM user_profile WHERE id IN (SELECT * FROM userProfileForDelete);
DELETE FROM user_account WHERE id IN (SELECT * FROM usersForDelete);


DROP TABLE IF EXISTS coachGroups, notCoachGroup, coachUsers, usersForDelete,
                      activityUploadForDelete, activityForDelete, eventCodeFaildForDelete, groupForDelete, groupStudentForDelete, userProfileForDelete;
SQL;
        $this->execute($sql);
    }

    public function safeDown()
    {
        return;
    }
}

Требовалось удалить тестовые данные, которые создавал пользователь(заказчик) в продакшн базе а также мы сам(разработчики, кто работал над проектом), очень много таблиц завязано между собой ключами, было принято решение оформить всю процедуру в safe миграцию(в случае ошибки изменения бы откатились, т.к. safe миграция "оборачивается" в транзакцию). Некоторые моменты можно было реализовать с использованием JOIN конструкций, но решая задачу "в лоб" потребовалось меньше времени, и для данной ситуации выбранное решение является более надежным. Решение реализовано с использованием объединений(по строкам) и виртуальных таблиц.