Eloquent Upsert vs UpdateOrCreate: Practical Demo

hello guys today i want to tell about upsides in laravel in laravel eloquent absurd feature was released in laravel 8.10 and i want to discuss how people did the same situation before 8.10 and also discuss why absurd may be better and what is the syntax and this is so interesting this video is compiled from a lot of you guys it's based on a tweet by martin who tweeted about absurd and this is the syntax from the laravel docs then i retweeted that tweet but this video idea came from the replies on my tweet where a lot of people noted that absurd is actually making fewer queries to the database so how it all works i decided to explain step by step so here's a typical scenario you have a form with multiple fields for example you want to assign points to users so you have users list the points is in a separate database table and you need to do for each of those points it's an array of user id key so in the blade we have something like this all of them are type number and it's all points user id and in the database they should be saved in the points database table with user id and points in the beginning there are no points no records so you need to create them but each time you want to update the points something needs to be checked so for each of the user if the points exist we update them otherwise we create that record so how to deal with that in laravel there are a few ways the long way then shorter eloquent way and then absurd way let me show you all three of them so here's the update method the old school typical for each way of dealing with that situation you do for each of the points array then you check if the point record exists if it does exist then you update the points if not you create that so it's all readable and it all works but in laravel there's a shorter way how to shorten this inside of the fridge into one eloquent statement there's a function called update or create so instead of all of those we can do one liner update or create and it has two parameters what to search by so mimicking this where statement and what fields to update in our case it's points this one so it will perform exactly the same thing so let's try it out actually so let's comment that out update or create and if we try to fill in the form so the first time let it be one two and three we save the points success is just a blank page we go back we refresh the values are here and in the database if we refresh the points table 1 2 and 3 are saved but for example let's imagine that 3 doesn't exist we delete that and then we save it again save the points success what happens now in the database that third line is inserted and the first two lines are not changed but another scenario let's delete that again and let's change that to four for example which means change the value save points and refresh and as you can see that line has been updated so updated add is different and points4 so this is what is done by update or create but there are two queries for each of the points for each of the array item and i installed laravel debug bar at the bottom so here's how many queries we have let's zoom it in a bit and the queries are here so select from points by user id there will be three selects at least one for each user basically and then if we do need to update or insert then there will be update or insert by individual record so in this case we have six queries well five because this is under auth so this doesn't count so five queries to the database and if we had for example 10 users there will be dozens of queries to the database so that's not an ideal scenario it would work but we can do it better and this is where we get to the original tweet to the absurd method it was submitted in larval 8.10 by a guy named parasmelhotra i hope i pronounced it correctly so this is the github and by the way shout out to a tool released by paris as well so as a thank you note for his release of laravel core function let's advertise his premium product enlighten and have a separate review of that so you can watch the video review of the tool that would auto check a lot of things in your laravel project so perez submitted that absurd method and in my tweet i claimed it that is not very readable at least to me personally what are the parameters what do they all mean but if you think about it it's logical first you have all the array so in our case all the points as they should be in the database so we would need to transform the original array and i'll show you that in a minute then the second array would be the fields that uniquely identify the record in our case it's user id and then the third array which fields to update or insert in case of record being changed so here's how it looks in our case in our code so instead of doing update or create for each of the records we go outside of the loop and perform one absurd for all the requests so here it is as i mentioned the array of data and we transform that then the key of unique record so what to update by and what to update with so the points would be the values to update and that points array needs to be in a specific structure so each points array item should be exactly as the database row so we transform the initial request points it cannot be just key and value of user id and points so i did good out for each maybe there's a shorter way to do that with collections or something like that so if you know a better way shoot in the comments below the suggestion i just transform it to user id and points and just in case i made it an integer because i saw it's coming as a string maybe it was not needed i'm not sure and now let's try it out let's comment this out and let's see the database query so we have our form i've zoomed it in and let's change this to 2 for example this to 5 and this leave s3 with save points and see the queries how many queries do you see one for the auth and then there's only one query for everything insert with values array and then the sql takes care of all the duplications on duplicate key update this is how absurd works under the hood now important thing is how masquel knows that it needs to check by user id on duplicate key means that the fields in the database table should be unique so for it to work you need to specify the unique fields so user id should be in the migration if we open the migration points unique here then mysql is saving that as a unique key and then uses that key in the duplicate key update this sentence so if you work with absurds make sure that you work with unique keys on the database level because with this case you make the database work so mysql or whatever database you use needs to know what are the unique keys if you want to find more tips about eloquent like this one you can check out one of my courses about eloquent one of them is about eloquent performance other one is how to structure databases and then there's older one but still relevant eloquent expert level or you can get all the 23 of my courses by subscribing to yearly membership and then you don't miss any new courses like for example in november just now i released a new course about graphql so go to laraveldaily.teachable.com to sign up and see you guys in other videos

Channel: Laravel Daily

Views: 8,647

Keywords:

Id: J8x268as2qo

Length: 7min 38sec (458 seconds)

Published: Sun Dec 05 2021