2

I have a large import with over 5000 products. These product elements have a image field wich is a assets relation field. Looping over the products and setting the field as null and then save takes waaaayyy too long:

    foreach($products as $product) {
        $counter++;
        $product->setFieldValue('fieldProductMedia', NULL);

        Craft::$app->getElements()->saveElement($product, false, false, false);
    
        $message = $counter .'/' . $total;
        Console::stdout($message);
        Console::stdout(PHP_EOL);
    }

I tried using a query like so:

DELETE r FROM `relations` AS r INNER JOIN elements AS e ON r.sourceId = e.id WHERE r.fieldId = 131 AND (e.type LIKE '%Product%' OR e.type LIKE '%Variant%');

But this messes with the foreign key constraint. So I tried it another way but this causes key constraints again when re-importing the images:

        $relations = (new Query())
            ->select(['r.id'])
            ->from('relations AS r')
            ->innerJoin('elements AS e','r.sourceId = e.id')
            ->where(['r.fieldId' => '131'])
            ->andWhere("e.type LIKE '%Product%' OR e.type LIKE '%Variant%'")
            ->indexBy('id')
            ->all();

        $result = Craft::$app->db->createCommand()
            ->delete(Table::RELATIONS, ['id' => array_keys($relations)])
            ->execute();

Any ideas?

7
  • Why does it matter that it takes some time to loop over the elements? Is it a one-time operation or does it need to happen regularly? Can it be done in the background?
    – MoritzLost
    Commented Mar 1, 2022 at 15:20
  • It's related to another fun issue I'm having with memory that keeps increasing after each save(). I think it's a bug in Craft: 4454/5729 - 1800.85 mb Error: Allowed memory size of 2147483648 bytes exhausted (tried to allocate 135168 bytes)
    – bartpulse
    Commented Mar 1, 2022 at 15:35
  • Maybe your code is just badly optimized? Based on your example, it looks like you're loading 5000+ entries into one huge array and then iterating through it? No wonder you're having memory problems. Instead, try to load the entries one by one so that only one entry is in memory at a time. Will probably solve your problem here as well. You can use $entryQuery->ids() to get an array of entry IDs, then iterate through that and load each entry in turn.
    – MoritzLost
    Commented Mar 1, 2022 at 15:48
  • Also, if you think you're looking at a memory leak in Craft, try to track it down to its source and open an issue on Github. Better to address the source of the problem than to put a bandaid on it.
    – MoritzLost
    Commented Mar 1, 2022 at 15:48
  • 1
    There is also the $productQuery->each($n) method that will batch the DB operation and Element population into $n chunks to reduce the footprint of the whole operation. As long as you don’t memoize anything outside the loop, PHP will garbage collect for you! Commented Mar 1, 2022 at 15:52

1 Answer 1

2

I haven't actually tested this, but going through the Relations service instead of the Elements service might avoid the issues you're facing. Using the each() method when looping over the element query results (as mentioned by August in the comments above) is also a good idea.

$fieldProductMediaField = Craft::$app->getFields()->getFieldByHandle('fieldProductMedia');

foreach ($query->each() as $product) {
    Craft::$app->getRelations()->saveRelations($fieldProductMediaField, $product, []);
    ...
}
1
  • 1
    That's working. Really great, thanks!
    – bartpulse
    Commented Mar 2, 2022 at 8:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.