Jump to content

Query join


jerome

Recommended Posts

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 ?

 

Link to comment
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

Link to comment
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

Link to comment
Share on other sites

  • 1 month later...

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'.")

 

Link to comment
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

 

Link to comment
Share on other sites

  • 10 months later...

Archived

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

×
×
  • Create New...