 Okay. Hello guys. Today I'm gonna talk about object-oriented database. I'm pretty sure a lot of you have heard about or have used ORM before. This is pretty much for those who haven't. Introduction is a social protocol because I hardly know anyone of you here. Except for Michael. That's it. I'm pretty sure you guys haven't heard of me. So about me, I'm Eddie. Eddie, he died, right? I'm a full stack developer for 13 years. I may look like I'm 18, but I'm pretty old. Currently I'm the head development of Neurune, Singapore. I have three front-end developers and four back-end developers in my team. Used to be four front-end developers. Where's Huijing? You see here? Huijing left. Okay. How I got to where I'm here? I started off as a web designer and front-end developer back in 2001. I left in PHP when I was in 2002. And then I was doing a freelancing. And then I got paid for it at that site. That was like an achievement. And then I joined Ineos Solutions in Singapore for a very long time. And then I joined Publicis Neurune. About Neurune, I'm pretty sure a lot of you haven't heard of it. It is a publicist for a white company. Neurune just got bought over about last year, early last year. It is a design technology consultancy. And our HQ is in Monterey, Canada. We've been in Singapore since 2015, May 2015 last year. This is going to be my quote for today. Oop, you're the bookworm. Oop, you're the DV with ORM. Okay, what is ORM? ORM is object relational mapping. It is a technique that allows you to query and manipulate your SQL, normally SQL query data using an object-oriented paradigm. So as a style, let's say you have three tables, which are related to each other. I'll say you have a customer's table, and then you have a customer's type. Say the customer's type could be a normal customer, a corporate customer, and premium customer. And then each customer will have multiple transactions. So a typical relational database would look this way, right? Where a customer belongs to a customer type, and a customer has many transactions, and then transactions belongs to customer, and so on. So these are the three keywords that you need to know. It is has one. Is it three? Has one? Has many? Belongs to, please, there's three, right? Yeah. Has one, has many, belongs to. There's also one has many true, which involves a pivot table, which unfortunately I didn't have time to create the slides here. How it will look like? So basically at the top, you can see how a normal SQL query will look like, and at the bottom with ORM. How many of you here haven't used ORM before? You haven't? Okay, I believe this will be a good introduction for you. So if you can, as you can see, right, it creates some more object-oriented approach to access your database. Do you have any, any questions on this quote? But obviously for every other, anything, there are pros and cons. So I would like to give you what the pros are. It is dry, dry as in don't repeat yourself. So you write your data model in one place, and it's easier to update and maintain and reuse it. I believe it is perfect for collaboration, especially if you have a junior in your team. It creates some, it's kind of make it less likely for them to mess up. As you know, data is very sensitive, especially if it's a production one. And then if you mess up in query, sometimes a simple mistake can, can be costly, right? And then a lot of stuff is done automatically from the database. This can be, say, sanitizing your variable automatically. And also it forces you to write MVC quotes. It will make your code looks a lot more cleaner. And you don't have to write polyform SQL. As you know, SQL can be very, very long, especially if there's a lot of joints and where, where, where from and so on. And then usually a lot of beginning backend developers would treat SQL as a sub-language. I have juniors in my team, sir. And I must say, they won't go into deeper than inner joints and left joint and all those kind of things. And some, some of them don't believe in learning more about SQL. And this is where I believe, as a, as a starter in PHP, I believe you should go into ORM first. They'll make it easier for you to understand SQL later on because SQL is really a very, it can be a very complex language. But the cons is you have to learn the ORM. There are a couple of ORM libraries around and each of them has their own style, their own concept. And you have to learn, learn them through the documentation and such. And the, another pain point is you have to set it up in the models. I will show you that later. For performance-wise, it can be okay, but only for simple queries, usually. For more complex queries, you can still do it with ORM, but it's still going to look a bit, a bit overwhelming. I'm going to show you that later as well. And it abstracts the database. So it can be a trap for new programmers. You're going to find that a lot of OOP, right? You're going to do a lot of loops and then inner loops and so on. And this can be a trap for programmers where you will end up with a lot of queries to the database, like multiple queries in one session where on a normal SQL statement you can write one statement with one query, right? It can be a trap. Although it can be optimized in ORM. The, what I like about it is, of course, it makes things fast. It is not something for you to use for a high-performance kind of system, definitely. But if you want to build something fast, ORM is definitely the way to go. And then you do the optimization later. Sorry to jump in. You seem to be talking about this particular implementation of ORM, which is, which may have the set of cons. There are other ORMs where you can build production quality, high-scale, high-performance code, which still gives you all the benefits of ORM. Because this particular implementation has query in a particular style is why we have these drawbacks, especially around, you know, SQL flexibility. Yeah, I'm going to show you how to optimize in ORM as well. What I was saying is there are other implementations where we don't need to even optimize it. You have full query flexibility, in which case your performance issues completely go away. And you can then focus on writing good off the internet code. Okay. Which is when sharing ADODB, ADIO, A-B-O-T-B-A-G-O. So PHP opensource project from many years ago. Okay. And that allows you full query flexibility. And you don't have to set up models. So the setup completely goes away. It's not very lightweight, but for most production loads at reasonably high-scale, it doesn't hinder your performance at all. And query flexibility is full. So you can even use joins and all kinds of complex statements and still get objects returned. So you don't have to follow any of these tracks. Yeah, I'm pretty sure there are a lot of, there are others that are very optimized by now. I mean ORM is pre-or. So I'm pretty sure someone has already created a more optimized version of it, so on. But usually those very popular ones and those that come with the framework, they are not really that good in terms of optimization. And for new PHP developers, I'm pretty sure they are not aware of what goes on underlying the ORM. And therefore, when it comes to optimization, it's pretty hard for them. Yeah. Definitely, this is something not for advanced programmers like you. This is simply an introduction to ORM for those who haven't used it. Because I love ORM. It's just like you can create something fast like in one day and so on. Okay, before that, ORM is not the perfect answer. And don't try to write your own ORM unless you have too much free time. There's two very popular PHP ORM, which is the Propel and Doctrine, which you should check it out. But for the demonstration I'm going to show, I'm not using either. But the point is I'm introducing the concept of ORM and Propel and Doctrine will have a similar concept, a bit different style or different syntax and so on. And the best is to get to know one ORM and be very good at it. The moment you're very good in ORM is pretty easy to jump to another ORM brand. Okay, I'm going to show you a database table, which I created just now. Can you guys see? All right. So let's say this is an e-commerce shop, which is called My Shop. And you have a few customers, Bra, John, May, and so on. And then you can see there's a type ID. The type ID refers to what kind of customers they are, whether they are normal customers, corporate customers or premium customers. And then each of the customers will have, will make transactions on your shop. Do you guys have any questions about this? Well, it's pretty easy, right? Like each transaction belongs to a customer. And then each customer belongs to a customer type. And each customer has many transactions and so on. And then when we go to the code itself, how are we going to query and list down the customers? Okay. How do I do this? Command plus? Can you guys see? Small, right? Yeah, and that means that it goes to, I think it's all the preferences. It's font and color, font and color, font and color, so no one has increased the code. 24? No, it is, yeah. So every small? Every bigger, yeah. 24. Bam. There you go. For this one, I'm using a Koana framework, but we can ignore this framework stuff. It is just about the ORM. So this is how, this is very simple. You don't have to do that whole select from, select star from customers table. But the downside, as I mentioned earlier, that you have to set it up. That there's three tables, therefore you have to set up three models. And as you can see, I've set out the customer model. It belongs to type and the model related to type is the customer type. This actually refers to this file. And the foreign key is the type ID, which is this column, type ID. And same goes with transaction. It belongs to a customer. And the foreign key is customer ID, where you can find it here. And the customer itself, actually this has many as well. Okay, never mind. Okay, so after everything has been set up, you can simply do the customer, create an object, put the object in the customer's variable, and then you can start looping the customers. This can, so usually this can be done in two ways. Another way it could be, say, new model customer. But then you will have to assign another variable this way. I prefer this one-liner thing on line 19 and 20. These two lines would be more clearer for beginners. But I believe the first one is clear enough. So from these action customers, what I'm doing is just listing out the customers. Brow, John May, John May Miao. And then this will list the customers and the customer's type. Over here you can see how, where the ORM advantage is. You don't need to do another query, you just have to customer type. So the demo here. So Brow is a normal customer. How did it know that type refers to the name column? Again, this is the advantage of ORM again. Over here, I set this up to string. So whenever I try to echo that object, they will try to convert to string and return this. So it makes things a lot more clearer. It makes your code more easier to understand as well. And over here we have by type. That means you do a query on the types table first, and then you find which customers in each of these type. As you can see, there's this inner loop thing that can be a trap to beginners. Of course, this can be optimized. But if you're trying to do something fast, this is definitely the way to go. And I'll show you the result. So on a normal customer, there's Brow on the corporate customers as John May. And then on the premium customers list, there's these two. For the optimization that I was talking about, ORM can be quite intimidating when it comes to very advanced query. This is where, let's say if you have to calculate how much each customer spends in total in your shop, this is how the ORM is going to look like. I'm pretty sure there are other ORM that can do better, or maybe that OOBE. So what this does is actually it just generates the SQL query. I will show you the query. You can see at the top. Are you able to see the query at the top? It's actually select some and so on. This is the actual query that it's using. So obviously this can be made easier by refactoring. So put this whole chunk in the model. It has customers total spend. Let's say if I put this in a model, it probably going to look... Oh, it's just too much. So I hope I made a good introduction to ORM. The reason being, I've been using this for very long. Frankly speaking, I'm pretty reliant on it. I don't think I can go back to doing queries and stuff because it's just too cumbersome. Then the ORM that you were sharing about... That's ADO. ADO. ADO. This one? Yeah. So ADODB is a general DB layer. It has relational as well as non-relational support. Sorry, not sorry. Object-oriented as well as non-object-oriented support. And I use the object-oriented version of it. And it's... I think it combines the best of words. It gives you proper ORM layer. It also gives you full, very good solution. Oh, okay. I will check it out. Usually the ORM libraries will provide simple optimization. Like in this case, right? I am trying to load customer type and as well as the customers. It can actually do a simple join with customer. So what this does is it will actually do one single query, put it, put all the result in the object. And then you can loop it. It won't do two queries. So this second one is going to do two queries. So there's a difference. Of course, a lot of other optimization features in the rest, doctrine especially. Any questions? How do you express relationships with other tables? How would you declare a relationship, say customer has many transactions? How would you declare here? Here? I mean, when you create a customer, do you actually set up and say this guy has... a customer who has many transactions? Or is it more like you write a query and just go where, join? Yeah, that's why I was a bit... I was a poster of it because I didn't... I didn't set it up by people so I didn't know why. You know this kind of case, right? My code works, I don't know why. And it's supposed to have this, has many transactions. And then you have the same thing. The model and so on. This is like Larabelle or Kohana. Yeah, so when I was doing cake PHP, you would also do something like that. You could declare it, say this guy belongs to this guy. Then I would do a doc, how to give me all the related transactions. You actually use a foreign key to just discover what are the associated break records and give it to you. So I think one of the good things about an ORM is that you get this all free. You don't need to explicitly say, get me all the transactions. You just get customer transactions and you give you all the transactions. Which is very interesting. And especially if you use IDE, you can do that technically setting that up with Sprite and Reach. It gets very messy. You try to put that into a non-review code base and run it on production. It basically kills performance. I think ORM as it is, it's not optimized for like, it's pretty but it's not really my performance sometimes. I question that. I'm saying the moment you find your sweet spot, it can actually be very interesting. Actually, if you know how it works behind me, it's very easy to optimize. So that's the key thing. You will have to know what goes on behind it. You are getting the customer object. It is also fetching the type. So I don't know, I don't want type also but it is having an object so it is taking more memory. I guess you can kind of say I only want a certain thing. Are you ready to miss? You can, oh. That's a second example. Oh, actually this is the second query. On line 39, you will do a second query. On the object, it's not a method. You are directly accessing the property. So it's already fetched. When I access the name, it's already in the customer's table column. But when I access the type, it's not far out of the customer table and therefore you will do the second query. For the runtime. Yeah, in runtime. It will fetch in the database. So if you don't want it to do two queries, any other ORM would have this type. So you will do a joint statement and get all the results put in the object. Line number will be same in this case also. Yeah, what? I have to fetch the type same way even you write it with. Yeah. This is a very basic ORM usually not performed. When you start to do this whole looping, they will do multiple queries. Definitely something that you can, it's easier to comprehend, easier to develop. But when it comes to optimization, that's where you have to look deep inside the ORM to figure out how to optimize it. It's not something where, I know some people are obsessed about optimize as you code. I don't, I mean, do the necessary, but don't be too obsessed with it. As far as you can, optimize later. Any other questions? This ORM I used before is PHP Active Record. It actually has a way of saying, if you know they're going to be fetching this other entity, you can tell it to, I've got to get all these other entities and basically make a joint, a second query which has all the IDs of the associated. So instead of doing one query at a time, you actually do another query with within where something is in this, this of, basically passing the list of problem keys should then fetch all the records in one query rather than using joint. Some people can control what to fetch or not. It's not really a joint, it's actually a second query. PHP Active Record has this function. I don't know what to call it, but basically you can tell it, I need to get this other stuff. You basically, from the first query, derive all the list of keys and make a second query with just that list of problem keys, which makes it a lot more, basically just making two queries instead of one. It's quite handy. Another feature I would like to point out is that I don't know if Doctrine has it, but I developed something here where I can generate the list of columns from the table, size, right? And then it will... It's going to sound a bit silly, but it's going to write on itself and put it here. So it's going to look something like a method, right? Or a loop prop, something like that. So you will list down all the columns here so that you can get the autocomplete in your IDE. So you don't have to remember what is the column name and whatnot. It's great. Yeah, it's a square arrow. It's inside here. I don't know. Where am I? It's a very big file. Yeah, it is. All right. This is a general list of possible properties for the current model. So this thing will query the database for all the fields and it will generate the properties. Pull it. Yeah. Because I love autocomplete. So I don't have to remember so much. Who remembers all the PHP functions? Yeah. I have a real question for you. Thank you. Thank you.