Script to delete duplicate resource (based on alias)

I’m searching a way to write a snippet to delete duplicate (alias) resources.
The script should find resouces with same alias and keep on of them.

Here is some code that should do this:

$stmt = $modx->prepare("SELECT id, alias FROM `modx_site_content` c1 WHERE EXISTS (SELECT * FROM `modx_site_content` c2 where c1.alias = c2.alias and <> AND deleted = 0) AND deleted = 0 ORDER BY alias");
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

$count_deleted = 0;
$deleted_ids = array();
$last_alias = '';
foreach ($rows as $row) {
    if (!empty($row['alias']) && $row['alias'] == $last_alias){
        $resource = $modx->getObject('modResource', $row['id']);
        if ($resource) {
            $resource->set('deleted', 1); //mark as deleted
            //$resource->remove(); //or delete resource completely
            $deleted_ids[] = $row['id'];
    } else {
        $last_alias = $row['alias'];
return $count_deleted . " resources deleted: " . implode(",", $deleted_ids);

If you care about, which one of the resources (with the same alias) you want to keep, then you’ll have to adjust the code.

Note that if you use alias paths, it’s better to check the uri instead of the alias because you might otherwise find too many matches and delete stuff you don’t want to.

Although having written that, I’m not 100% sure if the duplicate resource will actually have an URI set because modx tries to prevent that…

FYI, SiteCheck will report duplicate aliases in the same context (along with a lot of other things), and will provide links to them so you can select which ones to delete. It might be a safer way to proceed.