How to make nested comments preload associations as well?

Questions / Help
,
1

Hi,

I want to add nested (so an infinite amount of) comments for my project.

Adding 1 layer of nesting works and the associations get loaded, now I want to add another (10 or more) layers of child comments + associations. But this seems to be recursive… So I’m looking for such a solution, whereby I can get all comments from a company + all of their subcomments (+ their subcomments etc etc)…

Documentation I’ve found so far:
Similar to my case:


http://tensiondriven.github.io/posts/recursively-load-self-referential-association-using-ecto

Other documentation:

1 Like
2

This is not easy since SQL/RDBMS is not well suited for hierarchical data, so the solutions you mention recursively makes queries to fetch all data. That means you’ll need as much queries as you have levels in your comments. IMO that’s not an ideal situation. The approach I often take is storing the path next to the parent_id (I’m sure this approach has some name, but I cannot recall it now).

For example

id    text                parent_id     path
1     "hello"             nil           /1
2     "what's up"         1             /1/2
3     "what time is it"   nil           /3
4     "the sky"           2             /1/2/4
5     "a plane"           2             /1/2/5
6     "beer o'clock"      3             /3/6

So if you what all comments on the first comment(including the first), you query for comments whose path starts with /1. This path is set on/before you commit your comment to the DB. That way you don’t have to fire multiple queries at the DB and can easily check if a comment is in the subtree of some parent/root comment.

Hope that helps, shoot if you have more questions :slight_smile:

1 Like
3

If you will always load a full comment tree at the same (root) parent level, it might be worth denormalizing just a little bit so that each comment is associated to its root parent directly, as well as to its parent comment. Even if you only need a subtree you could still join on the root id and just filter out the rows you don’t need - depending on how broad/deep your trees are that may be faster than N queries.

1 Like
4

That is ingenious! I’ve not seen that before and it is so simple to think about! Instead of a path though you could use an expression indexed arrays and it would speed up querying them as well. :slight_smile:

5

Postgres has even a type you could use for this - ltree

https://www.postgresql.org/docs/9.1/static/ltree.html

4 Likes
6

Oooo, even better!!

7

Another useful link is Ltree extension example, because it didn’t work out of box last time I’ve checked. Here it is: https://hexdocs.pm/postgrex/0.13.0/Postgrex.Extension.html#content

1 Like
8

Postgres has support for recursive queries, works like a charm if your trees is not to large/deep.

https://www.postgresql.org/docs/9.6/static/queries-with.html

1 Like
9

Oh I forgot about that, I used it for a few specific things years ago!

Either way, not supported by Ecto (have to drop to raw SQL to do it), so… ^.^;

Would be nice if Ecto supported it though.

10

Thanks, gotta love this community, where answering a question only makes you learn more :blue_heart::green_heart::yellow_heart::purple_heart:

2 Likes
11

I thought about doing this in Django once, which meant I came across Django MPTT which states:

Modified Preorder Tree Traversal is a technique for storing hierarchical data in a database. The aim is to make retrieval operations very efficient.

The trade-off for this efficiency is that performing inserts and moving items around the tree is more involved, as there’s some extra work required to keep the tree structure in a good state at all times.

And points to these articles in the README:

Trees in SQL
Storing Hierarchical Data in a Database
Managing Hierarchical Data in MySQL

I hope they are of interest.

1 Like