pawel Posted July 25, 2017 Report Share Posted July 25, 2017 Hi, We are in the process of evaluating ftrack as a replacement for our in-house system. Quite often we need to find database entries based on a system path. From the system path, we can extract the project/sequence/shot etc names that are unique for an entity. One way to retrieve a shot information is with multiple queries to access specific IDs: project_number = 4444 sequence = 'Online' shot_name = 'sh010' project_id = session.query('select name, id from Project where name is {0}'.format(project_number)).first()['id'] sequence_id = session.query('select id from Sequence where name is {0} and project.id is {1}'.format(sequence,project_id)).first()['id'] shot = session.query('select name, link from Shot where name is {1} and parent.id is {0}'.format(sequence_id, shot_name)).first() this seems to be quite convoluted and potentially slow. however shot['link'] contains the same information of the parent structure that we can extract from the path and we could easily create it (without the IDs). is it possible to construct the 'link' with the names of the items in the hierarchy and use it to query? I tried something like this but the query doesn't like the square brackets (this is where my sql knowledge ends): [p['name'] for p in session.query('select name, link from Shot where name is sh010 and link[0].name is 4444 and link[1] is online')] is there another better way to query a shot (or another entity) knowing its logical path (link)? thank you Pawel Link to comment Share on other sites More sharing options...
Mattias Lagergren Posted July 25, 2017 Report Share Posted July 25, 2017 Hi Pawel, thank your for posting and welcome to the forums. There are a ways to achieve what you want in a faster way that just requires one query. # Going through the parent hierarchy. session.query('Shot where name is "sh010" and parent.name is "seq1" and project.name is "test_project"').first() Another one would using the ancestor relation, which could be more powerful if you have a more complicated nesting of the project structure where the exact order of the objects is unknown: session.query('Shot where project.name is "test_project" and ancestors any (name is "seq1" and object_type.name is "Sequence")').first() For readability I would recommend #1. Link to comment Share on other sites More sharing options...
pawel Posted July 25, 2017 Author Report Share Posted July 25, 2017 thank you, Mattias. your solution is indeed much cleaner and nicer. It is however always restricted to two levels of hierarchy - the project and the parent. I think this will get us going for now but I'm worried about more complicated hierarchies (we actually have more levels than that) where the query may return more than one item (or the 'first' is not the entity we are after). Please let me experiment more. Appreciate your help and quick reply. Link to comment Share on other sites More sharing options...
Mattias Lagergren Posted July 25, 2017 Report Share Posted July 25, 2017 If you have deeper structures you can go for either a parent.parent.name is "foo" type of solution. Or could utilise the ancestors one Link to comment Share on other sites More sharing options...
Mattias Lagergren Posted July 25, 2017 Report Share Posted July 25, 2017 If you only expect one query result you can do: session.query(...).one() As that will raise and error if you get multiple results Link to comment Share on other sites More sharing options...
pawel Posted July 25, 2017 Author Report Share Posted July 25, 2017 That's brilliant! Thank you. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.