Fixing Sort Order on Imported Stream Note Entries?

[Zosh]

Hi, I'm wondering if anyone has encounter this situation before...

We are trying to import from a past CRM system old Account Notes as entries in the Stream section of an Espo Account, we've modified the Espo API to allow us to do this along with injecting the matched User that posted the Note along with also injecting the timestamp, sometimes from many years ago for these notes.

Everything works great, however the problem we are running into is a sorting issue within the Stream section now... It looks like Stream entries are sorted based on a Number column in the Note table. If you import new Note records either the API layer or the SQL layer assign it a generated Number value. This throws the chronological sort order of the Stream notes off, as pictured below. It seems like the incremented note.Number column is stretched across the entirety of the SQL table... I'm really not sure why this was seemingly the intended design because theoretically the Stream notes would never leave the Account which they are already associated to (unless a record Merge is done?) so why then does the sort order, which is predicated on the Number column, have to involve ALL seemingly unrelated Account Stream Notes in the table!? - Which then creates this issue I'm having trying to bring Account Notes from an old CRM system over the Espo API.

Has anyone dealt with this before? Not sure how to deal with this exactly... I was wondering if I could just dump the entire Note table to a CSV, sort the whole spreadsheet by created_at, and then just re-increment the whole Number column to fix Stream sort ordering for Accounts we've imported old CRM notes for. I'm unsure if modifying the Number column in the Note table will have any unintended consequences elsewhere in the system though?? I wish there was maybe some formula that could be ran in the GUI on an entity to just re-sort all their Stream entries.

Any ideas at all on how to tackle this?

[yuri]

I'd write a PHP script.

<?php

include 'path/to/espo/bootstrap.php';

$app = new \Espo\Core\Application();
$app->setupSystemUser();


/** @var \Espo\ORM\EntityManager $em */
$em = $app->getContainer()->get('entityManager');

$notes = $em
    ->getRDBRepository('Note')
    ->sth()
    ->order('createdAt')
    ->find();

$number = 0;

foreach ($notes as $note) {
    $note->set('numberAux', $number);

    $em->saveEntity($note);

    $number++;
}

The numberAux is a temporary created column (you will need to define it in metadata > entityDefs > Note). After the script executed, rename it to number and make an autoincrement unique. Not 100% sure whether it will work.

The problem of this solution that will lose the current order of notes.