Migration_20221028_105818_App.php
3 years ago
Migration_20230109_144830.php
3 years ago
Migration_20230131_121621.php
3 years ago
Migration_20230419_080000.php
3 years ago
Migration_20230425_211517.php
3 years ago
Migration_20230712_180341.php
3 years ago
Migration_20230803_200413_App.php
2 years ago
Migration_20230825_093531_App.php
1 year ago
Migration_20231128_120355_App.php
1 year ago
Migration_20240202_130053_App.php
2 years ago
Migration_20240207_105912_App.php
1 year ago
Migration_20240322_110443_App.php
2 years ago
Migration_20240730_212419_App.php
2 years ago
Migration_20241015_105511_App.php
1 year ago
Migration_20241128_114257_App.php
1 year ago
Migration_20250120_094614_App.php
1 year ago
Migration_20250501_114655_App.php
11 months ago
Migration_20260421_155908_App.php
3 months ago
Migration_20260515_120000_App.php
3 months ago
Migration_20260623_120000_App.php
1 month ago
Migration_20260805_120000_App.php
1 week ago
index.php
3 years ago
Migration_20250501_114655_App.php
101 lines
| 1 | <?php declare(strict_types = 1); |
| 2 | |
| 3 | namespace MailPoet\Migrations\App; |
| 4 | |
| 5 | if (!defined('ABSPATH')) exit; |
| 6 | |
| 7 | |
| 8 | use MailPoet\Doctrine\WPDB\Connection as WPDBConnection; |
| 9 | use MailPoet\Entities\StatisticsClickEntity; |
| 10 | use MailPoet\Entities\StatisticsUnsubscribeEntity; |
| 11 | use MailPoet\Entities\SubscriberEntity; |
| 12 | use MailPoet\Entities\SubscriberSegmentEntity; |
| 13 | use MailPoet\Migrator\AppMigration; |
| 14 | use MailPoetVendor\Doctrine\DBAL\Connection; |
| 15 | |
| 16 | class Migration_20250501_114655_App extends AppMigration { |
| 17 | private const DB_QUERY_CHUNK_SIZE = 1000; |
| 18 | |
| 19 | public function run(): void { |
| 20 | $isSQLite = WPDBConnection::isSQLite(); |
| 21 | if ($isSQLite) { |
| 22 | // SQLite (used in WP Studio) does not support the TIMESTAMPDIFF function, so we skip the migration |
| 23 | return; |
| 24 | } |
| 25 | |
| 26 | $clicksStatsTable = $this->entityManager->getClassMetadata(StatisticsClickEntity::class)->getTableName(); |
| 27 | $unsubscribeStatsTable = $this->entityManager->getClassMetadata(StatisticsUnsubscribeEntity::class)->getTableName(); |
| 28 | $subscribersTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName(); |
| 29 | $subscribersSegmentsTable = $this->entityManager->getClassMetadata(SubscriberSegmentEntity::class)->getTableName(); |
| 30 | |
| 31 | // First get all subscriber IDs that were unsubscribed by a bot |
| 32 | $subscriberIds = $this->entityManager->getConnection()->executeQuery( |
| 33 | "SELECT DISTINCT mp_unsub.subscriber_id |
| 34 | FROM {$unsubscribeStatsTable} AS mp_unsub |
| 35 | LEFT JOIN {$clicksStatsTable} AS mp_click |
| 36 | ON mp_unsub.newsletter_id = mp_click.newsletter_id |
| 37 | AND mp_unsub.subscriber_id = mp_click.subscriber_id |
| 38 | AND ABS(TIMESTAMPDIFF(SECOND, mp_click.created_at, mp_unsub.created_at)) <= 4 |
| 39 | WHERE mp_unsub.created_at > '2025-03-01' |
| 40 | GROUP BY mp_unsub.subscriber_id |
| 41 | HAVING COUNT(mp_click.id) >= 3" |
| 42 | )->fetchFirstColumn(); |
| 43 | |
| 44 | if (empty($subscriberIds)) { |
| 45 | return; |
| 46 | } |
| 47 | |
| 48 | // Process subscriber IDs in chunks |
| 49 | foreach (array_chunk($subscriberIds, self::DB_QUERY_CHUNK_SIZE) as $chunk) { |
| 50 | $this->processSubscriberChunk( |
| 51 | $chunk, |
| 52 | $subscribersTable, |
| 53 | $subscribersSegmentsTable, |
| 54 | $unsubscribeStatsTable |
| 55 | ); |
| 56 | } |
| 57 | } |
| 58 | |
| 59 | private function processSubscriberChunk( |
| 60 | array $subscriberIds, |
| 61 | string $subscribersTable, |
| 62 | string $subscribersSegmentsTable, |
| 63 | string $unsubscribeStatsTable |
| 64 | ): void { |
| 65 | // Switch the global subscriber status to subscribed |
| 66 | $this->entityManager->getConnection()->executeQuery( |
| 67 | "UPDATE {$subscribersTable} |
| 68 | SET status = :subscribedStatus |
| 69 | WHERE id IN (:subscriberIds) |
| 70 | AND status = :unsubscribedStatus", |
| 71 | [ |
| 72 | 'subscribedStatus' => SubscriberEntity::STATUS_SUBSCRIBED, |
| 73 | 'unsubscribedStatus' => SubscriberEntity::STATUS_UNSUBSCRIBED, |
| 74 | 'subscriberIds' => $subscriberIds, |
| 75 | ], |
| 76 | [ |
| 77 | 'subscriberIds' => Connection::PARAM_INT_ARRAY, |
| 78 | ] |
| 79 | ); |
| 80 | |
| 81 | // Update the subscriber_segment table, find rows that were unsubscribed at the same time |
| 82 | $this->entityManager->getConnection()->executeQuery( |
| 83 | "UPDATE {$subscribersSegmentsTable} AS mp_subseg |
| 84 | JOIN {$unsubscribeStatsTable} AS mp_unsub |
| 85 | ON mp_subseg.subscriber_id = mp_unsub.subscriber_id |
| 86 | AND ABS(TIMESTAMPDIFF(SECOND, mp_subseg.updated_at, mp_unsub.created_at)) <= 2 |
| 87 | SET mp_subseg.status = :subscribedStatus |
| 88 | WHERE mp_subseg.status = :unsubscribedStatus |
| 89 | AND mp_subseg.subscriber_id IN (:subscriberIds)", |
| 90 | [ |
| 91 | 'subscribedStatus' => SubscriberEntity::STATUS_SUBSCRIBED, |
| 92 | 'unsubscribedStatus' => SubscriberEntity::STATUS_UNSUBSCRIBED, |
| 93 | 'subscriberIds' => $subscriberIds, |
| 94 | ], |
| 95 | [ |
| 96 | 'subscriberIds' => Connection::PARAM_INT_ARRAY, |
| 97 | ] |
| 98 | ); |
| 99 | } |
| 100 | } |
| 101 |