Objection.js: Dealing with ltree in postgres

[jasonmacdonald]

Is there a way in Objection to deal with ltree hierarchies in Objection. I can't find anything. I have a table which organizes rows using an ltree. What I would like is for a model to be capable of communicating it's model as an object, but representing and persisting that object as rows in a ltree.

As an example, the output of the model would be something like...

{
   item_id: 1,
   path: '1',
   items: [
       {item_id: 2, path: '1.1'},
       {item_id: 3, path: '1.2'},
  ]
}

and the rows in the DB are something like

item_id   |   path
    1          1
    2          1.1 
    3          1.2

I'm stumped on how to do this efficiently. I'd like to be able to commit the whole tree, calculate the ltree path for each node, and then update the whole thing with a single SQL query. Is this possible?

[elhigu]

I have never heard anyone using ltree with object nor knex so this might be uncharted territory...

Wouldn't your case mean that you would like to update many ltree rows with single SQL query? Updating multiple rows with different values with single SQL query is usually quite complicated. Do you have in mind what kind of query it would be in plain SQL?

I don't know how objection would help to do this kind of task of modeling ltree structure... Maybe having some LtreeModel which has relation to one ItemModel and to multiple LtreeModels... Then you could use insertGraph to push the whole tree and if there is going to be updateGraph in some point that could be used to update all tree elements... It might be less efficient than some custom update method, which doesn't update all the rows in the table.

I'm having a bit hard time to understand all the use cases what you would like to exactly :)

[elhigu]

I think this is something that is not that much related to objection... Lets open this again if there are more concrete examples how one would like to use this.

[solumos]

I would suggest that you re-open this. the ltree plugin has its own set of operators (see https://www.postgresql.org/docs/9.1/static/ltree.html)

There is a simple third-party Django field that supports the most common operators for Django:

https://github.com/whitglint/ltreefield

[koskimas]

This is a perfect candidate for a plugin. I don't think this should be a part of objection core.

[jawadcode]

Sorry to ask this here but are there any plugins available for objection which add support for LTREEs yet?

[solumos]

Doesn't look like it - I ended up rebuilding using SQLAlchemy (sorry, probably not helpful).

[jawadcode]

Oh, I would've expected someone to have attempted to make this in the past 3 years. But I have no entitlement to it, maybe I could write the plugin myself 😂