From db381626d5fd8364b10b91af6c366d79c6bed11b Mon Sep 17 00:00:00 2001 From: fomichev Date: Wed, 22 Apr 2026 11:24:49 +0300 Subject: [PATCH] =?utf8?q?fix:=20=D1=8D=D0=BA=D1=81=D0=BF=D0=BE=D1=80?= =?utf8?q?=D1=82=20=E2=80=94=20=D0=BE=D0=B4=D0=B8=D0=BD=20JOIN-=D0=B7?= =?utf8?q?=D0=B0=D0=BF=D1=80=D0=BE=D1=81=20=D1=81=D0=BE=20=D1=81=D0=BA?= =?utf8?q?=D0=B0=D0=BB=D1=8F=D1=80=D0=B0=D0=BC=D0=B8=20=D0=B2=D0=BC=D0=B5?= =?utf8?q?=D1=81=D1=82=D0=BE=20AR=20+=20eager=20loading?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Убирает OOM: GUID-ы фильтруются отдельно, данные — одним SQL с STRING_AGG для маркировок. Никаких ActiveRecord-объектов в памяти. Co-Authored-By: Claude Sonnet 4.6 --- erp24/services/ProductMappingService.php | 112 ++++++++++++----------- 1 file changed, 61 insertions(+), 51 deletions(-) diff --git a/erp24/services/ProductMappingService.php b/erp24/services/ProductMappingService.php index 13e6702d..c6b2ef75 100644 --- a/erp24/services/ProductMappingService.php +++ b/erp24/services/ProductMappingService.php @@ -209,13 +209,11 @@ class ProductMappingService */ public function exportToXlsx(ProductMappingFilterForm $filters): string { - $query = $this->buildFilteredQuery($filters)->orderBy(['n.name' => SORT_ASC]); - - /** @var Products1cNomenclature[] $products */ - $products = $query->all(); - $mappingsByGuid = $this->loadMappingsForProducts( - array_map(static fn($p) => $p->id, $products) - ); + // Шаг 1: получаем отфильтрованные GUID-ы (только строки, не AR-объекты) + $guids = $this->buildFilteredQuery($filters) + ->select(['n.id']) + ->orderBy(['n.name' => SORT_ASC]) + ->column(); $runtimeDir = Yii::getAlias('@runtime'); if (!is_dir($runtimeDir)) { @@ -223,55 +221,67 @@ class ProductMappingService } $path = $runtimeDir . '/product-mapping-export-' . date('YmdHis') . '-' . uniqid() . '.csv'; - $fh = fopen($path, 'w'); - // BOM для корректного открытия в Excel - fwrite($fh, "\xEF\xBB\xBF"); + $fh = fopen($path, 'wb'); + if ($fh === false) { + throw new \RuntimeException('Не удалось создать файл экспорта: ' . $path); + } + + fwrite($fh, "\xEF\xBB\xBF"); // UTF-8 BOM для Excel fputcsv($fh, [ - 'GUID товара', - 'Название товара 1С', - 'Категория', - 'Подкатегория', - 'Вид', - 'Поставщик', - 'Название у поставщика', - 'Плантация', - 'Артикул', - 'Штрихкод', - 'Квант', - 'Маркировки (коды)', + 'GUID товара', 'Название товара 1С', 'Категория', 'Подкатегория', 'Вид', + 'Поставщик', 'Название у поставщика', 'Плантация', 'Артикул', 'Штрихкод', + 'Квант', 'Маркировки (коды)', ], ';'); - foreach ($products as $product) { - $mappings = $mappingsByGuid[$product->id] ?? []; - - if (empty($mappings)) { - fputcsv($fh, [ - $product->id, - $product->name, - $product->category ?? '', - $product->subcategory ?? '', - $product->species ?? '', - '', '', '', '', '', '', '', - ], ';'); - continue; - } - - foreach ($mappings as $mapping) { - $markingCodes = array_map(static fn($m) => $m->code, $mapping->markings); + if (!empty($guids)) { + // Шаг 2: один JOIN-запрос — скаляры, не объекты, STRING_AGG для маркировок + $rows = (new Query()) + ->select([ + 'guid' => 'n.id', + 'product_name' => 'n.name', + 'category' => "COALESCE(n.category, '')", + 'subcategory' => "COALESCE(n.subcategory, '')", + 'species' => "COALESCE(n.species, '')", + 'supplier_name' => "COALESCE(s.name, '')", + 'supplier_product_name'=> "COALESCE(pm.supplier_product_name, '')", + 'plantation_name' => "COALESCE(pl.name, '')", + 'article' => "COALESCE(pm.article, '')", + 'barcode' => "COALESCE(pm.barcode, '')", + 'quant' => 'COALESCE(pm.quant, 0)', + 'marking_codes' => new Expression( + "COALESCE(STRING_AGG(mk.code, ', ' ORDER BY mk.code), '')" + ), + ]) + ->from(['n' => 'products_1c_nomenclature']) + ->leftJoin(['pm' => '{{%erp24.product_mappings}}'], 'pm.product_guid = n.id') + ->leftJoin(['s' => '{{%erp24.suppliers}}'], 's.id = pm.supplier_id') + ->leftJoin(['pl' => '{{%erp24.plantations}}'], 'pl.id = pm.plantation_id') + ->leftJoin(['mm' => '{{%erp24.mapping_markings}}'], 'mm.mapping_id = pm.id') + ->leftJoin(['mk' => '{{%erp24.markings}}'], 'mk.id = mm.marking_id') + ->where(['n.id' => $guids]) + ->groupBy([ + 'n.id', 'n.name', 'n.category', 'n.subcategory', 'n.species', + 's.name', 'pm.id', 'pm.supplier_product_name', + 'pl.name', 'pm.article', 'pm.barcode', 'pm.quant', + ]) + ->orderBy(['n.name' => SORT_ASC, 'pm.id' => SORT_ASC]) + ->all(); + + foreach ($rows as $row) { fputcsv($fh, [ - $product->id, - $product->name, - $product->category ?? '', - $product->subcategory ?? '', - $product->species ?? '', - $mapping->supplier->name ?? '', - $mapping->supplier_product_name, - $mapping->plantation->name ?? '', - $mapping->article ?? '', - $mapping->barcode ?? '', - (int)$mapping->quant, - implode(', ', $markingCodes), + $row['guid'], + $row['product_name'], + $row['category'], + $row['subcategory'], + $row['species'], + $row['supplier_name'], + $row['supplier_product_name'], + $row['plantation_name'], + $row['article'], + $row['barcode'], + $row['quant'], + $row['marking_codes'], ], ';'); } } -- 2.39.5