After we introduced pagination in the last lesson, we shouldn't have performance problems with loading too much data. But what about Eloquent queries in the search? Time to test their loading time.
For that, first, we need to simulate a significant amount of data, in factories/seeders.
Simulating Data with Seeders/Factories
I will go for this scenario to seed:
- 100 countries
- 1000 cities
- 1000 geoobjects
- 1000 owners and 1000 regular users
- 100 000 properties
- 200 000 apartments
- 200 000 bookings with ratings and 200 000 bookings without ratings
To implement that testing scenario, I decided to create a totally separate Seeder class, that could be launched on a separate testing DB, for example...
Excuse me, You did copy and past ApartmentSeeder codes block in BookingSeeder
Great catch, need to be more careful next time! Fixed by pasting from the repository.
I am getting this error while loading the count of facilities. How can I fix this?
SQLSTATE[HY000]: General error: 1390 Prepared statement contains too many placeholders (Connection: mysql, SQL: select facilities., (select count() from properties inner join facility_property on properties.id = facility_property.property_id where
Hmm, interesting, I thought about it and wondered why this error didn't happen for me: the reason is too many property IDs loaded as parameters and MySQL didn't like it. How many properties do you have in the DB?
Maybe I should rewrite that query somehow to not contain so many IDs.
The records I inserted into the database are the same as those in your example. Most likely, the problem stems from the fact that I accidentally called "search" without including the filter for country, capacity_children, and capacity_adults. So: booking.test/api/search. However, the error message could still occur if, for example, I apply the filter for country=90 and have numerous properties in that country.
I replaced your query with this one, it should be fine. Maybe when you have time, try testing it with 2-3 facilities per business and check the results it produces to see if they are consistent and if it can still be optimized.
Screenshot: https://postimg.cc/Yv58dsb6
Wow interesting solution, definitely will check it out in upcoming days (I have 0 free time at the moment) and will get back to you.
Ok I've made some research and apparently the limit of parameters/placeholders is 65 536
I think with the filter of country/city there should be really smaller amount of properties than this limit. So I don't think this optimization is for a real-case scenario. But good catch, anyway!
Hello, i ran php artisan db:seed --class=Database\Seeders\Performance\PropertySeeder and i got this error SQLSTATE[42S22]: Column not found: 1054 Unknown column 'role_id' in 'where clause' (Connection: mysql, SQL: select
idfromuserswhererole_id= 2)public function run(int $count = 100): void { $users = User::where('role_id', Role::ROLE_OWNER)->pluck('id'); Role::all()->pluck('name'); $cities = City::pluck('id');
Sorry it's hard for us to debug every individual situation especially if you're not following the exact lessons but decide to use Spatie package, then you need to make changes in the factories/seeders too, so they would work according to that package.
Also, yes there's no role ROLE_OWNER, it is a constant inside of Role model, please compare to the repository in the course.
Looks like you're missing the Role Class. the $users variable should be $users = User::role(Role::ROLE_OWNER)->pluck('id')