Ugh. I don’t know why this problem became a problem, or why I went after the most obvious yet least approachable solution out of the gate.

Here’s my basic testing data:

And here’s what I want it to look like (a subset):

In the recursive scenario, I would have had to call the top-level object (TLO) with a Parent_ID === 0 and then use that ID to get all records whose Parent_ID = TLO.ID. For each of those records I would have to call the function (recursion), passing the ID of the child to act as the ID of the parent. Each object would have to be added to the parent’s ChildZones property array until all parents have had their children processed and control returns to the original parent record.

In hindsight, this would have resulted in a lot of database calls. Right now, I only have 9 records representing just some of the “zones” represented in Star Citizen. It starts with the System of Stanton, which has Planets, but also other zones which aren’t currently in the test data. Each Planet has Cities, Outposts, Settlements, Derelicts, Points of Interest, and more. Depending on how granular we want things to get, a City or Station might have Shops as well. The planet of Hurston alone has at least two dozen places that need recording, so once the game has more star systems, this list is going to get pretty big.

The best option may have been to handle the tree-building at the database level as it would theoretically have the most horsepower to spare in building the thing. But SQL Server doesn’t operate like that — one of many reasons all modern RDBs suck ass in 2022.

With recursion causing a migraine, and server-side database being a no-go, it was up to the client to save the day. Well, the API server, actually.

Thanks to this post, I was able to get a solution that works right now. It sorts all records returned from the query into an array where the index is represented by the ID of the record. Then, it loops through each record in the original dataset. As it goes, it uses the current record’s Parent_ID to find the parent record, and then it adds itself to the parent’s ChildZones array. I mean, as far as solutions go, it’s stupidly simple when I write it out in plain text, but this was pretty much the Solution of Last Resort mainly because although it works with the nine records I have, I have no idea how it’ll work when there are dozens of star systems with several planets and moons, each with stations, cities, outposts, settlements, and other places in need of cataloging.

I might amend the endpoint to accept a Parent_ID as a starting point, so I could, say, pass the ID of the Stanton system and ONLY get children within that hierarchy. I don’t actually see a need to get an entire galaxy’s worth of locations; right now, I am working on an admin UI to allow admins to manage this data, so presenting the entire database as a hierarchy has value. As this is only an occasional endpoint call that wouldn’t be used on the front-end, at least not at the galactic level, it might hold up over time.