Laravel Global Scope indexing issue with multiple where conditions

[alignwebs]

SCENARIO

In a Multi-Tenant application, we divide tables by the tenant_id column. Adding $query->where('tenant_id', $tenant_id) to each and every model query is not the best practice. To overcome this issue, we use Global Scopes.

ISSUE

select * from banners where active= ? andtenant_id = ?

When applying Global Scope to a Model, the applied Scope gets appended to the end of the Query Builder.

But when adding indexing to tenant_id for the above query makes no sense due to the current order of columns.

EXPECTED RESULT

select * from banners where tenant_id= ? andactive = ?

Global scopes are added keeping in mind that the first scope will be executed then the subsequent where conditions.

It will be really helpful in creating optimized multi-tenant applications via Laravel if we can have an option to configure whether to apply the global scope at the start or end.

SUGGESTION

I think simply re-ordering all the where conditions at the final step of query keeping scopes at the priority could help us to achieve this.

Thanks

[deleugpn]

The order of your where clause doesn't matter to the optimizer. It will use the best index it can discover available

[alignwebs]

Can you please share a related source? @deleugpn

[korazy8s]

I would also like to throw my hat into this ring. I have a project that I started with v4.2 and is currently using v8.1. I depend on global scopes for multi-tenant queries and they must be the first added where clause items.

Example: MyModel::where('active', 1)->get(); must have a where clause '... where tenant_id=1 and active=1;'

[alignwebs]

Can you please check and verify @deleugpn answer?
I was told that best possible index is auto selected.