laravel-property-bag
laravel-property-bag copied to clipboard
Performance
Hi! Something should be wrong here ... When I do something like this:
\App\models\User::withSetting("company", "name")
it performs this query:
select * from `property_bag` where `resource_type` = 'App\Models\User' and `property_bag`.`resource_id` in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11)
and also all these queries:
select * from `property_bag` where `property_bag`.`resource_type` = 'App\Models\User' and `property_bag`.`resource_id` = 1 and `property_bag`.`resource_id` is not null and `resource_id` = 1
select * from `property_bag` where `property_bag`.`resource_type` = 'App\Models\User' and `property_bag`.`resource_id` = 2 and `property_bag`.`resource_id` is not null and `resource_id` = 2
select * from `property_bag` where `property_bag`.`resource_type` = 'App\Models\User' and `property_bag`.`resource_id` = 3 and `property_bag`.`resource_id` is not null and `resource_id` = 3
select * from `property_bag` where `property_bag`.`resource_type` = 'App\Models\User' and `property_bag`.`resource_id` = 4 and `property_bag`.`resource_id` is not null and `resource_id` = 4
select * from `property_bag` where `property_bag`.`resource_type` = 'App\Models\User' and `property_bag`.`resource_id` = 5 and `property_bag`.`resource_id` is not null and `resource_id` = 5
select * from `property_bag` where `property_bag`.`resource_type` = 'App\Models\User' and `property_bag`.`resource_id` = 6 and `property_bag`.`resource_id` is not null and `resource_id` = 6
select * from `property_bag` where `property_bag`.`resource_type` = 'App\Models\User' and `property_bag`.`resource_id` = 7 and `property_bag`.`resource_id` is not null and `resource_id` = 7
select * from `property_bag` where `property_bag`.`resource_type` = 'App\Models\User' and `property_bag`.`resource_id` = 8 and `property_bag`.`resource_id` is not null and `resource_id` = 8
select * from `property_bag` where `property_bag`.`resource_type` = 'App\Models\User' and `property_bag`.`resource_id` = 9 and `property_bag`.`resource_id` is not null and `resource_id` = 9
select * from `property_bag` where `property_bag`.`resource_type` = 'App\Models\User' and `property_bag`.`resource_id` = 10 and `property_bag`.`resource_id` is not null and `resource_id` = 10
select * from `property_bag` where `property_bag`.`resource_type` = 'App\Models\User' and `property_bag`.`resource_id` = 11 and `property_bag`.`resource_id` is not null and `resource_id` = 11
And this is in my development environment with 11 users ... I can't imagine if I have 3000 users and it has to do 3000 queries.
I think there's a missing "join" somewhere in the "withSetting" method.
I know this is AWFUL code ... but just to show you ...
If withSettings method (in HasSettings) is replaced by this:
return static::select('users.id')->join('property_bag', 'property_bag.resource_id', 'users.id')
->where('key', '=', $key)
->where('value', '=', '"'.addslashes(json_encode([$value])).'"')->get();
it returns the same information, but avoiding doing as many queries as users having the setting requested.