returning a filtered list from DB by a list of ids contained in another query with SQLAlchemy Flask-CodePudding

I am trying to write an SQLAlchemy DB query that returns a list of provider_service_files that have NOT been assigned to a client:

My first query gets all the service files that have been assigned to a user:

client_resource_guide_urls = db_session.query(ClientResourceGuideUrl).filter(\
        ClientResourceGuideUrl.client_id == client_id).all()

returns a list with this output:

[
    {
        "client_id": 20,
        "id": 9,
        "service_file_url_id": 3
    },
    {
        "client_id": 20,
        "id": 10,
        "service_file_url_id": 2
    }
]

Using the service_file_url_id in that list, I query for ServiceFileUrl and if an entry contains an ID that is equal to any service_file_url_id I want that entry filtered out of the list.

   for client_resource_guide_url in client_resource_guide_urls:
        print(client_resource_guide_url) # only shows first entry
        service_files = db_session.query(ServiceFileUrl.id, ServiceFileUrl.service_id, \
            ServiceFileUrl.file_label, ServiceFileUrl.file_url, \
                ProviderService.name, ServiceFileUrl.provider_id,\
                    ServiceFileUrl.created_at)\
                        .select_from(ServiceFileUrl, ProviderService)\
                            .join(ProviderService).filter(\
                                ServiceFileUrl.provider_id == provider_id, \
                                    ServiceFileUrl.id != client_resource_guide_url.service_file_url_id).all()

This query is only filtering out the FIRST entry and not the second. A print of client_resource_guide_url only shows the first entry is being looped through. My approach is cleary not ideal here. Any advice would be appreciated.

CodePudding user response：

To leverage the power of SQL, rather than a Python loop, pass a list of values to the Column.not_in() function ref.

ids_to_filter = [i['service_file_url_id'] for i in client_resource_guide_urls]
    
service_files = db_session.query(
    ServiceFileUrl.id,
    ServiceFileUrl.service_id,
    ServiceFileUrl.file_label,
    ServiceFileUrl.file_url,
    ProviderService.name,
    ServiceFileUrl.provider_id,
    ServiceFileUrl.created_at)\
    .select_from(
        ServiceFileUrl,
        ProviderService)\
    .join(ProviderService)\
    .filter(
        (ServiceFileUrl.provider_id == provider_id), \
        (ServiceFileUrl.id.not_in(ids_to_filter)))\
    .all()