Complex 'sg_status_list' filtering

Hello,

If I want to list all completed ‘cmpt’ rigs, I would use this filter:

filters= [['entity', 'is', {'type': 'Shot', 'id': 24737}], ['sg_status_list', 'is', 'cmpt'], ['task.Task.step.Step.short_name', 'is', 'rig']]

But what if I want to filter:
all completed ‘cmpt’ rigs:

[ ['sg_status_list', 'is', 'cmpt'], ['task.Task.step.Step.short_name', 'is', 'rig']] 

Plus Any model ‘mdl’ whether completed or not:

[ ['sg_status_list', 'is_not', 'null'], ['task.Task.step.Step.short_name', 'is', 'mdl']] 

How do I combine these two filters? I don’t think nesting works.

2 Likes

Hi @aitrdae,

I think you’re looking for complex filters. Let me know if you need more details and I can take a stab at rejiggering your example.

2 Likes

Thank you. I am not sure I am able to merge the two commented filters:

filters = [
['entity', 'is', {'type': 'Asset', 'id': 4176}],

{
    "filter_operator": "all",
    "filters": [
        
        ['task.Task.step.Step.short_name', 'is', 'rig'],
        ['sg_status_list', 'is', 'cmpt']
    ]
}

]

#filters= [[‘entity’, ‘is’, {‘type’: ‘Asset’, ‘id’: 4176}], [‘task.Task.step.Step.short_name’, ‘is’, ‘rig’],[‘sg_status_list’, ‘is’, ‘cmpt’]]
#filters= [[‘entity’, ‘is’, {‘type’: ‘Asset’, ‘id’: 4176}], [‘name’, ‘is’, ‘mdl’],[‘sg_status_list’, ‘is_not’, ‘null’]]

2 Likes

Hi @aitrdae,

This should do the trick:

filters = [
    {
        "filter_operator": "any",
        "filters": [
            {
                'filter_operator': 'all',
                'filters': [
                    ['sg_status_list', 'is', 'cmpt'],
                    ['task.Task.step.Step.short_name', 'is', 'rig']
                ]
            },
            {
                'filter_operator': 'all',
                'filters': [
                    ['sg_status_list', 'is_not', 'null'],
                    ['task.Task.step.Step.short_name', 'is', 'mdl']
                ]
            }
            
        ]
    }
]
sg.find('Shot', filters)

In your filters list, each filter that is usually a list of three items (a path, a relation and a value) can be replaced by a dictionary that describes a filter group. If you use the dictionary syntax for a filter group, you can specify a logical operator to use (‘any’ or ‘all’) for the filters in that group. Each filter in the group can then in turn, be a list or a dictionary that allows you to specify a nested group.

You could simplify the above example by using the filter_operator argument of the find() method, like so:

filters = [
    {
        'filter_operator': 'all',
        'filters': [
            ['sg_status_list', 'is', 'cmpt'],
            ['task.Task.step.Step.short_name', 'is', 'rig']
        ]
    },
    {
        'filter_operator': 'all',
        'filters': [
            ['sg_status_list', 'is_not', 'null'],
            ['task.Task.step.Step.short_name', 'is', 'mdl']
        ]
    }
]
sg.find('Shot', filters, filter_operator='any')

Let me know if this helps.

5 Likes

Hi @bouchep,

Thank you very much.

These are very useful intelligent filters.

3 Likes

Thank you again. I have a related question:

If this is the ‘PublishedFile’ entries:

{'version.Version.sg_status_list': None, 'version_number': 1, 'name': 'cam35', 'task.Task.sg_status_list': 'fin', 'published_file_type': {'type': 'PublishedFileType', 'id': 42, 'name': 'Alembic Camera'}, 'entity': {'type': 'Shot', 'id': 99999, 'name': 'SIL_999_0010'}, 'version': None, 'sg_status_list': 'cmpt', 'task.Task.step.Step.short_name': 'layout', 'type': 'PublishedFile', 'id': 999999}

What will be the correct filter to find the ‘Alembic Camera’ in the ‘PublishedFile’?

filters= [['entity', 'is', {'type': 'Shot', 'id': 9999}], ['published_file_type.name', 'is', 'Alembic Camera']] 

res = sg.find('PublishedFile', filters, fields)
1 Like

You can do ['published_file_type', 'name_is', 'Alembic Camera']
or possibly ['published_file_type.PublishedFileType.name', 'is', 'Alembic Camera']

There’s a fair amount to read here https://developer.shotgunsoftware.com/python-api/reference.html#filter-syntax

1 Like

Thank you very much @mmoshev

1 Like

Hi @aitrdae,

What @mmoshev said is right on the money so he gets the points.

I’d like to suggest a possible optimization when dealing with entity links that link out to a limited number of choices for the target entity type. In your case, there will only ever be a limited list of PublishedFileTypes that shouldn’t grow too large. For example, PublishedFileType records should be somewhat predictable values vs the myriad of Version or Note records you might have.

In this case, you could do:

['published_file_type', 'is', {'type':'PublishedFileType', 'id':<some_id>}]

When you use bubbled field syntax like, published_file_type.PublishedFileType.name or filter on name_is like ['published_file_type', 'name_is', 'Alembic Camera'] you’re asking the database to do a lot more work to join extra tables. This work isn’t required when searching for a literal object record like {'type':'PublishedFileType', 'id':<some_id>}.

Furthermore, depending on your code’s structure, you might even already have a list of PublishedFileType objects you can use. For example, if you had a UI with a pulldown of possible PublishedFileType records, you should be able to cache those and filter by the actual object and not its human-readable label like Alembic Camera.

Hopefully, this all makes sense.

2 Likes

Thank you very much @bouchep. Yes, it makes sense.

2 Likes