0

Alright, so I've looked EVERYWHERE about how to sort/order hierarchical data with some sort of arbitrary value, like alphabetically, highest votes, etc. Do any of you have any solutions for a nested set or something else?

Here's a couple example queries that a guy made on reddit in my thread and I added to (unioning a lot of selects.. help?):

http://sqlfiddle.com/#!9/6dd457/10 - alphabetically and still in order http://sqlfiddle.com/#!9/786d89/36 - in order of highest votes first

These are adjacency lists at heart, and the mysql creates a materialized path based on your sorting parameter on the fly. This would be generated every time someone goes to viewtopic.php as each user has their own custom sort.

Now there are a few issues with this approach. First, seeing all of those selects and joins makes me a bit worried. I assume there's a much more optimized way to make all of this.

Php can be used to work out the highest parent node value, how many layers down we want to generate from that, and the formatting of some other things, but there's the problem with this crap in there too:

(1000 - l1.TotalVotes)+(2 * l1.Negatives) - having to be repeated like 5 times in the whole query

I tried to do ((1000 - l1.TotalVotes)+(2 * l1.Negatives)) AS l1.Votes in its respected select query, but I got an error :/ that would be one step to making it cleaner.

My ultimate goal is making a query that is able to sort in the order that I want, so I can just use templating with depth information to generate the html without having to use php any more than I have to, seeing as how sql is more efficient and meant for these sorts of things. I'm mostly worried that a query looking like that will be bad for larger sites.

The end goal is to have something as fast as reddit: nested comments with the ability to sort by post date, vote total, and some other parameters, all using as much mysql and as little php as possible. If you have any solutions to this using closure tables, nested sets, or just plain materialized paths, I'd love to hear them. The solution I have here looks optimized and scary.

Thrash Tech
  • 21
  • 1
  • 4
  • *as much mysql and as little php as possible* - why? If it was faster to sort in application code (php or client-side JS, wouldn't you want to do it there? – Strawberry Jun 15 '18 at 06:22
  • Well.. I don't know if it actually is faster to sort it in php or not. Everywhere I was able to find was insistent on sorting in mysql, as it's meant to do so and optimized for those operations. I'd rather do it the hard way that's eventually better than the easier way just so I can pump out the project faster. And I would never sort this in js. – Thrash Tech Jun 15 '18 at 06:27
  • *"seeing as how sql is more efficient and meant for these sorts of things"* That's a bit of an overgeneralization. An RDBMS is optimized for "sorting" because it avoids having to do very much actual sorting -- indexes literally store column data in already-sorted order so data can simply be read sequentially from the index and * poof * it comes out in the right order. Once you depart from queries that can be satisfied with indexes, much of the edge is lost. Part of the magic of nested sets is that they can be *stored* in a hierarchically-meaningful order. – Michael - sqlbot Jun 15 '18 at 09:54
  • The only problem with that is that I don't know how to go about ordering a nested set alphabetically, or by any other parameter whilst maintaining hierarchical order to an arbitrary depth, unless I make a nested set or materialized path or something per thing I want to sort but that's ridiculous. – Thrash Tech Jun 15 '18 at 10:09

0 Answers0