Our current method for retrieving hierarchical tree connections is downright awful. We iterate through the tree, retrieving each node and making a new query every time we need the next node. This code features most prominently in the Class and Topic Repository's descendants() and ancestors() functions and it no doubt slows the entire site down. It's extremely inefficient because it requires O(n) queries to the database. As we know, queries are one of the most expensive processes in a request.

It would be nice if we could make O(1) queries and retrieve exactly the number of levels of the tree we desire.

The ideal situation would be for us to use a graph based NoSQL database like Neo4j. However, there do exist ways to retrieve hierarchical data in MySQL, as well.
Unfortunately, most people recommend using MySQL 8's with recursive statement. But we currently use MySQL 5.7, and I've read that the transition is rough (see laravel/framework's pr 23948 and issue comment 437717019 on that pr).

• consider either using with recursive or Neo4j
  • check whether either option can do all of the things we want them to
• if using mysql, figure out how to install mysql 8 in the installation script or wait for it to be supported by debian
  • also make sure current mysql 5.7 users can use the installation script to update to it
• if using mysql, write new queries and refactor the code to use them. if using Neo4j, rewrite everything
  • if using mysql, you can use this article to construct queries for getting ancestors and descendants and calculating depth