Laravel: BelongsTo vs Polymorphic? Let's Test!

hello guys quite often I get a question like how to structure the database and specifically about relationships polymorphic relationships or belongs to relationships in this video we will compare them test them practically what is better what is faster what is more convenient and this will be based on a question I have you can see that on the screen so this is a situation database can have calls and appointments and every call or appointment may be attached to lead or a customer or an operator or someone else and the person asks what is the best structure so first option is to have separate tables the costs for the leads calls for the customers calls for operators or have it all in one table with multiple foreign keys and then there's a third option polymorphic relations but we will get to that so first let's tackle this one this one I wouldn't advise because if you need to populate one table of appointments for the week you would need to query multiple tables so you would have to Union join or something else like complex structure every time you need to get all appointments that structure makes sense only if that tables are really different so lead appointments are different unique appointments with some unique calls for the lead appointments and then customer appointments would be somehow different with a few more columns specifically for customers so in that case it could be separate otherwise I wouldn't advise that so let's just cross that out and let's get to this one now the question is should it be caused with lead ID customer ID operator ID as 3 belongs to foreign keys or polymorphic relationships with two columns callable ID and callable type and I've created a local demo project to test both of them so we have database with appointments with three fields with belongs to relationships lead ID customer ID an operator ID and I've seated 10,000 points to test the performance so one of these is always present so two others are known so that's one way to structure and for calls I have tried polymorphic relationship so instead of three fields we have two fields callable ID and callable type type is a class name of that model that calls the long term now which is better and there are three questions here three sub questions first what is faster so like if we get all appointments and all calls which is faster second question is which is faster or more convenient to query so use where statements in eloquent and how to query by some name or something like that so we will test that one and third question how convenient it is to change the structure so add more fields or delete date or something like that so that would be our third test actually before we get into actual web project let's compare the database size both of appointments table and cost table have 10,000 rows and appointments has size of 5 to 8 kilobytes that's how simple Pro shows that and calls 1.5 megabytes so as you can see polymorphic relations is 3 times more in size in fact I'm not sure how it's calculated in sequel probe because I've done export so export to SQL tried it also and this is the result the actual export in SQL is really similar so polymorphic are only like a few percent more in size so I'm not sure if that is accurate so let's say just a random stuff from sequel Pro you can test that probably on your database and now let's get to the web project I've built I've installed laravel debug bar to measure the queries and there is a list of appointments which looks like this and list of calls which looks like this almost identical and let's see their performance in controller we load all appointments with 3 relationships and paginate in in appointment we have 3 e function so lead customer and operator all of them belongs to to their own respective class and in course we have the same thing but just in one relationship so in call we are polymorphic which looks like this so just more of two the difference in the code is probably this one so how to show customer or lead or operator in prefix in the belongs to case you have to basically do this so if and then show that field and show that name in polymorphic it's more convenient so all you do is this you get the class of that callable some string manipulation to get rid of app and then call a bone name so it's more pretty involving morphic case but is it faster so let's compare the queries appointments let's ignore the total time because it has some more logic you not been panel but let's focus on the actual queries that we care about so laravel queries the appointments and then queries the count for the pagination and then whenever the lead is found or the customer or the operator there are three queries additionally to query the relationships and those are pretty quick so milliseconds milliseconds two milliseconds let's take a look at calls so select from call select from calls from customer leaves and operators guess what the queries are identical maybe that time is a bit different but in general structure of the queries is identical so whenever you use polymorphic or belongs to in the just a list of records the results should be almost identical so in this case there is no winner next test we will do is how to query the appointments or calls for specific lead or specific customer so let's say we will have a parameter off like lead ID something or operator ID something and we need to show the calls for that specific person and this is the code for the belongs to relationship so I will use eloquent function when and I check when the request lead ID is present required by lead ID otherwise by customer ID or operator ID in course controller and polymorphic relationships we can also use one but then we need to query two things we need to query callable ID and also callable type and callable type is actually string not an integer and also this one doesn't have index on that so in theory it should be slower right let's test it out okay so query the appointments by I D and this is what we get where the appointments by lead ID and the amount of that for pagination then we'll get this query for the relationship and these two are pretty weird right wouldn't query customers or operators but in fact we did when we are loading the appointments we specify that we load with these relationships and even if those relationships don't exist but they're still acquired to the database so this and this so these two queries are called no matter what but they are really really small to be honest so wouldn't worry about them too much in polymorphic case we have lower amount of queries so we don't have those two extra queries but as I said we are querying by two fields callable ID and callable type and by default none of them is index in the database so in theory it is slower in this case for this project it's not that visible but c4 belongs to the query is eight hundred microseconds so 0.8 milliseconds for the polymorphic is for five milliseconds so if you compare that in terms of percentage it's significantly slower and you can of course add indexes to the database to this one and test it out but by default polymorphic relations is slower to query and let's try another query let's query not by lead ID or operator but by name of a person who may be operator lead or customer so let's say we will have calls and then query equals some name and in case of belongs to relationship we add this one so when the request query is present we do where has it's an eloquent operation of querying the relationship and in this case we need to add three queries where has lead with the main or where has customer with the main or where has operator with the main in polymorphic case the syntax is actually different it's shorter but I had to actually Google that how to query that in polymorphic and take a look at the syntax query where has more so callable is the relationship then you specify which relationships which class has to include in that so you can exclude something it's pretty convenient actually and then have one query where statement let's take a look at the queries and the performance so for the belongs to relationship this is the main query so select from appointments where exist and then three sub query select from leads or select from customers or select from appointments or actually operators and it is 42 milliseconds quite heavy already let's take a look at the Norfolk way and we have this so select from cause we're also three sub queries but we have callable types called each time so called bull-type is this or this or that it's a pretty longer query but in terms of the time spent it's really similar so 37 milliseconds verses 42 milliseconds actually let's try it again 35 in actually 39 in belongs to and 38 in polymorph so it's really really similar in this case but in general what I would say it's more complex thing to query the polymorphic relation so you have to know specific syntax for polymorphic relations and if a query gets a little bit heavier eloquent defaults would provide you the syntax but in polymorphic relationships you would probably likely would need to get two raw queries or DB query or something like that and you wouldn't be able to use full power off eloquent okay so that's querying the database and make your own conclusions which is better or which is more convenient to you now what if you want to add another field so let's talk about the structure of the database and any changes in the future let's imagine we need to add a fourth field I don't know vendor ID or something so lead customer operator or vendor and this is where polymorphic relations shine this is the specific case why polymorphic was invented so imagine for the belongs to relationship you would need to add another method to the model of appointments so but vendor or something then you need to add another new field in migration as in controller you need to add with here so vendor then we need to add well request here for example and then in index blade in if statement you need to have this so add another line here basically whenever you have three cases three ifs you would need to add a fourth one and this is where polymorphic is better because in call stable nothing is changing structure is the same you would just have another value of app vendor so that's one thing you don't need to change in the model of code bhp no new method callable is still callable in the controller you don't need to change with statement probably a few things to add so here one request and probably add here so still some work to do I agree and then in index blade in appointment you have fourth if statement in cost index you change nothing so I'll probably repeat myself but polymorphic relationships the best way to use them is if you predict in the future that there will be third fourth or fifth type of that data in the same table on the final note of this video I want to mention a drawback of polymorphic relations because polymorphic relations not sure if you are aware there are not actually relationships on the database level so if you look at the database structure appointments you can go to table relations and they are all foreign keys to customers table to leads or to operators in calls there are no actual relations so those two are just fields in the database and eloquent or lateral is artificially attaching them to each other on the application level on level level but not on the database level what does that mean practically so on the database level what if some lead or customer operator is deleted then database foreign keys wouldn't allow to do that so they would prevent on the database level if I try to delete the lead that has appointments database would either restrict that or if you choose to cascade them then delete the appointments automatically with that lead on the database level in case of polymorphic relationships if you delete the customer or the lead nothing changes in this table the data would stay and just kind of hang in the air so there is no way to restrict the lead from being deleted if they have appointments and there is no direct way in the database to cascade the deletion of appointments by foreign keys or database cascade and there are packages for that lateral packages there are cascade soft aliefs and stuff like that but on the database level it's not that easy so that's it my mini research mini tests on how both work if you want to know more about polymorphic relationships or eloquent relationships in general I have a course called eloquent expert level so you can check that out also I have a special video about polymorphic relations specifically explained well for those who haven't used that so the video is in the corner check that out also subscribe to the channel because I'm shooting videos daily now so it's totally worth to subscribe and see you guys in other videos

Channel: Laravel Daily

Views: 22,178

Keywords:

Id: 6J8vb5_WRBw

Length: 14min 8sec (848 seconds)

Published: Thu May 14 2020