Deleting all entries from a Section

Question

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?

Answer 1

Records are definitely not the way to go with dealing with large amounts of data sets in the database. There is just too much overhead 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.

Answer 2

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);