Latest / The Laravel Podcast / Eloquent and the Query Builder, with Jonathan Reinink

Transcript

Eloquent and the Query Builder, with Jonathan Reinink · The Laravel Podcast

• Matt Stauffer: Welcome back to Laravel Podcast season four, today we are talking to Jonathan Reinink, known for many things, but the primary one today is being the master of eloquent query performance and all that sort of stuff. Stay tuned. Matt Stauffer: Welcome back to the Laravel podcast, season four today. I'm talking with the man, the mystery, the legend Jonathan Reinink, who's known for so many things I won't even begin to list them off, but the latest incarnation of his reputation comes from being the database guy, the eloquent guy in the Laravel community. And Jonathan has been sending out a lot of tweets and recently released a course, which we'll link in the show notes, of course, talking about how to use eloquent, not just using it, but also thinking about some of the more common challenges that we run into using eloquent as it comes to optimizing speeds, and what things do our eloquent queries get stuck on and all that sort of stuff. Matt Stauffer: But that's not beginner level stuff. If you are an experienced Laravel developer and have been using eloquent, I would say definitely check out Jonathan's course, check out his tweets because he's been putting out so much real useful stuff in terms of optimizations. But first we're just going to talk about eloquent itself. So before we go there, Jonathan, I gave you a quick intro, but can you just say hi to the people and just let us know who you are and when you meet somebody, how you actually tell them what you do? Jonathan Reinink: Yeah. Hey, thanks for having me on the podcast, Matt. Yeah. So I've been involved in the Laravel community for quite some time and been involved in open source for quite a while. Early on, I was involved in the PHP league. Had a couple of packages that I put out, that was where I got started with open source and then I helped Adam Webbin with the initial launch of Tailwind CSS so that was the big project I got involved in and more recently I've moved to a JavaScript project, which is inertia JS, which is a JavaScript tool that basically lets you quickly build single page applications using react view and Svelt but building them more in the classic server side, running and controllers sort of approach that you would with a classic monolith Laravel application, meaning you don't need an API or any of that. Jonathan Reinink: So that's the other big project that I've been working on recently. And then over the last few years, I've always had a real strong interest in databases and how databases can affect the performance of your applications and the critical role really, that they play in the performance of our applications. It's not always the most sexy topic. I think there's other things that are more interesting that we like to talk about. I see a lot more people talking about little silly things like rope caching or other little micro optimization, stuff like that that are easy, but really don't result in significant performance changes to your application. Whereas the database is, the vast majority of applications that we right now days, interact with some type of database and just knowing your database, knowing what's going on between Laravel and the database and just learning some of the basics, can just have such an enormous impact on the performance of your applications. Jonathan Reinink: So that's been an area of focus for me. I've spoken at a couple Lara-cons about it as you know, and then yeah, I launched the course in June all about, an advanced level course called eloquent performance patterns, which really deep dives into all this stuff. So yeah, it's just something I really enjoy talking about. So if you follow me on Twitter, you'll notice that I often share crazy database queries type things and they tend to be done all using eloquent. Matt Stauffer: Yeah, it's that perfect intro- Jonathan Reinink: So that's a long introduction. Matt Stauffer: No, it was perfect because... so let's talk about a few things. I think the second thing we'll talk about is what is eloquent, what's Laravel's query builder and all kind of stuff. But I think the first thing I want to ask is what is it that drew you, I don't think that a lot of people get into a database just because they write apps, just because they're building them. So what aspect of your particular experiences do you think drew you into database performance optimization? Jonathan Reinink: Yeah, I think, so I always tell people when I first started doing web development, I really didn't understand where the lines started and stopped between each piece of technology. So I remember a buddy teaching me on my very first application that I built, which was actually a project for a guy I knew who sold wood products and he wanted a website where he could post these wood products, post pictures, and people could find them and call them to buy them, right? We're talking literally late nineties, early two thousands. Matt Stauffer: Okay, yeah. Jonathan Reinink: And so I built this app, my very, very first PHP, very first web project was a PHP, my SQL application, and I didn't know really at that time what parts were PHP, which parts were HTML. Matt Stauffer: Right. Jonathan Reinink: There was really, CSS was almost nonexistent back then, right? And also my SQL. So I just remember just feeling super, super empowered. This idea that some other user that's totally not technically inclined whatsoever can go into an administrative control panel that I've created. They can add products and that's going to dynamically update this public website that the rest of the world can see. I just remember being so thrilled with how that whole system works. And so yeah, fast forward to today, it's really the same thing. I'm still building applications that are driven by a database that are out the web. Jonathan Reinink: And maybe it's just the type of work that I've run into, but I suspect it's the type of work that most of us run into, there's always this database involved and I'm kind of just, I hate slow apps. I really just hate slow. So like anybody who's been in the business long enough, you learn over time what things matter and what things don't really matter. And I guess for me, because I think I started really quite early on, I had to learn some foundational stuff, some SQL skills that I think maybe people who are entering into the web development more, even today, or within the last 10 years, you don't need to know nearly as much SQL as you did back then because we didn't really have ORMs back then, so I was writing raw SQL. So I had to learn how to do that, which was really, it was terrible compared to what we have today. Tools like eloquent are just, they're amazing. Matt Stauffer: Yeah. Jonathan Reinink: But it did give me a set of skills that I know now, a little closer to the metal when it comes to the database stuff. And so as I transitioned from that to using an ORM and using eloquent, I kind of always had this desire to do the things that I knew that I could do in raw SQL, but do them in the eloquent instead. So for me, that was kind of the transition. As opposed to somebody who's maybe learning eloquent to start and then have to go back and learn the SQL, mine went the other way around. And that pushed me to do stuff maybe that the next person wouldn't even try, because they don't necessarily know that they even can, if that makes sense. Matt Stauffer: Yeah, totally. And then for anybody who's listening to this who's not familiar with SQL or relational databases, we're going to go in with the assumption that you are. So you might want to hit pause right now and just go watch some five minute primer on those. But the primary folks in the Laravel community do tend to use my SQL, and anything we're talking about here will be 95% applicable to Postgres or to Microsoft Sequel. But we are going to be talking in the context of my SQL today. So assuming that everybody understands what my SQL is and what SQL is in general, can you imagine you were describing to a five-year-old what eloquent is and what would be like to describe it? Jonathan Reinink: Yeah. So it's been fun listening to the other episodes, seeing other people's struggle through this question. So I have put some thoughts down on this. So pretending like I'm five years old here. So imagine, you have a webpage which lists all your favorite TV shows. Okay, shows like Peppa Pig and Paw Patrol and The Wiggles and all that good stuff. Stuff that you watch in the morning while you're eating your Froot Loops. And this particular includes a bunch of extra information about these shows as well. Like maybe where they're playing. Are they on Netflix? Are they on Prime? Are they on cable? And maybe some reviews from other kids who are also watching these TV shows. But here's the thing, all this information that's showing up on this TV show website, it's not on the website itself, it's up in the cloud somewhere in something known as a database and a database is really just a big file that contains a whole bunch of information. And for our example, that means that somewhere in the cloud, somewhere in this file, somewhere in this database, it tells us that Sally likes rubble best on Paw Patrol and says that I like Paw Patrol or The Wiggles the best or whatever, right? Jonathan Reinink: And so all this information is up in the cloud somewhere in this fancy database, but somehow we need to get the information from our database to display on our website and that's really where eloquent comes in. Eloquent is like this magical little tool that sits between your website and your database up in the cloud that helps you get information out of it and display it on your website and not only that, it can actually also put information into that cloud, into that database as well. So for example, when some kid leaves a glowing review and the latest Peppa Pig episode, that doesn't get saved in the webpage, it gets saved up in this cloud, in this database. And eloquent makes all that happen, it's the glue that ties the two pieces together. Matt Stauffer: Wow. I felt like I was five and I was... I was thinking, my daughter is three and my son is seven. My seven year old son would have got that, no question. My daughters was three would have been with you until you said the cloud, and then she would have been just looking straight up. So I think a five-year-old probably would have been able to handle that just right. So I'm very impressed, man, nice work. That's great. So one of the things I often do, because sometimes, I love that people can simplify it down for five year old, but sometimes that's not enough information for the actual target audience here. So let's imagine that you were talking to somebody who is a JavaScript programmer who understands how databases work and maybe has connected to them in Node or something like that. How would you describe to them what eloquent is? What's an ORM and what is eloquent and how is it different and similar to other ORMs and stuff like that. Jonathan Reinink: Yeah, right. Okay, so I'd probably start the next level, I would start by saying that eloquent is really just a tool that lets you get information from a database and display it on your website, or it's a tool that lets you put information into a database from your server side, language of choice. Getting more into the weeds of what eloquent is specifically, eloquent is a database ORM that's included with Laravel. So an ORM stands for a, I believe it stands for object relational mapping. And that basically just means that it maps some data from your database into objects in PHP and these ORMs exist in basically all sorts, all server side languages tend to have these things like JavaScript, Node, PHP, rails, this is a very common thing to have them. And it's just basically a tool that connects your server side language with a database. And actually nowadays it doesn't even only have to be server-side, we're now seeing, ORMs even client side with tools like Firebase and stuff so we can actually go client side straight to a database. Jonathan Reinink: Anyway, so this tool allows you to connect to the database, get data and update it but all the while, at least in eloquent, you don't do that and you don't interact with your database using some database object, but rather you do it using models instead. So you don't start by saying, new database and then run an SQL query, pass it some data to put in or whatever. With eloquent, eloquent's built using a pattern called active record. And with active record, you basically have models in your application and a model is just a class, a PHP class, and that model represents a table within your database. Jonathan Reinink: So real practically, just imagine you have a database, relational database, let's call it a my SQL database. And in that database you have a users table. So that would mean that in Laravel, using eloquent, you would have a model, a user model. So just a class in Laravel, a class in PHP called user, and it would extend eloquent basically. So it implements eloquent that model as the base class, which enables all this interesting functionality. And then in order to get some data, to put some data in the user table or to get some data from the user table, you would actually use that user class to do that instead of again, using some database classes to do that. So I'm going to try to walk you through what a simple database query, maybe two simple database queries, what they would look like. Matt Stauffer: Okay. Jonathan Reinink: So let's start by saying we want to create a new user in the database. Matt Stauffer: Okay. Jonathan Reinink: So what you would do is you would call a user, so the user class, and then you would call statically on it, the create method. So user colon, colon create that method and then you would pass it an array of values and each value has a corresponding key. The key, so for example, first underscore name and maybe last underscore name, those are called attributes and those attributes correspond to the columns in the user's database table. Matt Stauffer: Right. Jonathan Reinink: So anyway, so you have this user create method, you're passing an array. It has these keys for the different column names and then the values in that array are whatever value you want to enter into the database table. So for instance, Matt Stauffer, and you basically just run that and it immediately inserts a row into the users table. So that's really, really simple. A really simple API that you can read it. It's just user create, and here's the data. Matt Stauffer: Right. Jonathan Reinink: It's really, really nice and simple to use. And that was really for me, when I first started looking at active record. I remember active record in the CodeIgniter days, that's really where I became most familiar with it. And I knew it a little bit from rails, but Laravel is really what made me fall in love with it. It's just this really simple, expressive syntax that that's wonderful to work with. That's why I think eloquent, it's called eloquent because it is just such an eloquent way of working with a database. Jonathan Reinink: So that's how you would put some data into the database and then getting data is equally simple. So again, imagine we want to get, let's say we want to get some data, you know what, let's start by getting all the user records. So every single record from the user's table. You could literally say, user, so create a variable users and say equals, and then again, call the user model, the user class, colon colon, and just say all and call that method and you now, just like that, have every single record, every single row from the entire users table in this user's variable. Typically you don't do that. More likely you're going to have some sort of where conditions. So this is where it starts getting really interesting. Jonathan Reinink: So you can start adding some where conditions and other features of regular SQL queries and you can append those onto this statement, this user statement. So for instance, you'd maybe say user colon, colon, and then say where, and maybe you're looking for your active users. So you give it two values. The first argument is active. So that's the name of the column that you want to look up. And the second argument is the value you want to find. So you want to find all the users that are active, you'd say true for the second argument. And then you would append on, so you chain onto that, get method call. Jonathan Reinink: So hopefully this isn't too much to explain in a podcast, but the whole, all of eloquent is designed to be chained. So when you say user where active equals true, it then, when you run that where method on the user object, it then adds that where conditioned under the hood and then returns back to you an instance of the query builder, which you can then add more stuff on. So you could say where active is true and then say, or where something else and build up these complex queries. And then at the end, you just append get as the last method call and then it'll actually execute that query. So when you call a user, like I said at the beginning, user all, that's like a shortcut for user get. So yeah, it's just a really, really nice expressive way of working with your database. Jonathan Reinink: And then maybe to just wrap up the intro on eloquent. It's not only this really nice expressive, fluent API, it also has the concept of relationships, which is I think the other really big part of eloquent and the act of record pattern. So within any app you typically have, each one of your database tables and each one of these models within your application, they tend not to be islands on their own. You often have data that's related to each other. So for example, you might have a user that has many friends, or you might have a user that belongs to a company, or you might have a product that belongs to many categories. So relationships in eloquent make it really easy to access related data in a super expressive way. Jonathan Reinink: So for instance, if I were to get, say I'm working with a user record, imagine I'm going to explain some code here again, so work with me. So imagine we have an instance of a user record. What you can do is you could say user and then access a property on that user object. And we'll say in this case, friends. So you say user dash friends, and now it's going to, behind the scenes, eloquent's going to go off to the database. It's going to say, okay, we're looking for all the friends for this particular user that we're working with right now. If it has the friends loaded already in memory, it's going to return them back to you right away. But if it doesn't have their friends loaded in memory, it's going to say, okay, well we need the friends and we know where to find them, they're off in the friend table, and it's going to go and get those records and then return them back to you. Jonathan Reinink: So what it means practically is in your PHP code, in your Laravel code, you're writing this really nice expressive code. You're saying user, you already have a user in memory. You're saying user, give me the friends for this user. Or you're saying, I have a company, give me all the users for this company. Or maybe you're looking at a product category, go and get me all the products for that category, and it just makes it really, really, really nice to work with your database in this way. Matt Stauffer: Yeah, that was so good. I've got a couple of notes going backwards, the most recent one. So notice that all the previous times, for those of you who are learning via audio, all the previous times, he was calling things on that user class. So it would be capital U, User, colon, colon, whatever, get are all aware. But this most recent one where he's talking about friends, he said it was on an instance. And so just in case you're still growing in your OOP experience, an instance means that's not going to be the user class, that's going to be a specific user that has already been returned by a previous one. So that's going to be something like, if you imagine you wanted the friends of a user, what would happen is your first line would be likely something like dollar sign user, So a user variable, equals something. Something that gives you a single user back. User, colon, colon first or something like that. And then on the next line, now that you have a single user represented as an object, then you can call things like arrow friends on it to get the friends off of it. Matt Stauffer: And that brings into play a little bit of what he's talking about earlier about the active record. And I actually just went back and relisten to the object oriented programming episode with Alena Holligan. if you have not listened to that one, especially if you're new to OOP, I'd recommend again, hit pause and go listen to that one because a lot of things are going to be relevant here to the language we're using. But one of the things we talked about is that you can in Laravel, when you're in PHP, when you're writing methods or looking at data, you can be calling on an individual instance like Jonathan, or you can be calling it on the class, like user. And so when you're calling methods in the class, those are static methods with the two colons. When you're calling into the user itself, you're doing the arrow. Matt Stauffer: And that's helpful both for us to understand where we already given any given moment, but that's also a really helpful aspect of understanding active record, unlike some other database patterns. If you've worked with databases before and other database record, things that are not active record, this will be really relevant for you to understand it. With active record, and therefore with eloquent, the same class describes both the tooling that you use to get data out of your database and save it into your database, so persistence, and that class also represents individual instances of that data. And that's different than CodeIgniter and that's different from... CodeIgniter called itself active record, but it wasn't really, but it also is different from a lot of things that don't identify themselves as active record. Matt Stauffer: So just to note, the user class, you call static methods on that to get data, but you can also then get an instance of the user class that represents that object and the user class can delete or modify or save itself. And so it's all really bundled together into one and that's a very specifically active record thing. And I think that context really fleshes out what was a really, really great introduction, Jonathan. So I really appreciate that. Jonathan Reinink: Yeah, no great, great comments. It's sometimes hard to describe this stuff from a beginner's perspective after you've been working with it for so long, but yeah, that's very helpful. That's really where the name active record comes from, right? Because you're working with this record that you can now make changes to directly. Matt Stauffer: Yeah. So normally at this point, I'm going to ask you the question about when when's the last time you use the system. But I think that one's pretty obvious, but I was going to say, I think that there's a little bit further we can dig into there that is the query builder. So I'm going to ask Jonathan to explain it, but again, there's one more reference back to the Alena Holligan object oriented programming episode. Remember I mentioned that if you do something where every method returns this, you could do this thing called fluent chaining, where it would be something like, I think the way I described it was basically you use fluent chaining when the thing that you start with is not complete and you use fluent chaining to modify the thing. So I think at that point we said something like, you have an animal and so you'd have whatever, blah, blah, blah equals new dog, and then on blah, blah, blah, you'd also run set name and then set color and set whatever. And so fluent chaining is the idea that every method you run, like Jonathan mentioned, returns this, so you can keep running more methods on it. Matt Stauffer: Like Jonathan mentioned, returns this, so you can keep running more methods on it. There's some times when that's not a good fit, but it makes the most sense when you're building something. And I love that ... Jonathan, just, you just pointed out the idea that we're building a database query. And so when you're doing that DB colon colon, so that, that user colon, colon, whatever, until you hit get or whatever else, you know, delete or whatever, you're just building a query. And so that means you can add a "where" and you can add a "where not", and you can add a "where in" and all these other things that y'all learn about later, but all these modifiers, what you're doing is bit by bit by bit building up a database query until you actually execute it. So could you, Jonathan talk to us just a tiny bit about what the database query builder is in Laravel and how it relates to Eloquent? Jonathan Reinink: Yeah, that's actually a great question because I think for myself even, I didn't really understand kind of where one piece started and the other piece started before. So when you're working with Eloquent, it's important to realize that Eloquent is actually still just a layer on top of what's called the query builder in Laravel. And I'm just going to pull up the documentation, because I think even the documentation in Laravel is organized in this way. So if you look at the docs, you'll see the Eloquent ORM but that actually falls under database. So even the documentation orders organizes these things as two separate things. Matt Stauffer: Yeah. Jonathan Reinink: So I think that's helpful to realize because the, the query builder is actually the thing that's doing the bulk of the heavy lifting in Laravel, and Eloquent in a way is kind of just this light layer on top of it. Jonathan Reinink: That makes it really nice to work with these models, these records in kind of like this, the active record style way. So if you're the type of person that really doesn't even like active record, there's literally nothing stopping you from even just use using the query builder directly in Laravel and not even using the active record functionality. So this is kind of how it works, basically when you call ... Going back to say this user example. So when you say user, and then you call the where method because you want to start a new query, what Laravel is doing under the hood is it;s saying, "Okay, you called where on the user object, we know that you now want to start making a query." So what it does is it actually creates an instance of the query builder behind the scenes. Jonathan Reinink: And really from that point forward, you're not working with the user model directly anymore. You're actually working with the query builder from that point on. So this is really, really helpful to know, because anything that's possible with the query builder, you can now do that on that active record model that you're working with. And sometimes it even makes sense to just use the query builder directly and not use Eloquent at all. Jonathan Reinink: So one good example of this is if you're doing some sort of like reporting layer, right? Maybe you have a report somewhere in your app that pulls in some data from the user table. And then you join in some data from another table and do some calculations on that and whatever else. What you get back, and basically what you're doing is you're selecting a bunch of custom columns from maybe a couple of different database tables, what you're getting back at that point isn't really a user record anymore. It's kind of like this custom record that you've just kind of come up with that makes sense for your particular query. Jonathan Reinink: So when you get that record back, it doesn't really make sense to get that back as a user model anymore, because what you've created, what you've gotten back from the database, isn't really a user record. It's kind of a bunch of this other data. Anyway, so what you can do is in those situations, you can actually just work with the query builder directly. And to do that, you do instead of putting user colon, colon, you do DB colon, colon, and then it just runs a raw query. I say raw, what I mean by that, it's not an active record query. Jonathan Reinink: And the end result is essentially you're going to get the same kind of database query. Basically, it's going to run the exact same kind of query under the hood because it's all using the query builder. But what you get back gets handled differently. And this is the important piece. Either instance, you're going to get a collection of data back. But the difference is when you're working with active record, you're going to get a collection of user records back. So Laravel is going to go off ... When you do say user colon, colon, all, it's going to get all the user records. It's going to get those data ... those records back from the users table. It's going to convert them all into eloquent models, so you have an instance of every one of those rows as a user model. And then it's going to give them all back to you as a collection. Jonathan Reinink: However, when you're working with the query builder directly, you're not going to get a collection of user models back. You're just going to get a plain collection of array values. So you'll get, there will be no models involved at all. And that's often in those situations more desirable because you don't want to work with an active record because you kind of have this custom report query that you're running. Matt Stauffer: Right. Jonathan Reinink: But I think understanding that Eloquent is built on top of the query builder is super useful because it gives you a sense of what you can all do with Eloquent. And what I'll often do is even if I don't want the returned response back to be a collection of user models or whatever model I'm working with, you can still start by writing it as a user query. And then before you call the get method on a, to actually get the value, you can call To Base, which a method on the Eloquent, I'm just going to double check that I have that, right? Yeah. It's called "To Base" and the To Base method will actually convert it to just a generic, plain query builder instance. So sometimes that can even make sense to do that. Yeah. Does that help explain the query builder a little bit better? Matt Stauffer: Yeah, I think that's great. Jonathan Reinink: Maybe ... Yeah okay. Matt Stauffer: No, I think that's really good. And I was, it's funny because I was thinking it's get query, but they're not the same. So this is a little bit of- Jonathan Reinink: No- Matt Stauffer: 201, but I- Jonathan Reinink: ... Important difference. Matt Stauffer: ... Yeah, what's the difference? Jonathan Reinink: Yeah. So it's very simple, but it's an important difference. Get query will just give you the raw query, which sometimes is what you want. However, to query, I'm just going to verify or to base it applies any scopes that you have. And we should probably talk about scopes before this podcast is over, but scopes. And that can be really important depending on what you're using. For example, if you use soft deletes, you need to base, otherwise you're going to get deleted records included- Matt Stauffer: That's good. Jonathan Reinink: ... Because soft deletes use scopes under the hood. So that's why depending on what you're trying to do, you just got to ask yourself when I convert this to a base query builder object, do I want Laravel to apply any of the scopes or do I not want to? Matt Stauffer: That's good. Jonathan Reinink: And that's really, that's really like your global scopes. Right? Matt Stauffer: Right. So I think this is a perfect time to talk about scopes. So here's what I think our next good thing is. And then we're going to move on to our normal, what are common challenges and gotchas and any other miscellaneous stuff. But I think that in order to define Eloquent well, we need to at least cover one other thing here. And I think what it is going to be for me is going to be basically ... How do I say it ... When you're defining an Eloquent class, can we talk a little bit about what the various things are going to be on that class? You mentioned relationships. Jonathan Reinink: Yeah. Matt Stauffer: So in and Eloquent class, I can define its relationships. What other things will I be defining on that class? Jonathan Reinink: Yeah. Great. So let me just ... So the big ones are ... We'll start by simple, and then we'll kind of move up from there. I'm just looking at some code here as we go. So one of the things you can define, really, really simple ... So by default, Laravel ... I think it's always helpful to kind of use an example. So let's keep working with this user object. So by default, when you create a user object, a user model in Laravel, Laravel automatically tries to figure out the table name that that user object belongs to. And it just uses the class name for this. So if our class name is called "User," it pluralizes it and says, well, we're going to guess that the table name for this is "Users." Matt Stauffer: Yeah. Jonathan Reinink: Right? And if you do the same thing with products, and even if you have like stuff that has like underscores and it does fancy proper title case format to snake case formatting, it kind of handles all that. However, if you have a situation where Laravel doesn't do the translation from the model class name to the table name properly, you can assign a property on that model ... Or sorry on that, that class. Matt Stauffer: Class. Jonathan Reinink: Yeah. That says what the table name is. If you didn't call it a table, well, if the table wasn't called "Users" for you, but it was called "BFFs Forever," you could just put that in there instead, and then from that point on Laravel's going to use that table definition moving forward. Okay. So that's one thing. Jonathan Reinink: Another really neat feature is casting. So I'll just try to keep this simple again, but imagine you have a table ... So you got your user's table and in that table, you have a date, let's use a date. So maybe it's your birth date. So by default, when you go off to the database and get that record, that user record back, it's going to give you the first name, which is a string, and it's going to be your last name, which is a string, and your email address, which is a string. But then it's also going to give you a birth date and your birthday is a string as well, because that's just the way it comes back from the database. That's kind of some deeper under the hood stuff related to how PDO works, but you're going to get it back as a string in PHP. That's the point. Matt Stauffer: Right. Jonathan Reinink: So what you can do in Laravel, within your user model, you can define this cast property, which takes away a key value array where you can say a column name. So in this case, "birth date." So you can say, "Whenever birth date ... Whenever I pull a new record, a user record from the database, automatically convert the birth date from a string to some date object." So you can do that. You can use the built in date objects that come in PHP, which makes it way, way nicer to work with the date, right? Because you can then convert it to various different formats. You can do operations on it to figure out kind of all the stuff related to dates. So it's just, it's nicer to work with, like that object oriented date object, as opposed to just working with it as a string. And Laravel actually uses a really, really fantastic library called Carbon, which is built on top of PHP's datetime functionality, which adds a whole bunch of convenient methods on top of it. Jonathan Reinink: Anyway, so what you can do is you can say "birth_date," that column automatically casts that to a datetime object, which Laravel's going to then convert to a carbon instance. So that's really, really nice. You can do this for Booleans. You can do this for integers. You can do this, even for JSON columns. If you have a column in your database that's JSON, it'll automatically convert that either to an array, just a plain PHP array, or it can even convert it to a collection. And I think even just a standard, kind of like a standard object as well. Matt Stauffer: Yeah. Jonathan Reinink: So that's cast, that's kind of like the next thing that kind of comes to mind. Another thing, and then is relationships. So I talked briefly about relationships. I'm not going to get too deep into this because this is kind of like, there's a lot to relationships- Matt Stauffer: No kidding. Jonathan Reinink: ... but the simple. Yeah, so the simple way you implement a relationship ... So let's say we have ... Let's do user and company. So imagine you have a system where you have a users table, but then you also have a companies table. And every single user in that system belongs to one company. So maybe in your users table, every single record has a company_ID. And that is the ID, the primary key from the company's table. So what you can do is within your user model in Laravel, you can define a relationship, and you do this by creating a method within your Eloquent model. So for this, in this instance, you would do that by creating a company method, and that company method would have one little line in it. It would say "return, this belongs to," so this being the current model, user model "belongs to," which is the name ... "belongs to" being one of the types of relationships that Laravel supports. And then you pass to the belongs to method, whatever it is that it belongs to. And in this case, in case it's the company. Jonathan Reinink: So that said simple little ... It's four simple lines of code that is ... it's very standard Eloquent sort of stuff to do, that four lines of code then from that point on now connects your user model to your company model, and then you can do all kinds of interesting things with that. It makes it kind of like what I was talking about earlier. If you have an instance of a user model, you can say, well, give me the company for it. And you can do other things too, like eager load all the company records, which is maybe too advanced for this particular podcast, but it's got a ton of benefits kind of, these relationships. Jonathan Reinink: And there's a bunch of different types of relationships. So you have "belongs to," you have "belongs to many," so maybe a user can belong to many companies, not just one company. You can have those, a company has many users. So it's kind of like the, almost like the inverse of the user to company, a company in this case would have many users that belongs to it. And yeah, so there's a bunch of relationship types kind of depending on the database design. So that's relationships. Jonathan Reinink: And then I think the last big thing to talk about when it comes to Eloquent models is scopes. And I think it's kind of like your explanation from earlier is helpful. When you're dealing with Eloquent, you're dealing with these models, so these active records, right? So when you have an instance, that's kind of like ... Yeah, how do I put this? Jonathan Reinink: When you have a specific instance of an Eloquent model, such as a specific user, you have a set of functionality that you can call on it. Like update or delete or whatever, right? And that's on a specific instance. The scopes aren't used when you're working with a specific instance. A scope is really used when you're working on just the user model itself before it's actually been turned into an instance, and the scopes really help you build your database queries. So scopes don't add any functionality in and of themselves. What they are is pre-packaged chunks of query builder code that you can put into a scope method within your Eloquent model, that then you can then reuse throughout your application. So let me give you an example, because that's again, probably going to make this easier to ... yeah. So I'm trying to think in my own app, what's a good ... Jonathan Reinink: Oh, I have one. I know one. So in my particular, one of the projects that I'm working on, in the user table, there is a column that dictates whether that user is allowed to log into the system or not. And that column, it's just a Boolean that's called can_login. So what I could do is anytime I'm now looking for the users in my system that are able to log in, I could write some query, like some Eloquent code that goes along this lines: You could say user, upper case U-S-E-R, colon, colon, where can log in, that being the column name in the database Users Table, and then pass it the value "true." Because I only want the users who can log in and then call get on that. Jonathan Reinink: But the problem is ... it's not really a problem per se, but what you're kind of doing now is you're really leaking these specifics about how your database itself is designed and the name of the column, you're leaking this elsewhere in your application. Which, to be honest, a lot of times it's fine. But the problem is you've kind of got to remember that. Jonathan Reinink: And sometimes it's a little more complicated than just checking it to see if one column is set to true or not. A lot of times it's much more complicated than that. So what you can do is you can create scopes, which encapsulates, basically take this a little bit of query builder code and puts it into a reusable method. So imagine on our user model, we could then like we did ... We create a method just like we did for the relationship, but this time for a scope, you would say "public function" and you'd give it the first part of this, the method name would be called "scope." That's kind of like the Laravel standard, which basically just tells Laravel, "hey, if this, if this method starts with scope, we know it's a scope." Matt Stauffer: Right. Jonathan Reinink: And then you give it whatever you want it to be called. So in this case you would say, "public function, scope can log in." So then the method name would be "Scope Can Login." And that scope takes an instance of the query builder as its first argument. And then within that scope, you can basically just write that exact same code that we were just talking about where can login equals true, but you would write that within the scope. So you would say, "query where login equals true," but it only has that one tiny little piece and nothing else. And then, now when you need to find the users in your system who can log in, you no longer need to think about what's the name of that column? And what's the value that I need to set to get the right users who can log in? Now you can say "User, so uppercase U, User, colon, colon can login." Jonathan Reinink: So that's the method you gave, you call on the user model and then get, and so it nicely abstracts it. So you end up with this, again, this really beautiful fluent API where you can just run some more complicated database query, but the actual way you interact with that model, it's just this really beautiful, simple API that you define. And honestly, I use scopes like crazy because one, it's just ... There's two benefits, really. One is you get the code reuse, right? So if I write some query builder code, for example, maybe, it's can the user log in or something much more, much, much more complicated. And there are lots of examples of more complicated things. Like, for example, maybe you want to write a scope that orders your username first by their last name and then by their first name. Jonathan Reinink: Well, that's two pieces of query builder code that you could then put into this method. So when you put it into a scope within the user model, you now have the ability anywhere within your application to reuse that query builder logic. So it's just ... it really just reduces duplication. And then the second big benefit is it makes your code where you're actually using that scope so much simpler off often and much more expressive. Jonathan Reinink: I'll often ... So this is kind of an interesting topic all on its own. Some people like to use, what do they call it? Shoot, I'm going to forget the name. Shoot. Basically objects and PHP that you use to kind of facilitate getting data. What's the pattern? I'm going to forget it now, I don't even remember. Matt Stauffer: DataMapper? Jonathan Reinink: No, not DataMapper. It's where you create like an- Matt Stauffer: Oh, repositories? Jonathan Reinink: ... That's it, thank you. Matt Stauffer: Yeah, repositories Jonathan Reinink: So people like to use the ... Yeah, the repository pattern makes sense in certain applications where you have like a repository that says, okay, you know, I'm going to ask the user repository, which is just a class to give me a user. Or I'm going to ask a user repository to create a user or to do all these different things, right? And I'd argue that with Laravel, the repository pattern is actually an anti-pattern in Laravel because really Eloquent takes care of that for you. And you don't need the repository pattern to do these things. If you have a bunch of query builder code in Laravel, don't go and put that into a repository class. I would suggest that Laravel has a wonderful way of doing this already, and that's scopes. Take that reusable bit of code and put it into a scope. Jonathan Reinink: And it's just going to make your app ... Then in your controllers, or wherever you're going to run the database query, it just makes that code a lot more simple because it's just concerned about whatever it needs to do. Like, so I want to user that can log in. It's not concerned about, well, how do we actually define if a user can log in, which is, you know, there's a column in the table set to true. Jonathan Reinink: So I use scopes super, super aggressively, and that's something I always encourage other people who are new to Laravel and new to Eloquent. Definitely embrace this early on because it's wicked. And what's really cool about scopes is you can actually apply scopes to relationships as well. So for instance, if you said ... Imagine you have an instance of a company model, and you want to find all the users that belong to that company who are able to log in or who are able ... who have some other attribute or something. You can create the scope on the user model that defines if they can log in. Jonathan Reinink: And now when you load those users for that company, you can append on the query that's going to do that, you can append well only give me the ones that can log in. And Laravel behind the scenes, will actually generate the proper database query that says, okay, we got this company. We want to go off and get the users for them. So it's going to say, well, give me only the users where the company ID for this user equals the company ID for the instance of the company that we have in memory, plus also limit the results to only those users that can log in, which is a scope that's defined on our user model. Matt Stauffer: Yeah. Yeah. Scopes are powerful. And it's interesting because I think that a lot of us know to use scopes for where, and in your course, and in your tweets, you've shown just how much more can be done with them and obviously not appropriate for this podcast, but just know y'all like learning scopes does not just allow you to have shortcuts on your where. Although that's super valuable for many reasons, Jonathan just said. It's also going to prepare you to do a lot more other creative stuff and useful and functional stuff. But you know, also don't downplay the significance of readability and D duplication of code. I mean, there's so many arguments in our community about what the single responsibility principle means, but the best thing I like about it is if I'm going to change some logic, I'd prefer not to change it a whole bunch of places around my app. Matt Stauffer: And one of the things that allows you to do that is a scope because the number of times I've had to change logic from a Boolean to a date or like the published at is published change to publish at or whatever. And with a scope, you change it in one place. Maybe you rename the scope and you change it in a whole bunch of places, but it's a simple text substitution. And so you get these really expressive things like user is VIP can log in whatever and get, and it's just so much cleaner. And as someone who reads a lot of other people's code, one of the number one of refactors that I suggest to people is I look at a database query, and I just say, I don't know what this is doing without the context of your understanding of this database structure. Could you make this a scope? Because now I just have ... It's sort of like extracting a method out of a controller, but instead you're extracting repeated code out of a query builder pattern. Jonathan Reinink: That's exactly it. That's exactly what you're doing. And that example that you just mentioned, switching a Boolean to a datetime field- Jonathan Reinink: And that example that you just mentioned, switching a Boolean to a Date-Time field. That's a wonderful little, like a pro tip. I've tweeted about this before. More often than not, if you're thinking about creating a Boolean column in your database, consider using a Date-Time field instead, because it'll serve the exact same purpose, because if it's null, that's false. But if it's true, there's a date there and now you have some extra information. Maybe it's they became a VIP or it's are they VIP true, false? We'll, not only know if they're a VIP or not a VIP, but if you use a Date-Time column, you can now say, well, actually they became a VIP on June 5th, 2020. Matt Stauffer: Yeah. Jonathan Reinink: That's just a really simple little thing that's quite often with if it's a Boolean column using a date is a better choice. Matt Stauffer: I totally agree. I love that. There are two other things I commonly use on my models. They're not quite as common. Well, one of them is very common, which is guarded and fillable and then the other one is accessors and mutators. I'm just going to give the quick rundown accessors and mutators so that you don't have to dig it. What I want you to do is be ready to give your official Jonathan Reinink opinion on guarded versus fillable. Jonathan Reinink: Love it. Matt Stauffer: I just think that mutators are really simply the way for you to modify that every single time you get a certain column out of the database, it should be modified by a certain set of PHP code, that's an accessor. Let's say every single time I want to... You can either modify an existing column. Every single time I get first name back, I want to capitalize the first letter or whatever, or you could also create a new fake column, like a virtual column using an accessor. Matt Stauffer: Let's say you store first name and last name, and you want full name to be accessible. Well, you can create a fake column called full name and the PHP logic, and that accessor will define how to join the two. Look those up if you're interested. And then mutator is the same, but the opposite end. If you want logic to be performed on your data for a given column, every time you save, before it hits the database, that's what a mutator is. And it's the same thing, there's just a consistency tax, just like with scope, but instead of scope XYZ, it's set XYZ or get XYZ. Go look up accessors and mutators if those particular things are useful for you, but Jonathan is right. Those are not nearly as common as the other things he listed. But the thing he's about to tell us is the first thing that people do in every single Eloquent model they build most of the time. Jonathan, tell us about guarded and fillable and what we should do. Jonathan Reinink: Yeah. Okay. So this is the spicy topic, because it has to do ultimately with security, but I feel like I'm in a good company because I know that Taylor himself uses my approach and many others do as well. I'll first started by giving a bit of an introduction to the challenge of the security issue and then how Laravel solves it and then how I do it. With Eloquent, as we've discussed, you can create records in the database and you can update records in the database and you can delete records, right? But in particular, when you create and update records, you need to pass it in array. And that array has a combination of keys and values, right? First name Matt, last name Stauffer, email, whatever. Right? And that's great. Jonathan Reinink: That's a wonderful way to work. But the problem is if you we're to take some data from some form. Imagine you have some Foreman on your website that allows someone to update that record. Maybe it's their very own profile. Okay? Matt Stauffer: Mm-hmm (affirmative). Jonathan Reinink: They're able to update some info. They're able to update their first name and their last name and their email and their password or whatever. Right? But there's also additional columns on that user's table that define other things like their membership level in this system or whether they can... Whether they're a super admin in the system can access everybody else's records. They might a Boolean foot field for that. You only want them to be able to update some columns and not other columns. What Laravel provides, and the risk here is just to be clear, is if someone submits that form and they submit it down with their first name, last name, and their email address. Jonathan Reinink: And then within your controller method, you take the values from the request. You say, okay, request, get all the values from that form. And automatically just update that particular user's record with all the values that came from that form. First name, last name and email. That's all fine. You can do that. But the danger here is what if somebody manipulates the request and instead of just passing and down the first name and the last name and their email address, they also pass on an attribute that says is super admin. And they set that to true. If you don't have protection and you're just blindly passing all the values from that request, all those attributes and updating that user model, that user can theoretically... Someone can hack your system and very, very easily update other attributes on the record and give themselves access to things that they shouldn't have. Jonathan Reinink: That's a huge security hole. By default, the way Laravel protects against this is, is they have this property on that you can define on every single model, that's called fillable. So you define it. If you're on the user model, you might set your fillable attributes to the first name, the last name column, the email column, maybe the password column, whatever. Right? So that if someone tries to pass down a value from a form that they aren't allowed to update such as, is super admin, the system's just going to, I don't know if it throws an exception or it just completely ignores it. I don't even... Matt Stauffer: I think it just ignores it. Yeah. Jonathan Reinink: Yeah. That's what I would have expected. Shows you how often I actually use this feature. I barely don't. I don't use fillable, but that's how it's designed to work. To me the problem... That's good on one hand, the problem is I actually believe it leads to more security issues than the way that I use it. Jonathan Reinink: The issue is what happens when you have different types of users in your system. Maybe this user updating their own profile, they can only update first name, last name and email. But what happens if now me as a super admin, somewhere else in the application in some admin control panel, I want to update their membership status, or I want to set them to a super admin or something else. I now need to be able to update those columns. What's going to happen is the first time you do this, you're building this new fancy admin control panel for yourself to manage your customers or the users in the system, and you're going to submit that form and it's not updating that column for you. Jonathan Reinink: And you're not sure why? It's like, "Oh right, I'm not allowed to update it because the fillable doesn't include that." What do you do then? That's- Matt Stauffer: You add it to fillable. Jonathan Reinink: Yes you add it to fillable. And now you've created a security hole. Because now you've basically now made it possible for... Because fillable is just this static hard-coded value in your user model. It's not something that's dynamically set. So if you need some other user to be able to set it, who does have permission to do it, you've now made that change for everyone. And Leravel provides some functionality around this, where you can actually choose to leave, say is super admin as a non fillable attribute. And then there's some convenience methods that you could then use instead in your add control panel, controller that I think it's called force create or force update, which will bypass the fillable settings and allow you to force change those, assuming you have access to those end points. And that works to do it that way, because you basically say, "okay, I'll take control in this one area of the application to make sure I don't do something I shouldn't." Jonathan Reinink: But the problem is you're now using force, which means you're now introducing anybody, every single column they can update. Maybe you don't even want this particular user to be able to update everything on that user table. Maybe you only want them to be able to update one extra column. You just constantly fighting this, back and forth. Okay, it just reminds me of that Simpsons GIF, where Homer's adjusting the blinds and it's just up, down, up, down. And that's what fillable feels to me like, okay, I got to do this. You're always battling in fillable. My preferred approach, which is the preferred approach for, I think more people in the Laravel community is to just disable fillable entirely. Jonathan Reinink: And the way you disable fillable entirely is by setting... Fillable I should... Yeah. How do I say this in the most simplest way? So fillable white lists the columns that you're allowed to update, there's an alternative property called guarded, which I guess white lists the fields which wait lists the fields that can't be edited. Instead of defining which ones can be edited, you define which ones can't be edited with guarded. However, if you just set guarded to an empty array, just, protected guarded variable equals empty array. Now, anytime you work with that Eloquent model, there's going to be no fillable protection, no Mass Assignment. I should have mentioned that this is what this is all called is it's basically- Matt Stauffer: Mass Assignment Protection Jonathan Reinink: Mass Assignment, yeah, Protection. Exactly. It completely disables Mass Assignment Protection. Matt Stauffer: Yep. Jonathan Reinink: Why do I do that? I do that because I would much rather have control of which columns are updated on a per controller basis as my application requires. For me, if we go back to the example of the profile, right? If I say, if a user is editing their own profile, and I only want them to edit three columns, first name, last name, and email, maybe password. Right? Matt Stauffer: Mm-hmm (affirmative). Jonathan Reinink: In that controller method, I won't say, current user update, to update that record and then pass in request all, give me all the values from their request. What I actually will do is I'll explicitly say, I want these four attributes from the request. Give me their first name, last name, email and password, and only update those. And it's that simple. That's how you protect yourself. Jonathan Reinink: Don't use requests all. In my opinion, you should never use request all. Never, ever. Just don't ever use it. And if you follow that way, you're essentially doing mass assignment protection but you're not doing it at the model level, you're doing it at the controller level, where you have more control so that you can dictate that in one place in the application, this particular user can update these columns. And another place, maybe a super admin can update these other columns. And it just gives you a ton more flexibility. And you're not going to find yourself doing something super silly, like adding every single column to your fillable column, to your fillable property on your user model, because maybe a junior comes on it doesn't fully understand what the implications of that are and just adds them in there. That's what I would say, never use request all, always use request only. Request only takes that method, takes an array of columns or attributes from the request and you pass those to your update or your create method. Jonathan Reinink: Yeah. A little fun fact, if you use validation, which you should be in your controllers. And you say in controller, so say you're creating, or let's say you're updating a user. And in your validation you use the validator. You say validate, which is the validate facade, but I think you can do this using this validate as well. You say validate and you give it an array of validation rule. First name can't be longer than 25 characters it's required, email, must be an email address, password must be so many characters, whatever. You have all your validation rules. Jonathan Reinink: The validation method actually returns back only the data that you're validating. First name, last name. This is a really nice little shorthand, where you can basically just take the response back from the validate method, and then just pump that right into your update or create method. And really what it does is it saves you from having to repeat those columns a second time. Yeah, official Jonathan Reinick position, don't use fillable, use guarded. What I do is I actually create a base model in all my applications that all my other models extend and it sets guarded equals an empty array. Matt Stauffer: Yeah. It's funny. I know it doesn't make any difference, but I have been setting guarded to an array that just has ID in it instead, only because I'm like, well, I should be protected anyway, because I'm never going to ask for ID, but in theory, I don't ever want anybody to accidentally up their ID, so it doesn't make a difference, but I just do it anyway. Just it's like this little piece of mind for me, but yeah. And that's how we do things at Tighten as well. We don't use fillable. We used to use fillable. We have some old projects that do that we're slowly converting and it hits all the problems that you described. It's just gets... Anyway, yes. One are the other benefits you... Jonathan Reinink: I think there's been talk of switching the default behavior, but yeah, it's really a security thing. If you're going to make that change, how do you implement a change like that? But I think because Laravel's designed that way by default. When you create a new application, the user model has fillable set, I think for beginners getting started with Laravel, it's the default app structure, the default Laravel install suggests that this is the right way to do it. Which I think is hard to change. But I think as an unfortunate default. Matt Stauffer: Yeah. And I would say that that would be the first change you would make listening to this is that, every single time you open up a new model that you just generated, delete fillable and add guarded equals empty array, and use only in the controllers. What I was going to say is you mentioned the benefit of using only in the controllers or something like that is that you get control, but I also like that you get more visibility. It's much more clear, which ones are allowed and which ones are not allowed at the moment when you're saving the things versus having to remember where it is elsewhere and where it's defined, all that stuff. So, yeah. Jonathan Reinink: That is an excellent point because I remember on an early projects where I was using that behavior and doing request all, I remember going to my form view to figure out what columns I was submitting down. I'm like, this is ridiculous. Matt Stauffer: Yep. Exactly. Man, as always, I could talk to you for hours. As good as this is, let's move on to a few common challenges and got yous. When people are first, especially first working with Eloquent and the Laravel query builder, what are some things that trip people up often? Jonathan Reinink: Yeah. Okay, so it depends what your background is. Right? If you're more from my background, where you're used to writing raw queries, and there is definitely a set of people out there that this is their background. Sometimes it can be a little bit feel limiting and feel a little bit like, well, how do I do this in Eloquent? Because when you just use it as writing, whatever query you want, that can be something that can trip you up. I've learned that you can pretty much write whatever query you want in Eloquent if you know how to do it. Eloquent, well, it has a standard set of functionality, creating stuff, getting stuff, updating stuff, deleting stuff. It also has a whole bunch of raw methods. And these raw methods allow you to write whatever database query you need to. Jonathan Reinink: So there's a selectRaw, there's a whereRaw, there's an orderByRaw there's a bunch of helpful raw function. If you're coming from a background where you historically have written a lot of raw queries, I would say, don't give up too quickly with Eloquent, try to write it the proper Eloquent way, but then recognize that Eloquent does have escape patches for where there's times that this whole abstraction, which is an abstraction on top of SQL, doesn't provide whatever it is that you want. That's one thing. The fillable guarded one is another awesome thing we've already talked about that trips people up. We covered that well. Understanding the difference between the query builder and collections and in particular here. Yeah. Just understanding in particular. Generally, I'm just going to walk through this again, as I said earlier, when you run a query, Laravel's going to run the query, get some users from the database, get some products from the database, whatever. Jonathan Reinink: That happens when you call get on it. Right? Matt Stauffer: Mm-hmm (affirmative). Jonathan Reinink: And what that does is it runs the query and gets those records from the database and then returns them as a collection. Quite so that collection is the end result of the query that you ran. But the thing about it is a collection has a whole bunch of methods on it that are very similar to methods that you'll find on the query builder. The difference is, when you run it on the query builder, they're running the database. When you run them on the collection, they're running PHP. And sometimes if you're not in tune with it and understand how these things work, sometimes I've seen people get too much data from the database and then they do their work as a collection object instead of doing it in the database. Jonathan Reinink: For instance, you could say, users get, which will get you all your users. And then you can say where can login equals true. And what that's going to do is it's actually going to get every single database from every single user from the users table in the database, and that is going to get them all back in PHP. And then it's going to use the collection to iterate through and get only the ones that can log in. And that's bad for performance because now you're getting all the users back from the database. When in reality, you only want the ones that can log in. I'd suggest that beginners familiarize themselves with what is a query builder method and what is a collection method. And as a rule what's to the right of the get call. Matt Stauffer: Yes. Jonathan Reinink: That is the collection method stuff. And what's to the left is your query builder stuff. And as much as possible, move whatever you're doing on the right to the left, you want to do more work in the database and less work in PHP. The one area that people get in particular, especially hung up on this is with relationships and Laravel. I talked earlier about how on the user model, you could have a company relationship, right? Matt Stauffer: Yep. Jonathan Reinink: Or maybe a better example is on the company model, you'll have the user's relationship. What you could have happen is, if you call in your say in... Just to understand the difference, I'll try not to go too long about this, but you call on, you have an instance of a company and you call users on that to get all the users from that company, if you call it as a property. Jonathan Reinink: Just company-users, you are going to get a collection back. Because what's happening is Laravel behind the scenes is going to go and lazy load that all the users for that company and return them back to you as an Eloquent or as a collection, sorry. That's how it works when you just call it as a property. However, you can also call a relationship as a method. And when you call it as a method, you get back an instance of the query builder for the users, for that company. Which gives you all extra interesting stuff that you can do. Jonathan Reinink: For instance, you can limit those companies, just the ones that can log in or whatever else you could even paginate it if that made sense. I think people get confused sometimes about the difference between the two. And they both have their use, just remember that anytime you want to limit the results of the relationship, or do some sort of operation on it that would potentially be faster to do in the database such as sorting or different things like that. Jonathan Reinink: Try to do that as a query builder, which means calling the relationship as a method and not as a property. Matt Stauffer: Yep. Jonathan Reinink: Yeah. I know a lot of people are first getting going with Laravel have... And it's the nature of the beast a little bit. It's wonderful how Laravel's designed, how you can call a relationship as a property. That's a bit of a double edged sword because it's a little bit unclear, what the differences between the two are. And then... Yeah, go ahead. Matt Stauffer: There's a note to add there because there's two reasons why that's less performing. One which you all are probably thinking about. And one which until Jonathan mentioned it recently, I thought I was the only person in the world thinking about. The first one is what you're probably thinking about, which is that when you have more results come back from a database and it has less scopes applied to it. It has to do more work in the memory. The database is slower if you're pulling 10,000 things than if you're pulling 10 things. So just most simply, but the second one is that every time you instantiate an Eloquent object, it uses a little bit of memory. And 99 times out of 100, that doesn't matter. It doesn't matter that you're newing up a new Eloquent model because you only have one or two or 10 or 20 or whatever. Matt Stauffer: But if you're doing work with thousands or tens of thousands of Eloquent objects, that memory adds up and it becomes a big deal. And at that point, you're probably going to want to modify where you're working with in some way, shape or form, if you... First of all, just don't bring back 10,000. If you can avoid it, do what Jonathan's talking about here, scope them down so that you are not returning 10,000 users when you really just wanted the VIPs at the company. And then you would do company arrow users, parentheses, and then you can scope it down before you get it cool. But let's say you did need 10,000. That's a pretty advanced topic. We're not going to get into it here. Jonathan, is there a TLDR or should they just get your course if they want to learn more about that optimization? Jonathan Reinink: Oh man, if you actually need to work with 10,000 records, generally in that case, I would say, try to find, do whatever you can not to have to do that. Generally when you run into situations where you need to work with that amount of data you want to switch. One thing you can do in Laravel's, there's this idea of chunking. You can actually if you need 10,000, but if you can go through work with a thousand at a time, that's better. Or depending on what the operation that you're trying to do, you could just take those and throw them off to a job and let the job go on run. Because anytime you're working with 10,000 things, it's going to be a slow page. You're going to probably not want to make the user wait through that task. It's a great opportunity to use acute user job. Matt Stauffer: Yep. Great. Jonathan Reinink: Yeah. And that was memory usage. That was going to be my third thing that has tripped people up because, and I definitely experienced this. I think what happens is as you get more familiar with Laravel and Eloquent and just databases in general, you can run into situations where your page is slow. But when you go and look at the amount of database queries you're running, or you go and look at how fast those queries are running, it's wicked fast. I have a page that's running two database queries and they're each taking two milliseconds. I mean, I don't have a database issue here, but this can be deceiving because reality might be, yeah, you're only running a couple of database queries and they're running really fast. But the reality is it's potentially bringing thousands and thousands or potentially even hundreds of thousands of records back from the database that Laravel like exactly you said, needs to chunk through, needs to convert them to Eloquent objects. Jonathan Reinink: And then you're going to do whatever operation you're going to do on them. It's all very slow. So anytime you can push that work to the database level and not get those records to be begin with, databases are awesome at working with crap, tons of data. This is what they do. This is what they're designed for. So if you need to do sorting, if you need to do a filtering, if you need to do joins, if you need to do whatever, let the database do that work because they're really, really good at it. Matt Stauffer: Yep. And if for some reason you find yourself in a situation where you can't do that, which I would suggest is probably not as often as you think. There are little things that you can do that are going to provide you at least little benefits. Maybe it's a page, everyone knows is slow. And you're just trying to get it from 60 seconds down to 20 or something like that. You can do things like, you can do the chunking. You can also limit the columns that you're selecting. You can also use the query builder, so you're getting standard class objects instead of Eloquent objects, hydrated. Jonathan Reinink: Those are all- Matt Stauffer: So you're getting standard class objects instead of Eloquent objects, hydrated. Those are all possible. So put them in your tool belt, but I 100% agree with what Jonathan said, which is, 99% of the time when you're hitting memory issues, it's a something that you should have pushed into the database. Not some way you need to tweak how Laravel's loading the results or something like that. All right, so that was your third one? Jonathan Reinink: Yeah, I actually lied. I have three more too. But I'm going to go real quick, okay? Matt Stauffer: Yeah, okay go ahead. Jonathan Reinink: I'll go real quick. Okay so one, measure your database performance. This should be the thing I should have started this talk with. You need to know what your database is doing. You need to know, without some sort of measuring, you're flying blind. You don't know how many queries you're running. You don't know how fast those queries are executing. You don't know how much memory you're using. My recommendation, get the Laravel Debugbar installed, get Laravel Telescope installed, start tracking your database queries. Because unless you track it, you don't know. And the problem is everything looks great in development. You go to production and that's where the issues start. So use a tool like the Debugbar or Telescope. Jonathan Reinink: The next thing is just doing too much in PHP, which we've already talked about kind of related to the memory issues. What happens is people, I think developers, they don't know what they can all do in the database, or they don't know how to do in the database. And they quickly bail and they start doing it in PHP instead. And this is honestly the cause of so many major performance issues. Because you end up pulling way more data back because you have to because you need to do the operation in PHP and it goes really slow. So my recommendation is fight as hard as possible to do it in the database layer. And I've talked about this in other podcasts and stuff before. How at one point we had just this brutal page in this project I was building, that it was like the number one page. It was the number one thing it did in this application and we're doing it in PHP and there was just no faster way to do it. Jonathan Reinink: And I challenged myself. I said, "I'm going to figure out how to do this in the database." And I gave myself two days to do it, and I figured it out and it made a radical difference. And I was able to do stuff in, it was Postgres in this particular case, but no different than MySQL. I was able to do stuff in the database that I never even imagined I could. And it just made a massive, massive... It's remarkable what it can do. Okay. So yeah, so the third one I was going to say is just to make sure... People try to make their database vendor agnostic. So they try to make it work with MySQL and Postgres and SQLite. And I think this is like a really big mistake that people new to the framework think they need to do, because Eloquent is designed to be this database agnostic layer on top of all these different database types, right? Jonathan Reinink: But that's because it's meant to allow you to work with all these database types. It's not saying you have to only work with the Eloquent API. And if you think you only can work with the Eloquent API, you're going to miss out on all kinds of amazing features that these databases each uniquely provide. Like for instance, Postgres has fantastic geospatial support via their Postgis extension, that I use like crazy. And it's awesome. And MySQL has its own features as well, that are unique to it. If you try to write database agnostic code, you can do that. But essentially all you've done is you've limited yourself from taking advantage of all these wonderful database features. So I would say don't strive for that. Let Laravel strive to provide a database agnostic API, because that's its job, but you and your application commit to a database, commit to MySQL, commit to Postgres and take advantage of what that database offers. And if you don't, you literally are not allowed to argue which is better, because you are not taking advantage of either of those features. Matt Stauffer: Yeah. They're the same for you. Jonathan Reinink: They're the same. Exactly, exactly the same. So, my recommendation is take advantage of your database, learn how to write raw queries in Laravel allowing you to take advantage of those unique features. Don't be afraid to color outside of the lines of Eloquent. It provides methods and helpers to do those kind of things. Matt Stauffer: Yeah, that was good. Jonathan Reinink: All right. So, that's my common gotcha, I guess. Matt Stauffer: I love it. One note to your performance testing gotcha. One of the things that I've found that's very helpful for me is that I keep my seeds light. And so I miss a lot of the opportunities for performance testing locally, because not only me, I'm working on a local machine, so the connections are a little bit faster, but I often have a hundred where the real thing's going to have 10,000. So just a note to y'all that one thing you might want to do, to do a performance test, in addition, of course, to adding those tools each data is try maybe adding a supersized seeder to your testing. And maybe don't always do all your work there or maybe do, but regardless, consider really, really heavily loading your database locally before you even test it on the production environment. Jonathan Reinink: I love it, which is a whole other wonderful topic, which is fixtures and using seeds for your tests as opposed to using factories in your tests, which I- Matt Stauffer: Next episode. Jonathan Reinink: Yeah man, there's all kinds of interesting things there. Because like in my app that I run, like my main SaaS, I actually have 1,000 users that I use in my test suite. For exactly that reason. Matt Stauffer: Yeah. I love that. Yeah. And the next episode after this is going to... Well, not next episode, two or three episodes from here is going to be John Bonacorsi talking about migrations factories... No, I think it is right after this. Migrations factories and seeders. So you all will hear it two weeks after this one. So hopefully it'll be a really nice tack on to this. But because we are running so far behind, because we're having such a good time, let's move on. Is there anything else you think we should have covered today that we didn't get a chance to talk about? Jonathan Reinink: Yeah. Naturally, because I can kind of go on forever. Matt Stauffer: Any one thing. Jonathan Reinink: Two things. Okay. Two things. That's all I'm going to have left, all right? Just one, thinking just kind of as a beginner and kind of, what it might feel like to be a beginner and some of the traps that people run into. One, I think people underestimate what you can do with relational databases. And I think the danger with that is they too quickly move to other more complicated solutions, because this is how it goes. This is how it goes on Twitter. This is how it goes on Reddit. This is how it goes in Stack Overflow. Somebody says, "Hey, I'm trying to search my user's table. And the query is slow and it's taking too long." And you know what the answer's going to be. You need to use Elastic Search. You need to use Algolia. You need to use a document database. You need to use this and you need to use that. Jonathan Reinink: MySQL, Postgres, these are amazing tools that can do way more than you realize. And they have, for instance, wonderful search support built in. Yeah. Not to the level that Algolia, which is completely specialized on or as Elastic Search is completely specialized on. But you might, depending on your particular app... If you're building a search engine or an app that has like high priority on search, you may need to use Algolia, you may need to use elastic search. But if you just have a SaaS app where you want someone to be able to look up a user and have a bit of fuzzy searching going on, so it supports a bit of misspells and some ranking, you can do that in MySQL, you can do that in Postgres. Jonathan Reinink: You don't need to commit to a whole separate service and have the cost of that service and the management headache and all that. So don't be afraid to push the limits on what your relational database can do. I was tweeting with Sahil, the founder of Gumroad. Hopefully I said his name right. And I asked him what he uses. And Gumroad's massive, and millions of million dollars of sales, lots and lots of users. And I asked him why, kind how big... Well, what database are you using and how big is your largest table? Well Gumroad is using MySQL, surprise, surprise. They're not using some document database, not using anything fancy. MySQL. And he says his biggest table is easily hundreds of millions, if not billions of rows long. MySQL. MySQL can do this. Don't underestimate it. Postgres can do this. Don't underestimate it. So, that's just kind of one thing. Don't be so quick to jump to other services if you have a relational database setup ready. Jonathan Reinink: All right and then my second and my last big point I want to make is the key to database performance, as far as at least selecting data from your system is indexes. And indexes are these amazing, wonderful, magical things that are hard to learn. And the reason they're hard to learn is because... And this is something I had to really learn the hard way. I used to think indexes was, "Okay, I'm going to create some tables in my database. And then I'm going to just go ahead and I'm going to sprinkle some indexes on it. And I'm going to add an index to the first name and the last name, maybe, because I'm going to search on that and I'm going to add an index to the ID, which happens because the primary key happens automatically. And I'm going to add the key... Sorry, an index to another thing and another thing, just hoping it's going to make a difference." Jonathan Reinink: That is absolutely the wrong way to go about it. Don't add indexes hoping it's going to magically, magically do something. Because in all likelihood, it won't. And the problem is indexes are not free. They cost in two ways. One, an index adds additional space to your database, because an index is essentially duplicated data behind the scenes, that your database keeps in a separate spot to make it easy to look it up. So, that has a space cost. And the other cost is, every single time you make a manipulation of the database like an update or a create, it takes a little bit longer to do that create, because it has to populate that index as well. So indexes are not free. Don't just randomly create them. Jonathan Reinink: The trick to making indexes work is to analyze the most important queries in your application. Go find the queries that are running the slowest, and go find the queries that are running the most frequent. These are often referred to as the queries that are most time consuming. Because just having one bad query that's slow, isn't necessarily a bad thing if it only runs once in a while for some super user on Wednesdays. Who cares? What matters is the queries that you, you kind of like the main value proposition of your application. Those are the queries you want to look, at the ones that run all the time and maybe even a query there you might think is fast, because it's running at 50 milliseconds. But, if it's running millions of times a day, or even just thousands of times a day, you might want to look at an opportunity to create an index there and get that thing down to one to two milliseconds. Jonathan Reinink: But my point is don't add in, don't sprinkle on indexes, hoping it's going to make a difference. Instead, start with the query. Look at the query and say, "How could I make this specific query faster," and then work it back. And the way to do it is, it's a bit painful, but it works, is to copy that query. If you use a tool like the Laravel Debugbar, you can copy and paste it right out of the Laravel Debugbar, go into a tool like TablePlus and paste it in, and run an EXPLAIN on it. And to run an EXPLAIN on any database query, all you literally do is type the word "explain" before it, and then the query. And then what it'll do...Yeah, MySQL and Postgres and SQLite will all give you a summary of what it did to figure out that query. Jonathan Reinink: Now, the tricky part is understanding EXPLAIN. For years I'm like, "I have no idea what this means. Everyone's telling me to use EXPLAIN, but I read it. It doesn't make any sense to me." I've learned that it comes down to basically two columns in the EXPLAIN, at least in MySQL it's two columns. Postgres is formatted a little bit differently, but in MySQL it's... Oh shoot, I forget what they're even called at the moment. But basically there's a column that says what index is being used. So if you have created an index and you think it's being used, but you don't see it listed there, you have a problem. And you got to figure out why that is index is not being used. Or if you don't have an index yet, look at your query and say, "Where do I think I need an index? Okay, well, I'm looking against this column. Maybe I'll add an index for that column and see if it improves this query." Jonathan Reinink: Anyway, so that is a bit of trial and error. At least for me, it is because I'm not as well versed in SQL that... I'm not well versed enough in SQL to that I can look at a query and say immediately, "There's where I need to add the index." So I just trial and error. And in my course, I talk a ton about indexes and how to kind of figure those out. And I just kind of show by example a whole bunch. So indexes, and then this is the best part. Because when you figure in an index, it's like a magical moment. Because you see a query that's taking a hundred milliseconds, 200 milliseconds, 2.2 seconds, you see it go down to milliseconds. It's such a rewarding thing. It's amazing. Matt Stauffer: Yep. Jonathan Reinink: Yeah. So, indexes, learn index, learn how to index. And like I said, it's not an easy thing to do. I think the best thing to do is just trial and error. And again, the key is to work from a query, don't just sprinkle on. Matt Stauffer: Yeah. And the two columns I believe you're talking about are possible keys and key. Jonathan Reinink: So possible, possible key is good because it says you're close to getting the index at work, but it's still not working, but yeah, yeah. Yeah. So that's exactly right. Thank you for looking that up. Matt Stauffer: Yeah. And then key actually tells you the one that it's actively using right now. Jonathan Reinink: Exactly. Yes. And there's actually another one, right at the very end. It's called extra, it's the last column in MySQL. I think it's called extra or extras. It'll also give some information about often, if a sort is using... Like for example, if you're trying to sort some data, like maybe by the first name, last name, it'll show in that column if it's using file sort, I think it's called, which basically means it's not using an index, it's using like the file system to do, which is really slow. So keep an eye on that last column as well, because it can be an indication of issues too. Matt Stauffer: Yeah. I don't think I've ever seen anything in extra. To be honest, I always know I need to do this, but EXPLAIN was kind of terrifying to me until I Googled that. And I honestly had forgotten that those were the columns. So you have brought me back in to the fold of remembering to use EXPLAIN. So thank you, I appreciate it. Jonathan Reinink: Yeah, yeah. Matt Stauffer: All right. So if somebody wanted to learn more about all these things. Now, the first thing we've already talked about is obviously your course, which is absolutely fantastic. It's going to be linked in the show notes. I bought it for everybody at Tighten Because I had seen some of the stuff that Jonathan had been working on. I was like, "Y'all have to do this." All of Jonathan's blog posts are really, really, really good. There's quite a few really fantastic ones, and all of his Tweets as well. But outside of Jonathan's stuff, where would you tell people to go look to learn about Eloquent and using database as well in Laravel? Jonathan Reinink: Yeah. So it's kind of the same ones that I think that a lot of people have said before. This is the wonderful thing about the Laravel community. It has two goldmines. One, the Laravel docs. Laravel docs are incredible. And really, if you just start going through the query builder code and then move on to the Eloquent code, you're going to learn so, so much. There's no fancy answer here. The docs are awesome. Taylor has spoken about how he's made documentation, such a critical, important part of Laravel, and it really is. It's awesome. Jonathan Reinink: And then the second one, you know what I'm going to say, Laracasts. Matt Stauffer: Laracasts. Jonathan Reinink: Laracasts. Especially as a beginner, Laracasts. Jeffrey just does a fantastic job of explaining kind of the more fundamental concepts. He doesn't necessarily get into some of the more sophisticated stuff, the more complicated stuff, but yeah. You can check on my blog or my course for that stuff. And I've also spoken at three Laracons now about it. So, if you want a bit of a teaser on kind of my take on a bunch of stuff. Look up my Laracon New York talk. I talk a bunch about kind of some crazy patterns that I use, my favorite one being dynamic relationships, which is like this super powerful technique that you can use in Eloquent. So check that out as well. Those would kind of be my go-tos at this point. Matt Stauffer: Okay. Well, I will make sure all of this is linked in the show notes. And in the outro, I'll ask you how people can follow you and any plugs you want to make or anything like that. But before that, the personal fun moment. Okay. So last time we talked, you and I talked quite a bit about learning how to fix stuff around the house and around the yard and stuff like that. And I wanted to ask you, what is the most crazy or memorable or good story or whatever of something that you've had to fix recently, where you could share with us the experience? I don't know if it's something you never thought you'd be able to fix or something where it almost blew up on you or whatever, but do you have any good stories? I didn't prepare you, take your time to think about it. I'll cut out the silence, but I'm just curious. Jonathan Reinink: Oh man. I feel like I have to have something like that. Yeah. Matt Stauffer: Whatever. Jonathan Reinink: Okay. All right. All right. All right. So if anybody has ever lived out in the country, in the rural area, you'll know that home ownership out in the country is way different than home ownership in town. Because I have had a place in town as well, but now my wife and I are fortunate enough to have this little 50 acre farm just near the town that we live. And it's wonderful, but a farm and country property is like so much... I have like a million examples of things that I have to fix. Like when we first moved here, okay? When we first moved here, it has this well on the property and that's what was feeding all the water to our house. Matt Stauffer: Right. Jonathan Reinink: But this well water is terrible and it's super, super high in iron and sulfur. Matt Stauffer: Sulfur, yeah. Jonathan Reinink: It's just disgusting. And it basically, it was completely destroying all the plumbing in the house. So we've since removed all the plumbing and redid it. But the problem is, this well was at kind of the back of the property and kind of like, it was just in this little hut and we had some crazy, crazy cold winters, kind of when we first moved here. And the waterlines kept freezing on us. So there I would be in the middle of the night out there with a heater, trying to heat up this little water hut to try to get it to thaw so I can get water back in the house. Fast forward, you're trying to quickly take a shower and leave the house and there's no water. Water is the most painful thing. Jonathan Reinink: So we've since completely in our basement, or in our garage, we dug out the entire floor, five feet down and poured in a concrete cistern. And I don't know if you know what a cistern is. Matt Stauffer: Really? Yeah. Jonathan Reinink: Yes. So now this is what most people do in the area, because in this particular area everyone's got bad water. We've got this wonderful, beautiful cistern built, right? So it sits in the garage and I got this constant pressure system that pumps the water into my house, redid all our plumbing. It was all wonderful. But the very first winter after we did this super expensive project and whatever. So sure enough, it gets cold outside and the water in the cisterns freezes. Matt Stauffer: Oh my goodness. Jonathan Reinink: So now we don't have water. And how do you fix that? Because you have this block of ice in there. So trying to like run heaters in there to get this ice. It's just been all water, man. If you live in town and you just have this wonderful water pipe that comes from the town and you don't think anything of it, you don't have any pressure concerns, nothing. You are lucky. This is not what us folks in the country have to deal with. Jonathan Reinink: So yeah, ended up insulating the whole entire garage. So now the garage is insulated. Now it keeps it from freezing. But that's only like one tiny little example of like all kinds of things. We also live, where we live it's like a super windy location. So when we bought the place, the shingles were all bad. So we put all new shingles on the place, but it's super, super, super windy. And the shingles didn't tie down. So for like the first year and a half, every windstorm, half... Not half our shingles, but we'd have like all kinds of shingles off the house as well. So we'd be up there and I'd have like this tar and I'm like mashing tar between shingles, trying to keep the shingles down. Man, that's country life. Matt Stauffer: Home ownership, man. Jonathan Reinink: Home ownership, exactly. Sometimes I think, "Why am I doing this?" But it is totally worth it. But... Matt Stauffer: That's awesome. I grew up just outside of... There was a farm across the street, farm down the way, but my house was not a farm. And so we did have well water. And the sulfur, it smells like raw eggs all the time, like going bad. Oh my goodness. I had not thought about that until you said that. And now I remember it very well. Jonathan Reinink: My brother is actually a plumber and iron's actually fairly easy to get rid of, because they have ways to pull the iron out. But the sulfur is the nasty stuff, it's almost impossible to completely remove the sulfur smell from water. Matt Stauffer: Yeah. Do you have a softener at least though. Jonathan Reinink: No, because we have the... Well, now we have it now we literally, we have water trucked in. So big, massive trucks and it all comes soft. Matt Stauffer: Yeah. Yeah. It doesn't have to worry about it. So did you build a port outside of the garage and then just put a pipe into it and pipe the water in? Jonathan Reinink: Yeah, so there's a little... The cistern outside has this little cap, like a portal... A pipe, that the people who deliver the water can pull the pipe, the lid off of it and it pours it right into the house. Matt Stauffer: That's. Awesome. That's so cool. Jonathan Reinink: Works pretty good. Matt Stauffer: Yeah. I was about to nerd out with you on like seven more things about living in the country and I'm not going to do that because we're already at an hour and a half. So how can people follow you if they want to get more about you? We've talked about it, but now actually what's the Twitter handle. Where else should they go? Everything. Jonathan Reinink: Yeah. So I would say two places. Really just Twitter, my handle there... This is really where I hang out, as you know, Matt. So it's just my last name, Reinink. That's R-E-I-N-I-N-K. So Reinink, you'll find me there. And then my website. So, that's where I blog. If you go to my website, it's my last name again. Reinink, R-E-I-N-I-N-K dot C-A and yeah, you'll find all my blog posts and stuff there. Matt Stauffer: Love it. This was awesome. I had so much fun. It's always fun to have to cap yourself, but yeah. Thank you so much for your time. Thanks for sharing all this stuff. And again, y'all he's always sharing new good stuff, so go follow him on Twitter because you're going to learn a lot more. So thank you so much, Jonathan. Jonathan Reinink: My pleasure. Matt Stauffer: All right. See, y'all later.