Reading properties from sql.lite in node?

[leefsmp]

Greetings Petr!

In the readme there is a partial example on how to read properties with sql query, but it's not obvious to me how I could perform such query from a node app? More specifically I would be interested in harvesting dbId's of all leaf nodes in my model.

Could you provide some insights on how to do such thing?

Thanks a lot.

[petrbroz]

Hi Philippe! You can find some more examples on how to access the sqlite database from a Node.js app, and how to query different types of data here: https://github.com/petrbroz/forge-props-service.

Reading from/writing to sqlite database: https://github.com/petrbroz/forge-props-service/blob/master/database.js Example queries: https://github.com/petrbroz/forge-props-service#example-queries

And by modifying this particular query you should also be able to find all leaf nodes. Basically you'd be looking for all IDs that don't have any __child__ property. I'm not an SQL expert but something like the following query should give you these IDs:

SELECT ids.id, (
    SELECT COUNT(eavs.value_id)
    FROM _objects_eav eavs
    LEFT JOIN _objects_attr attrs ON attrs.id = eavs.attribute_id
    WHERE eavs.entity_id = ids.id AND attrs.category = '__child__'
) AS child_count
FROM _objects_id ids
WHERE child_count = 0