This PHP Script for Moodle that will set as "deleted" all messages for a given user and date.

clear-messages-script.php

<?php

/**
 * This PHP Script for Moodle that will set as "deleted" all messages for a given user and date.
 * It will insert a row in message_user_actions for every meesage to be deleted.
 * Messages will stil exist, but won't display in Messages page view.
 */
 
// get moodle libs
require_once('config.php');

// set ID AND DATE
define('USERID', 4);
define('MAX_DATE', 1669908665); // 2022-12-01

// title
echo "<h3>Clear Moodle User Messages</h3>";
// get messages
$messages = get_messages_by_userid(USERID, MAX_DATE);
// count
printVar(count($messages).' messages');

// get all messageid for given conversationid
// and execute INSERT query for each message
for($i=0; $i<525; $i++) {
    // msg
    $msg = array_shift($messages);
    printVar($msg);
    // set id
    $conversationid = $msg->conversationid;
    printVar('conversationId = '.$conversationid);
    // get messages
    $submsgs = $DB->get_records('messages', array('conversationid' => $conversationid));
    // printVar($submsgs);
    foreach($submsgs as $subm) {
        // check if exists
        $exist = $DB->get_records('message_user_actions', array('userid'=>$subm->useridfrom, 'messageid'=>$subm->id,'action'=>2));
        printVar('Exists: '.count($exist));
        if(count($exist)==0) {
            $sql = "INSERT INTO {message_user_actions} (`id`,`userid`,`messageid`,`action`,`timecreated`) 
                VALUES (NULL,$subm->useridfrom,$subm->id,'2',UNIX_TIMESTAMP())";
            printVar($sql);
            $insert = $DB->execute($sql);
            echo 'insert: '.$insert;
        }
    }
    
}

/**
 * get all messages given the userid and timecreated
 * will return all unique conversationid where user is participating
 *
 * @param [type] $userid
 * @param [type] $timecreatedUnix
 * @return Array<Object>
 */
function get_messages_by_userid($userid, $timecreatedUnix) {
    global $DB;

    $sql = "SELECT DISTINCT(conversationid), id FROM {messages} WHERE useridfrom = $userid AND timecreated < $timecreatedUnix";
    $messages = $DB->get_records_sql($sql);
    // printVar($messages);
    return $messages;
    
}

/**
 * Debug and print in clear way
 */
function printVar($var) {
    echo "<pre>";
    print_r($var);
    echo "</pre><br>---<br>";
}


?>