Jump to content

is query by link possible


Recommended Posts


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



Link to comment
Share on other sites

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

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


This topic is now archived and is closed to further replies.

  • Create New...