1

I'm using Feedme to add entries via an external JSON feed. There was an issue with matching duplicates and now need to remove >4000 entries from the site.

To avoid further issues would ideally like to build this into the feedme script we are running but the built-in delete method times out due to memory issues. I've looked at other ways we can achieve this, eg by deleting via the entries model:

$entriesRecord = EntryRecord::model()->findAllByAttributes(array('sectionId' => $sectionId));
$entriesRecord->delete();

But this is also timing out. Mike Pepper pointed me in the direction of the a&m command add-on at https://github.com/am-impact/amcommand but this is also struggling. Is the only way to deal with such large quantities of entries to do this directly via SQL?

Help!

  • 1
    Okay - been round circles and several different methods to achieve this. Unfortunately with this amount of entries, working directly with the craft model or API just timed out. To clean out the database I had to manually clear out entries from the DB via the following code: gist.github.com/cole007/c1c20774510d6a6b5ae5 Note that you will need to execute the DELETE FROM craft_entries query last – Cole Henley Jan 14 '16 at 13:13
7

Records are definitely not the way to go with dealing with large amounts of data sets in the database. There is just too much overheard associated with them.

Manually running SQL (as you've discovered) is an option, but if you wanted to do this from PHP, you could still use QueryBuilder.

$elementIds = craft()->db->createCommand()
            ->select('id')
            ->from('entries')
            ->where('sectionId = :sectionId', array(':sectionId' => 18))
            ->queryColumn();

$rowsAffected = craft()->db->createCommand()
            ->delete(
                'elements',
                array('in', 'id', $elementIds)
             );

The MySQL cascading deletes setup on the foreign keys should take care of cleaning up the necessary rows in the remaining tables automatically.

| improve this answer | |
  • Hi Brad - sorry for delay in following this up. Is there a typo in your code sample on the delete() method? Seems to be throwing errors here. – Cole Henley Feb 3 '16 at 10:33
  • 1
    @ColeHenley it should have a comma after 'elements' and before the array() part. If that works edit the answer as such. – Josh Angell Feb 3 '16 at 11:10
  • 1
    Thanks @JoshAngell - caught that one but couldn't for the life of me see why it wasn't working. Turned out to be a typo - was using createComment rather than createCommand :D – Cole Henley Feb 3 '16 at 11:28
1

I updated the queries @ColeHenley posted. This should work with Craft 3.x:

DELETE elements FROM ut_entries AS entries, ut_elements_sites AS elements WHERE entries.id = elements.elementId AND entries.sectionId IN(1, 2, 3, 8, 9, 10, 11);
DELETE relations FROM ut_entries AS entries, ut_relations AS relations WHERE entries.id = relations.sourceId AND entries.sectionId IN(1, 2, 3, 8, 9, 10, 11);
DELETE revisions FROM ut_entries AS entries, ut_revisions AS revisions WHERE entries.id = revisions.sourceId AND entries.sectionId IN(1, 2, 3, 8, 9, 10, 11);
DELETE content FROM ut_entries AS entries, ut_content AS content WHERE entries.id = content.elementId AND entries.sectionId IN(1, 2, 3, 8, 9, 10, 11);
DELETE drafts FROM ut_entries AS entries, ut_drafts AS drafts WHERE entries.id = drafts.sourceId AND entries.sectionId IN(1, 2, 3, 8, 9, 10, 11);
DELETE search FROM ut_entries AS entries, ut_searchindex AS search WHERE entries.id = search.elementId AND entries.sectionId IN(1, 2, 3, 8, 9, 10, 11);
DELETE elements FROM ut_entries AS entries, ut_structureelements AS elements WHERE entries.id = elements.elementId AND entries.sectionId IN(1, 2, 3, 8, 9, 10, 11);
DELETE attributes FROM ut_entries AS entries, ut_changedattributes AS attributes WHERE entries.id = attributes.elementId AND entries.sectionId IN(1, 2, 3, 8, 9, 10, 11);
DELETE fields FROM ut_entries AS entries, ut_changedfields AS fields WHERE entries.id = fields.elementId AND entries.sectionId IN(1, 2, 3, 8, 9, 10, 11);
DELETE relations FROM ut_entries AS entries, ut_relations AS relations WHERE (entries.id = relations.sourceId or entries.id = relations.targetId) AND entries.sectionId IN(1, 2, 3, 8, 9, 10, 11);
DELETE elements FROM ut_entries AS entries, ut_elements AS elements WHERE entries.id = elements.id AND entries.sectionId IN(1, 2, 3, 8, 9, 10, 11);
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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