Jump to content

Efficent Custom Attribute Queries, javascript.


Samuel

Recommended Posts

Hi,

We are using the Ftrack's javascript api to query a few different attributes from a Task's Project, and Parent. Since (as far as I am aware) we cannot project custom_attribute fields we are projecting the entire custom_attributes collection and then pulling out the values we need in our code after. A query might look like this currently.

select project.metadata, project.name, project.custom_attributes, parent.custom_attributes, parent.object_type.name, parent.name, parent.id, id from Task where
    project.status is active
    and assignments any (resource_id="user-id-here")
    and status.name is_not "OMIT"
    and status.name is_not "ON HOLD"
    and status.name is_not "Approved"
    and project.name not_in ("Test", "dev")
    and type.id not_in ("47f5f240-fda1-11e4-92de-002590f35914", "51de618e-fda1-11e4-b48d-002590f35914", "93f57f18-3f90-11e5-841a-002590f35914", "a5cf41a2-84d6-11ea-ae88-0cc47adee6c0")

What I am wondering is if there is a faster way to query and return this custom_attribute data or just a few of the attributes instead of all as it can take a significantly long time when the number of matching tasks approach 100. If I remove the parent and project custom_attribute projections the query is extremely quick so it seems returning that data is slowing things down a lot but a few fields from that data is required for our tool.

Just thought I'd ask in-case I missed some recent update that makes this process faster.

@ftrack/api : v1.0.0
Ftrack: 4.10.2.2048


Thanks for your time!

 

Link to comment
Share on other sites

  • 3 weeks later...

Hi Samuel,

As I understand things, it is not possible to project to get only certain custom attributes. If I were you, I'd explore fetching your tasks without the custom attribute values and then getting the few custom attribute values that interest you in a second pass. For example, once you have your tasks you could do something like:

cac = session.query('CustomAttributeConfiguration where label is fps_h').one()
cav = session.query(f"CustomAttributeValue where entity_id is {task['id']} and configuration_id is {cac['id']}").one()

Obviously this is Python, which is what I was testing with, but you can easily adjust to taste. You can also obviously query for `CustomAttributeConfiguration` records based on multiple keys or labels based on your needs and then query for `CustomAttributeValue` records based on multiple `entity_id` based on your task query and multiple `configuration_id` based on your `CustomAttributeConfiguration` query.

I'd encourage you to profile both approaches to see if the second would get you gains based on your particular circumstances.

If your use case isn't dynamic in nature, you could also forgo the `CustomAttributeConfiguration` query by "hardcoding" those ids in your `CustomAttributeValue` query in a similar way you're doing it for `type.id` in your `Task` query.

Cheers,
Patrick

Link to comment
Share on other sites

Thanks! seeing the attribute configuration route was helpful and as our list of custom attributes expands that should help pull select values.

We've decided to restructure the tool to better match ftrack's structure so information can be pulled more dynamically as you've mentioned. It's an extra query but faster than pulling everything we might not need.

Link to comment
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
×
×
  • Create New...