How to organize the structure of reposts records?

enter image description here

The most obvious structure is:

post_id | parent_post | text 1 | NULL | ... 2 | 1 | ... 3 | 2 | ... 4 | 2 | ... 5 | 1 | ... 6 | 3 | ... 7 | 2 | ...

but in order to select all the reposts of a single record, it is necessary to write a huge request, which the server can easily put.

Perhaps there is a pattern for a flat structure?

  • And why choose all the repost of one record? As a rule, you need to know only their number. And in the tape you have to choose in the opposite direction - if the author of the tape made a repost with a comment, then all you need is to select all the parent reposts with their comments, and put them into each other. As a rule, this is a maximum of 5 levels. This is if your reposts work as in Tumblr, for example. - Artur Udod
  • @ArturUdod, I meant the repost tree for one record. in such a structure, you will have to make a select for each parent_post and if there are a lot of them, then acc. there will be a lot of requests to the database - Vitaly Leonov
  • Vitaly, I understood, the only question is: what kind of script is this? A single post repost tree can contain millions of posts, how are you going to show this on the UI? I can not imagine such a user script - Artur Udod
  • If you need to show who your repost is and with what comments, then first choose the first X first-level entries sorted by date. When you click the right arrow, the following X entries are loaded. When you click on the repost itself, its tree is loaded further along the same principle. In general, the user script can be any, the main thing is that by no means it will not be possible to show all the reposts at once, and therefore it is not necessary to load everything at once. - Artur Udod
  • And if we are talking about the total number of reposts, then, firstly, database queries for the number are executed much faster, and secondly, you can make a separate table-counter. - Artur Udod

1 answer 1

If it’s really about a tree (as the graph is more like a graph, although your text is a tree), then the Nested Sets pattern may suit you. Each post you supply with left and right boundaries and number all the subordinate leaves of the tree so that the left border of the repost is always more than the parent post, and the right border, on the contrary, is always less.

 post_id | lft | rth 1 | 1 | 14 2 | 2 | 11 3 | 3 | 6 6 | 4 | 5 4 | 7 | 8 7 | 9 | 10 5 | 12 | 13 1 1 <-------------------------------------------> 14 2 2 <----------------------------> 11 3 3 <-----> 6 6 4 <-> 5 4 7 <-> 8 7 9 <-> 10 5 12 <-> 13

Thus, in order to select all the reposts of which post, you need to select posts whose left and right borders are between the left and right borders of the root post. The problem of this pattern is the complexity of the insert. However, it is the fastest in terms of extracting, and most importantly, counting the number of reposts. Divide the right border of the root 14 into two and get the number of posts 7 (this really works if the numbering is solid). Another property - the removal of part of posts does not require renumbering, since the boundaries of the remaining reposts still belong to the set of the original post.

  • I considered this option, but, as far as I understood, he expects that only one parent refers to the child. But how to be when one record is reposted several times - Vitaly Leonov
  • Not quite clear, usually the descendant refers to the parent. We have the original post, which repost 20 people. The original post is a parent, 20 reposts are descendants, and they are quietly placed between the left and right border of the parent post. Pay attention to the 2 post with the boundaries of 2 - 11, he has 3 immediate repost: 3-6, 7-8, 8-10, they are all located between 2 and 11. Or the post may have several parents? Then we are dealing with a graph. - cheops
  • the tree in the picture (which, incidentally, is also a count), is simply “upside down” - Artur Udod
  • Yeah, but if it's a tree, even if upside down, you can use the Nested Sets pattern, if the graph is, it would be better to use the Neighborhood List pattern. - cheops
  • My opinion is that you just need to organize a portion of the dynamic data loading, and you can do without additional complexity. - Artur Udod