Ancestry with ActiveImport #433

Activity

1. kbrock commented on Mar 19, 2019

   

   kbrock

   on Mar 19, 2019

   Collaborator

   More actions

   @Brotakuu are you doing a one time import, or is this a common process?
   Is this postgres or mysql?

   There is a built in method called: Model.build_ancestry_from_parent_ids!. but this does go through ruby. So, if you have a bunch of models, we may need to find a sql. approach

   It is tricky to populate a bunch of ancestry parents. Also, it sometimes gets botched if you don't populate the parents, then their children, then their children.

   I wonder if it is possible to make a sql statement that runs a few times that will build up the ancestry values 100% in the db.

   This is back of the napkin type coding:

   -- update root nodes
   update comments
   set ancestry = parent.id
   from comments as parent ON parent.id = c.parent_id
   where c.ancestry is null -- where I haven't been processed yet
   and parent.parent_id is null -- my parent is a root

   then keep going until nothing gets updated (update_all returns a 0)

   -- update each level of nodes
   update comments
   set ancestry = concat(parent.ancestry,'/',parent.id)
   from comments as parent ON parent.id = c.parent_id
   where c.ancestry is null -- where I haven't been processed yet
   and parent.ancestry is not null -- but my parent has been processed

   If this looks like it is working, maybe we can get it into the product. Since updating the hierarchy is a pretty common task
2. Brotakuu commented on Mar 19, 2019

   

   Brotakuu

   on Mar 19, 2019

   Author

   More actions

   Thanks @kbrock. I'm using postgresql and this is a weekly process of importing 300k comments which only have parent_uuid column referencing the direct parent comment. So i'm using ancestry to build the entire comment tree.

   During the bulk import process, there's no way to set the parent_id since the parent comment is created at the same time as the descendants. So Model.build_ancestry_from_parent_ids! wouldn't work, unless I could run a similar method using the parent_uuid column.

   So you're suggesting running a recursive sql query to build the tree from top down? Do you think this would be faster than what I'm currently running:

   Comment.where(is_parent: false, ancestry: nil).all.each do |c|
       parent = Comment.find_by(uuid: c.parent_uuid)
       c.update(parent: parent)
   end
   
3. kbrock commented on Mar 20, 2019

   

   kbrock

   on Mar 20, 2019

   Collaborator

   More actions

   @Brotakuu think your code is pretty similar to build_ancestry_from_parent_ids! with the record passed in.

   It sounds like you are building the whole tree of comments. If the depth is more than 1 deep (just parents and children), then it is important to update the records in a particular order. #429 explains how just setting the parent without paying attention to the order is troublesome.

   You need to make sure the parent's ancestry is setup properly before the child's ancestry is set. Otherwise the child ancestry trees will be partial and not setup correctly.
4. kbrock commented on Jul 4, 2020

   

   kbrock

   on Jul 4, 2020

   Last edited by kbrock

   Collaborator

   More actions

   the performance differences will be <1 second vs an N+1. for us it was a few minutes vs under a second

   if you look at the ruby, comment.ancestry = comment.parent.ancestry + "/" + comment.parent.id assuming that parent has a parent. If parent does not have a parent, then it is comment.ancestry = comment.parent.id

   so if you know either of those cases, you can simplify the resulting sql

   so right now we have

   comment.ancestry =
     case comment.parent.ancestry is null then concat(comment.parent.ancestry, "/",comment.parent.id)
     else comment.parent_id
     end
   

   given that parent is a self join and parent either has a nil parent id and nil ancestry or non nil parent id and non nil ancestry

   in the other cases, the parent hasn't been updated yet and we need to approach in a separate pass.

   you can probably get some ideas from
   https://github.com/ManageIQ/manageiq-schema/pull/465/files

   shows us starting at the bottom and going deeper.

   @d-m-u do you have bandwidth to fix this? I'm thinking we have too many loose ends right now to approach. (and this is from 2019. so while lots of people need this, including us, it may unfortunately take a backseat)
5. d-m-u commented on Jul 7, 2020

   

   d-m-u

   on Jul 7, 2020

   Contributor

   More actions

   @kbrock I think it's worth a shot.
6. donnguyen commented on Sep 28, 2022

   

   donnguyen

   on Sep 28, 2022

   More actions

   @kbrock @d-m-u do we have any updates on this? Thanks