How to add field values ​​to a huge table with object hierarchy?

Question

MySQL

Hello!
There is a table like guid, parent_guid in mysql database.
The topmost objects have parent_guid = null. Number of levels 4.
The interface has filters for objects of upper levels and you need to get objects from lower levels using them.
The table contains 200k objects and querying every bottom object would be too long.
I want to add a top_guid field to each object in the table, which will contain the value of the top object, and thus display objects by filter.
Now the php script adds this value around 9 o'clock.
What is the most convenient way to add these values ​​​​through sql query?
What other solutions are possible?

Answer 1

I use something like this algorithm. I read the entire table with a simple query from the database (two fields) and create a tree with all sublevels. It seems easy to make changes to the algorithm to insert top_guid on the fly or then iterate over the array and add it.

/*
 * make a tree array based on sql query
 */
function buildtree()
{
    $refs = array();
    $list = array();

    $nodes = (Получить поля guid и parent_guid из всей таблички);

    foreach ($nodes as $data) {
        $thisref = &$refs[$data['guid']];
        $thisref['parent_guid'] = $data['parent_guid'];
        if ($data['parent_guid'] == null) {
            $list[$data['guid']] = &$thisref;
        } else {
            $refs[$data['parent_guid']]['children'][$data['guid']] = &$thisref;
        }
    }
    return $list;
}