Laravel Course - If you don't know how to use leftJoin: optional 1-N polymorphic relationships
Hello everyone here I wanted to make a quick video in which I think you will find it interesting if you have not worked much with the join or with the leftjoin and others since most of the documentation always uses the join eh ignoring a little the others But obviously the others also have a certain well they have a certain role in all this so I have always wanted to do this type of exercises but in what is in the course of the basic Laravel or any of them as more than the developments that are done there are basic for here the redundancy could be a more advanced development But that would consume many hours and well for what I have always said that these in the end are courses that not many people buy and really the competition is a little null sometimes no no no I look a little beyond creating courses for example of 100 hours also seems like nonsense to me then a little all that between that the course would be very long to do a project a little higher caliber or that it could be done by an external course, that is to say another course parallel to the basic Arabic but a little the same in the end it is a little It is more difficult to consume because people go for the basic one and usually a small percentage goes for the other one. I never end up making these developments, at least to date, but this will change in the future.
This is my free development project which is the blog that also has the Academy application that little by little I am giving more functionalities and here I wanted to tell you something interesting here you can see that it is the Api part, that is to say in this case an api that I have there that is to be consumed through the application in Vue and it is for a part of the books that to date I do not sell the books there I sell the books on the Amazon website, LeanPub on gumRoad among others Apple and Google Play Google Book Apple Book and Google Book I think I said it well And now I am also going to make it local to the platform and so I can set prices a little more as who says friendly a little less for the commission that these platforms charge me and Well a little bit of gain for the client and also for me obviously so I am working a little on this and here I wanted to talk precisely to make that part of the join l join Well a little more interactive the matter So what is this This is the live application as I was telling you and here I have the list obviously as an example since this is Example Data of books one Well here Look, it's the image of Django and Livewire that we have here, so here you can see that the book that you have purchased here appears purchased and
here it appears purchased through PayPal and here nothing appears, therefore this user has not purchased this book, that's a bit where we're going with all this. Now I'm going to show you a little bit of what the schema is like, that is, what we have in the database level, we have the book. Obviously, it's to paint the basic information of the book, such as the image, the text and everything else. Well, here I also have other relationships, for example with post and that, but this is not so much the case, the important thing, and this for the files to download, the important thing is this one called file payment, which indicates whether the same was purchased or not. Therefore, it is a one-to-many relationship between file payment, it only has one book and the books can have many F payments, meaning that many users can buy this book, which I hope will happen, for example, then there is the relationship, therefore, this can be zero or n times and this will always be a one-to-one relationship, so this is why there is what I consider interesting. is what we are doing here here what I am doing is getting the list of all the books all the books that are published and that is why we see here well Select book here I placed the typical Select then Well I can explain this to you a little but it is not the case and here the posted Yes that is to say the traditional would be like this If we remove the join for a moment I think it is quite understandable it is a bit normal and this is a normal SELECT but well I placed the RAW one here because I want to give a format to a date that I have around here and place it here so that I can easily consume it from View it is not at all strange about the matter and it is not what I want to mention here the issue is the left joint as I was telling you this is a relationship that can appear zero or one times and this is the only list that I have here of the books that is to say here you can see the detail if you want to buy it and Well here is a small plugin that I have there and here the PayPal plugin will appear in this case if you have not bought the book so that you can buy it and this It would be good here I did not place the downloads part because It is a test file and I have not uploaded the fields, so the buttons to download the file would appear here or but here you can see that the behavior is different because here again the links to download it would appear that is a conditional that I made using Vue again it is not so important but what I wanted you to understand is that here we are passing the Fill payment Data which is where we are going with all this So what would happen if I use a join here instead of a left join as we are doing I am going to comment on all this because here is the answer to everything because obviously it is working but I want us to go seeing the exercise little by little if we place the joint which is the one we always use 99.99% of the times at least in the course what would happen in this case:
Book
::select('books.*')
->join('file_payments', 'books.id', '=', 'file_payments.file_paymentable_id')
->where('posted', 'yes')
->get()Well, let's see it in practice. If we come here, you will see that it only brings you a book. Because it brings you a blessed book, because of the type of relationship, the joins are excellent if the relationship exists. In this case, the file payment relationship, which is the one we are discussing. But remember that this relationship will only exist if the user buys the book. If he does not buy it, it does not exist. Therefore, this is a conditional. The book must obviously exist because it is the main relationship and the file payment must exist.
If the file payment doesn't exist, well if it doesn't exist this would simply not bring it and with this it also doesn't bring us the main relationship which would be the book, which is not what I want because again here I want to show all the books regardless of whether it has been purchased on the platform or not, therefore this is the disadvantage that we have here in the join. And in this case the rest of the joins are used or we can use them, which are the join, the left join, the right join and other variants. I mostly worked with the left join because I have never really used the right join but it is the inverse of what I am going to explain to you right now about the left join, what the hell does the left join do? Let's comment on this and I am going to comment on it here, I am going to leave it this way:
Book::select('books.*')
->leftjoin('file_payments', 'books.id', '=', 'file_payments.file_paymentable_id')
->where('posted', 'yes')
->get()We come back here and see that now both the one I bought and the one I didn't buy appear. So that's exactly what it does, it's what it's indicating, this relationship is optional, the dependency is the file payment, the secondary relationship you could say since the main one would obviously be the book here. So even if this relationship doesn't exist, it's going to bring the main relationship and that's why I can't access the payment data here and that's why none of this information appears here, which is what we're asking about here, since to paint this I'm asking about the file payment type relationship, which is the one that's not bringing up right now because it doesn't exist, so you could ask yourself. Well, but here the exercise already works perfectly. Well, no, because in the end we're asking about a File payment that's obviously associated with a user, the user who bought it, and that's why here you see this bunch of conditions that I have here, something that I can't place here, of course, I could place it down here, for example, here, indicate so that it brings me the file payment based on this relationship, and well, again, bring me the book with the File payment which also the File payment user ID corresponds to the authenticated user which is what I am placing here and this well This is a polymorphic type relationship that is handled here so the thing is more fun for me so what would happen is that it would not bring the other because it does not bring the other:
Book::select('books.*')
->leftjoin('file_payments', 'books.id', '=', 'file_payments.file_paymentable_id')
->where('posted', 'yes')
->where('file_payments.user_id', auth()->user()->id)
->where("file_paymentable_type", Book::class)
->get()Note that it is only bringing the one that I bought, it does not bring the other because this is a force relationship, here I am placing it again as if it were a join in practice because when indicating these filters that we have here I am indicating remember the order in which all this is executed and you could see it first it is making the connection with the perfect join but this acts as a kind of filter, a kind of filter once this operation is performed, therefore it will filter me and here the redundancy all all the books with their File payments relationship that do not meet this condition because if this does not exist it will not exist in the one that the user has not purchased. Then it will not bring it because the one that is here just Before, before doing the get, that is to say again, first the connection is made, we bring the book, we bring the relationship and after that, we have the two records in this case, these conditions would be applied, therefore one of them, the one that the user has not purchased, does not have the File payment relationship, that is to say, it would be null and automatically it does not bring anything and that is why we only see one in practice for us for this exercise in particular eye very important is working as if it were a join then here you can see a little bit the joke all this as I tell you this It is difficult for me to do it in the course Because we do not have a relationship so complex and well you can see that the practice works like this of course here it varies a little bit depending a little on the Data that you have and so on But that is what is happening then you say well nothing Then we are going to go a little bit to the donkey and we are going to indicate that it brings us either that the payment user ID is that of this user or that it is null and here that is why I put the or null in this case Remember that we are placing like a callback And this is so that in the query here we have anything in the we we Boot equal to a here we place an ant that would be when we place the we here and here we place these parentheses so that the relationship is executed first the conditions that we have here inside and thus we avoid having here Another condition and here or and Another condition and not and we do not know what it is going to return it is always better to place the parentheses here to place as who says what the relationship has to fulfill in this case would be the user ID equal to one or that it is null in what we are indicating here the user ID is more or less what I am placing here and here the same but with the type then I am going to remove
This let's see what happens I come back here I reload and I think it died to see why it died because it died because it died ah I am not placing the join or join relationship here:
Where and where or
And look it works again how perfect very good So what is the problem now what is the problem that I forgot Ah I think I remembered the problem and let me here Find the database that I don't know where I have the right em to see to see to see msql sql this is obviously an authenticated user over here you can see it in it would be user one I think I have two at least here okay I have these two users and here we have the one of Well here you can see a little number one and two I am working with user one which would be the one I put here admin we are going to go to the file payment relationship if I find it here it is and…
Polymorphic relationship
In case you're wondering I called it that not directly Boot because precisely now with the subject of the courses I placed the courses That obviously I already have this whole issue of payments and now I wanted to do the one of books but the one of the courses I called it here directly for the Well I called it tutorials as equivalent then the relationship directly to the courses and What happens when I want to do the one of books I would have to copy and paste everything and What happens if later I want to sell other than little things in Blender then I have to copy and paste and do all those relationships again When can I take advantage of the polymorphic relationships here as you have seen up to this point and simply with the same ones I am varying Which is the which is the model class because in the end the structure of the purchase is exactly the same we have a resource that we want to buy that is a relationship. Aside from whether it's books, courses, Blender, or anything else, whether it's animals, whatever you want, and an F payment type relationship where the payment relationship is located, you understand the user ID, you understand what the payment method is. At the moment it's PayPal, but later I'm going to put the stripe one as well, since the blessed one, eh, what is it called here, the MercadoLibre one that is used a lot here? Well, it's blocked for Venezuela. So no, I can't open an account, so to hell with the ID, the price, and everything else, it's the same regardless of what you want to buy, so that's why I preferred to make one for a polymorphic type relationship, and that's why you see here that I use the book one, since in the end there are certain different things because the treatment of a course is different from the treatment of a book and I have to create the specific resource here, well, that was in case you're wondering.
Well here we have the relationship and notice that it is user one as you can see I am going to put user 2 which is the one that has in this case that you ask yourself that here I changed it I think it changed it to see if it updated file payments Here it is I come back here and no it does not have it I think this would also work But there was something now I do not remember what it was Ah precisely because it does not have it that is the problem I mean this would be worse than the previous solution suppose that all the good that I hope happens So several users buy several courses I mean several books Then they do not appear directly I mean…
When a new user comes to see the books here none will appear because if this book had also been bought from another user at the moment you can see that he has not bought it what I mean in the test data we only have one f payment which is for book one the one for dango and that is why it does not appear because now We are asking Notice what we are doing here is asking if there is a fail payment for me for user one there is not because I changed it for the user two for example or that is null none of these are met therefore it is no longer brought then it is even worse because we are not even listing a book that again remember that this is for the user to buy but if I am not listing it Because someone bought it it does not make sense to me Then it does not work either then in the end how can you conclude doing these filters at this level does not make sense maybe we could place another we but you are already seeing that this is going to hell just for this and in the end you say Hey I better bring all the books in another query the payments for the books and I do a match which is a pain to do that through the for in Vue then you can see that this does not make sense then here you could consider it is not better that I can place those filters directly in the join which is exactly where we have to place them since if we place it at this level for the fourth time I think I have commented on it what it does is bring the main relationship brings the secondary relationship if it exists and then we apply these filters on all that Data that we already have and that is the problem precisely This then that is why here neither this should be used for this particular case and we should use it here, which is what I had here originally. I'm going to comment this and I'm going to uncomment it here, what are we doing here? Here we are bringing again, it is the same thing that we have here, it is similar to the we, only in this case it is applied left joint in which we are placing a closure and here we are placing what is the first relationship, which is to ask for the Book ID, which I did not explain to you before. Here we also have it, it is the same, the Book ID, so I don't know how to pronounce this, I'm sorry, but this one. Remember that it is the field of the polymorphic relationship. That was the name that I gave it, I don't know if it's correct, but oh well, I think that the syntax follows, but the devil has already gone. This has to match what was commented before because like this a polymorphic relationship works in which we place a field eh of integer type to represent what would be the fk in a one-to-many relationship in this case but in this case as who says polymorphic and hence the name Because it can be anything in this case I'm only using it for the book and that's why I ask so much here for the type that is the Bot Class type that Remember that it is this little thing that is saved here is this here The Fill payment here the model would appear and here the identifier of that particular resource would appear, that is to say this would be the book because it is what we are placing here with an identifier of one, what is it that I have here then here but the final thing is to ask about the yes if it were a normal relationship, that is to say not polymorphic, there we would be placing the Book ID equal to File, the Book ID equal to the file payment book ID but again all of this is using polymorphic relationships.
Conditions by closing the leftjoin
Here we would remove this so if we do it this way the filter we are doing is locally in the join for the file payment type relationship and we are not affecting the main one which would be the book one that we are affecting with these at this level then here we can place as we want here we place as I told you what is the initial connection through join Well it would be only this and over here all the additional filters that we want to place which is the file payment user ID that corresponds to the authenticated user and here I will comment on it Well this so that it is more organized I could place it up here so if it is better understood here this and at the end this other little thing here that if the user has returned the book but well really for this it does nothing because no no it does nothing I do not have it implemented but it is there So now you can see what is wrong and everyone is happy:
Book::select('books.*')
->leftJoin('file_payments', function ($leftJoin) {
$leftJoin
->on('books.id', 'file_payments.file_paymentable_id')
->where("file_paymentable_type", Book::class)
->where('file_payments.user_id', auth()->user()->id)
->where('file_payments.unenroll');
})
->where('posted', 'yes')
->get()Here I ate the semicolon and here you can see that it works and here now to see what happened to my Data hell Ah sure right I changed it sorry It's that one gets tangled up remember that right now that user has not bought anything but here they both appear and therefore if I assign the file payment again to user one which is the one I have authenticated here the payment information will appear So finally by the gods I was able to make this connection in which I simply wanted to get the entire list of books in this case that I have published that S has to be kept here obviously because the books have to be published if they are not published So I'm not going to put it up for sale but what is the detail of what is the secondary relationship must be managed which is the moral of all this the conditions of the secondary relationship which in this case is this if you have if you have conditions like these we have to manage it is locally to the join and we cannot place it globally to what would be the query and we have already seen an example of why this several examples of why this so it was a little bit what I wanted to tell you here and also the use of the left joint that as you could see here we cannot place the joint because if not here we are placing this by force that if it does not comply with this Then it will not return anything and with that it kills the book as well, which is something we do not want and that is why we use the f join in case you are wondering And what the hell does the right join do well exactly the opposite if the book does not exist in this case and here we have by chance life some payment which we cannot do because of the type of relationship right now but suppose that here We have here I only have two books and a payment suppose that I have here a book that is, I say a payment that is for book 3 that does not exist Then that would also bring them to the list therefore what it does is the inverse if by if this what it does is return the main relationship regardless of whether it has a secondary relationship or not the right join what it does is return the secondary relationship regardless of whether we have the main relationship I have not really used it but well it exists there in case it is necessary according to the model of data that you are working on your business logic, which is the important thing, eh, it can be useful to you, but really I think that first join, then left join, and then the rest would be there, which I really have not used much in my life, so well, the video of the account was a little longer, but I hope that this exercise has been useful to you, so nothing, see you in another video.
- Andrés Cruz
Develop with Laravel, Django, Flask, CodeIgniter, HTML5, CSS3, MySQL, JavaScript, Vue, Android, iOS, Flutter