Query join
1 1

6 posts in this topic

Hi,

 

Is it possible to send queries with the new API that will use joins on several objects.

I would like to query an asset for a specific asset build, i.e. something like this:

Asset where parent.id is (    select id from AssetBuild     where         project.id is <projectId>         and name is "<myAssetBuildName>")

Is this kind of request valid with the new API ?

 

Share this post


Link to post
Share on other sites

Hey,

 

We don't support a full subquery style that you show there currently, though it has come up in discussion internally.

 

What we do support is the following syntax:

Asset where (parent.project.id is <projectid> and parent.name is <myassetbuildname>)

or more succinctly

Asset where parent has (project.id is <projectid> and name is <myassetbuildname>)

However, this will fail for you currently with a KeyError due to a polymorphic relationship in the backend. This is something we aim to address in the New Year.

For now, the best you can do is two queries:

asset_builds = session.query('select id from AssetBuild where project.id is <projectid> and name is <myassetbuildname>').all()asset_build_ids = [asset_build['id'] for asset_build in asset_builds]if asset_build_ids:    assets = session.query(        'Asset where parent.id in ("{0}")'        .format('","'.join(asset_build_ids))    ).all()

Martin

 

 

Internal ref: https://dev.ftrackapp.com/#entityId=5c5b5518-8172-11e5-be32-42010af06c59&entityType=task&objectType=bad911de-3bd6-47b9-8b46-3476e237cb36&itemId=projects&view=tasks

Share this post


Link to post
Share on other sites

Hi,

 

Thanks for your answer, I am currently doing the same workaround but I was hoping to avoid having 2 sequential requests to the server.

At the moment I am building a rather complex asset structure and the process can take quite some time to call each asset, asset build, versions etc sequentially. It would be great to have subqueries in the future !

 

Thanks again,

Jerome

Share this post


Link to post
Share on other sites

Hi,

Any update on this problem ? When trying to exec a single query I still have the following KeyError

asset_version_list = session.query("AssetVersion where asset.parent.project.name is '<myProject>'").all()

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/production/data/software/packages_2.0/ext/ftrack_connect/0.3.3/BUILD/lib/python2.7/site-packages/ftrack_python_api-0.11.0-py2.7.egg/ftrack_api/query.py", line 39, in all
    return list(self)
  File "/production/data/software/packages_2.0/ext/ftrack_connect/0.3.3/BUILD/lib/python2.7/site-packages/ftrack_python_api-0.11.0-py2.7.egg/ftrack_api/query.py", line 29, in __len__
    self._fetch_results()
  File "/production/data/software/packages_2.0/ext/ftrack_connect/0.3.3/BUILD/lib/python2.7/site-packages/ftrack_python_api-0.11.0-py2.7.egg/ftrack_api/query.py", line 35, in _fetch_results
    self._results = self._session._query(self._expression)
  File "/production/data/software/packages_2.0/ext/ftrack_connect/0.3.3/BUILD/lib/python2.7/site-packages/ftrack_python_api-0.11.0-py2.7.egg/ftrack_api/session.py", line 680, in _query
    results = self._call(batch)
  File "/production/data/software/packages_2.0/ext/ftrack_connect/0.3.3/BUILD/lib/python2.7/site-packages/ftrack_python_api-0.11.0-py2.7.egg/ftrack_api/session.py", line 1506, in _call
    raise ftrack_api.exception.ServerError(error_message)
ftrack_api.exception.ServerError: Server reported error: KeyError("No relationship u'project' exists for schema u'Context'.")

 

Share this post


Link to post
Share on other sites
Quote

 

Hi,

Any update on this problem ? When trying to exec a single query I still have the following KeyError

 

Nothing new to report on this yet. As of now you would have to go with a 2 query solution - or the somewhat odd:

session.query(
	'AssetVersion where asset.parent.id is "foo" or asset.parent.parent.id is "foo" or asset.parent.parent.parent.id is "foo"'
).all()

 You will have to "traverse" the parents as many levels as you expect to have and somewhere hit the project. Not the most elegant solution though

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
1 1