It’s time to dig into an important topic for all developers; filtering entities in an efficient way. The ftrack API already has a lot of flexibility and choice, providing options for what relations can be used and it’s just got even better.

Before diving into what’s new, we’ll guide you through a few ways of how to filter on data in ftrack using our API.

Filtering on scalar attributes is the most basic form of filtering – just filter on an attribute. Here we filter out Notes written on a given date:

Note where date is "2018-05-25"


Or even combinations of such filters:

Note where date > "2018-05-01"  and date < "2018-06-01"


ftrack also supports filtering on relations. Here we filter on a scalar relationship. As an example, we can filter out all the Notes written by a single author:

Note where author.username is "john.doe"


The same query can be written using the “has” keyword. Same thing but different syntax:

Note where author has (username is "john.doe")


Well that was easy, wasn’t it? Now let’s filter on a collection relationship:

Note where replies.author.username is "john.doe"


The alert reader probably noticed that we filtered on both a collection (replies) and scalar (author) attribute – we can mix them. Imagine now that we don’t know the username (john.doe), but we know the first and last name “John Doe”. How would we filter then?

Note where replies any (author.first_name is "John" and author.last_name is "Doe")

You can see that we use the “any” keyword. This allows us to filter on collection relations where an item in the collection must meet both criteria (first_name and last_name).

Now a sneak-peek at the new subquery filters

In the upcoming ftrack release we will be introducing subqueries as a way of filtering. The syntax is similar to other types of filtering but with a query as a condition:

<Entity> where <Attribute> in (<Query>)

E.g. we can rewrite our Note and replies example above with the new syntax:

Note where id in (select in_reply_to_id from Note where author.username is "john.doe")


This will give us the same result but the syntax is different. Another useful way of using subquery is to filter on items that do not have a natural relation. E.g. Notes based on a parent property.

In this example we filter out all Notes written on tasks that have the ‘In progress’ status:

   Note where parent_id in (select id from Task where status.name is "In progress")

 

That was a quick look at filtering and the new upcoming subquery feature. For more information on the API and filtering please have a look at these links: