Batching & WHERE clause with multi columns

[alonroth]

Thanks for the implementation of batching - it was just what I was waiting for :)

I have a problem to enable batching for a relationship with primaryjoin & secondary settings.

I have this kind of a relationship in my ProjectTenant model:

roles = relationship(
        TenantRole,
        secondary='system_project_tenants_roles',
        primaryjoin='and_('
                    'ProjectTenant.project_id == ProjectTenantRole.mt_project_id,'
                    'ProjectTenant.tenant_id == ProjectTenantRole.tenant_id'
                    ')',
        lazy='joined'
    )

When I'm trying to enable batching for this field I'm getting this error:

Incorrect syntax near ','.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n")
[SQL: SELECT system_project_tenants_1.mt_project_id AS system_project_tenants_1_mt_project_id, system_project_tenants_1.tenant_id AS system_project_tenants_1_tenant_id, system_tenants_roles.id AS system_tenants_roles_id 
FROM system_project_tenants AS system_project_tenants_1 JOIN system_project_tenants_roles AS system_project_tenants_roles_1 ON system_project_tenants_1.mt_project_id = system_project_tenants_roles_1.mt_project_id JOIN system_tenants_roles ON system_tenants_roles.id = system_project_tenants_roles_1.tenant_role_id 
WHERE (system_project_tenants_1.mt_project_id, system_project_tenants_1.tenant_id) IN ((%(primary_keys_1_1)s, %(primary_keys_1_2)s))s))

SQL Server doesn't support multi-columns in a WHERE IN clause (ref).

Maybe implement the tuple_ can be useful here?

Many thanks!

[jnak]

Batching uses SQLAlchemy's 'selectin' loader under the hood. Assuming your problem stems from the fact that 'selectin' does not work in SQL Server for relationships that have both primary and secondary joins defined, you should open an issue in SQLAlchemy as it's probably something they would want to address there. Let us know how that goes.

[alonroth]

@jnak It makes sense and it's seems that you're right, according to SA docs:

The only scenario in which selectin eager loading is not feasible is when the model is using composite primary keys, and the backend database does not support tuples with IN, which includes SQLite, Oracle and SQL Server.

https://docs.sqlalchemy.org/en/13/orm/loading_relationships.html#select-in-loading

[github-actions]

This issue has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related topics referencing this issue.