Complex 'sg_status_list' filtering

Pipeline Integrations
1

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
2

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
3

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
5

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
7

Hi @bouchep,

Thank you very much.

These are very useful intelligent filters.

3 Likes
8

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
9

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
10

Thank you very much @mmoshev

1 Like
11

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
12

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

2 Likes
13

this doesn’t worked. giving following error :
Traceback (most recent call last):
File “d:\shotgrid\shotgrid_event_creator\shotgrid_event_creator.py”, line 628, in
print(sg.find(‘Shot’, filters, filter_operator=‘any’))
File “C:\Users\zabronic\AppData\Local\Programs\Python\Python310\lib\site-packages\shotgun_api3\shotgun.py”, line 1057, in find
result = self._call_rpc(“read”, params)
File “C:\Users\zabronic\AppData\Local\Programs\Python\Python310\lib\site-packages\shotgun_api3\shotgun.py”, line 3423, in _call_rpc
self._response_errors(response)
File “C:\Users\zabronic\AppData\Local\Programs\Python\Python310\lib\site-packages\shotgun_api3\shotgun.py”, line 3740, in _response_errors
raise Fault(sg_response.get(“message”, “Unknown Error”))
shotgun_api3.shotgun.Fault: API read() Shot.task.Task.step.Step.short_name doesn’t exist:
{“path”=>“task.Task.step.Step.short_name”, “relation”=>“is”, “values”=>[“Rig”]}