is query by link possible

[pawel]

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

 

[Mattias Lagergren]

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.

[pawel]

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.

[Mattias Lagergren]

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

[Mattias Lagergren]

If you only expect one query result you can do:

session.query(...).one()

As that will raise and error if you get multiple results

[pawel]

That's brilliant! Thank you.