PHP Databases with Doctrine, MySQL and SQLite | Chris Worfolk | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

PHP Databases with Doctrine, MySQL and SQLite

teacher avatar Chris Worfolk

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

38 Lessons (1h 21m)
    • 1. Introduction

      0:52
    • 2. How does Doctrine work?

      3:12
    • 3. Installing Doctrine

      0:43
    • 4. Downloading the sample code

      1:23
    • 5. Running the sample code

      1:57
    • 6. Database platforms

      1:35
    • 7. What are entities?

      1:26
    • 8. Creating an entity

      2:59
    • 9. Adding getters and setters

      2:24
    • 10. Column types

      1:54
    • 11. Creating a database

      1:31
    • 12. Bootstrapping

      5:17
    • 13. Bootstrapping in SQLite

      1:00
    • 14. Configuring the CLI

      1:46
    • 15. Creating the schema

      2:02
    • 16. Example data

      1:10
    • 17. Primary key finds

      4:37
    • 18. Viewing our article

      0:39
    • 19. Repositories

      2:24
    • 20. Finding multiple items

      3:32
    • 21. Doctrine Query Language (DQL)

      3:09
    • 22. QueryBuilder

      2:59
    • 23. Managing articles

      0:27
    • 24. Updating

      3:42
    • 25. Creating

      3:37
    • 26. Deleting

      1:52
    • 27. What are relationships?

      1:37
    • 28. Adding an author entity

      3:17
    • 29. Forming a relationship

      1:50
    • 30. Updating the schema

      1:23
    • 31. Sample author data

      0:29
    • 32. Adding an author dropdown

      3:54
    • 33. Making the author editable

      1:44
    • 34. Displaying the author

      0:43
    • 35. Author pages

      3:39
    • 36. Fetching the author's articles

      1:42
    • 37. Fetching with DQL parameters

      2:12
    • 38. Final thoughts

      0:34
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

28

Students

--

Projects

About This Class

Doctrine ORM is a powerful database abstraction layer for PHP. It is used by Symfony and many other projects. In this course, we will learn how to use it, from the basics of creating classes and getting Doctrine to create our database schema automatically, to the entire CRUD process, to managing relationships.

You can use any database platform supported by Doctrine. This course includes full examples for MySQL and SQLite.

Meet Your Teacher

Chris Worfolk is a psychologist and software consultant. He is the author of How To Exit VIM and Do More, Worry Less.

See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. Introduction: welcome to this class on using doctrine O R M. By the end of the class, you understand how to use it. How do you use in your own projects on? We'll have some hands on experience in our project When we build a blawg together, this course assumes you are on existing PHP developer with some kind of local development environment. But we can use PHP is built in Web server on any day. Best platform you like, including my SQL and ask you a light all the instructions of in this class. So whatever you're working with, we can work with that. I'm Chris. I've been a software consultant for 15 years now. I can't wait to share my knowledge on my love of doctrine with you, so let's get started. 2. How does Doctrine work?: Let's look at a quick overview of how doctrine works. So doctrine is a no RM, which stands for object relation. ALS mapper. It maps objects in our native language, in this case, PHP to a relational database. Such a my SQL Oracle sequel server. So Doctrines job is to translate those objects between the database objects the rose in the database on the PHP objects and in doctrines case it works across a variety off database platforms. Its underlying layer is called the de Bell, the date based obstruction laugh on that takes care of translating everything into what we can store in the database and even come through seven query language de que el doctoring query language, which looks quite a lot like SQL. But there are some important differences will learn about later. It's all based around this concept. Off entities in generally, one database table maps toe one entity. So in this place, we've got an article entity here on the right on that would map to an article table in the database, and then, if you wanted a specific article, a specific grow that would come back as a new instance of the article class wouldn't object . And these entities are just ordinary PHP objects are not doing anything fancy. It's just a bunch off variables a bunch of on the class, and the way that we doctrine will translate that into the database is we add these little annotations like you can see here like at Entity and Act column will tell doctrine how we want this to be stored in the database. And then doctrine will handle fine details regardless of what platform were using. Doctoring uses what's called the data mapper pan, and this is where the entities on the database management are. Don't separately. So, for example, if we wanted to add a new article toe our database, we'd create an article object an instance of the entity on we'd set when she'll variables on it. And then we pass it to doctrines entity manager and say, Save this in the database now. Sometimes they this is combined. This is different pattern called active record, which, if you've used the propel, PHP or um, or if you used over language, for example, Ruby on rails uses active record pan. Everything's in one, so you would just create an article object that article object would know how to save itself to the database. And so you just created Cook save, which might seem a little simpler. But the way doctrine does it decoupling the entities and the entity Management means that it's a lot loosely coupled, a lot easier to work with and more performance as well. That's a quick overview of how doctor and work now let's dive in and look at some code. 3. Installing Doctrine: the easiest way to install doctrine is using composer. You get all the information off package ist, and then we could just do a composer, require Dr Ian slash around. If you want to do a more obscure way, you can go to the doctor and website, and they do do bundle releases on. In the case of this project, you wanted to do evil of those things because I'm going to give you some sample code that already has doctrine included in the composer requirements. But if you inserting this into a new project, you'd probably want to use the composer command and just bring in the normal way. 4. Downloading the sample code: I've create some sample code to help get you started, and it's available on Get Hub. So if you look at the resources section for this lesson, you'll find the link which will lead you here to this. Get Herb Repo. The best way is just Teoh do a standard get clone and then you just get the full get reap over everything you need in That's easiest. You can also download zip file. And one thing to be aware off is that there are two main branches you want. Look out for in this repost of the master branch, which is the one on one now that has all the completed code. So if you download that and you will not, you will get everything that will have done by the end off the course. But if you wanted to get started on day, used a sample code that we're gonna work through in these examples so that you can follow along, you want to go to the Project Crunch as well. This has all the new stuff taken out of it, so you'll start with the Project branch, even do it get check out to this band Children's download this branch on. Then we'll work through this together, adding in all the code that we're going to learn for Doc Tree and in the end will end it with something that looks like the master branch. So head over to the link on, get hooked on, get this, get reprieve a cloned or downloaded. 5. Running the sample code: once you've got the example Project code downloaded. You've gone to the project branch of that. Then we can get that up and running. So how you do that is a little up to you. If you've got an existing a lamp or ma'am por one stack and you want to use Apache, then you just need to point it to the public directory inside of that code. So just CD into public. Andi just set your Web server up to read from that directory. But if you're not sure how to do that or just don't want to do that, there is an easier way we can use Patris built in Web server. So I'm on the project code here. So if I was in the root directory Andi, let's just do it, get status. You can see I'm on this project around here and get and I'm gonna exceed into public. And then I'm going to start ph bees built in server something that do Okay, great. Looks like that's from running. And now if I get my Web browser and had refresh looks like that's running so local host 8000 which is the port we configured it to run on what we run. The command here. It's running, and at the moment there's not very much here. That's just head and an empty home page. We're going to add more of that stuff as we go through the course. But first step is just to get yourself open running. So you have a Web server that could be pitch peas, internal Web servers we used here. But you could also use Apache or whatever else you've got running on. Let's all get to this place, and then we'll go on and we'll start adding some doctoring. 6. Database platforms: next part of the set up, you are gonna need a database again. You've got a bunch of options here for purposes of demonstration. I'm going to use my SQL. I've got my Skrill running on my computer. I've also got pitch P, my admin installed so that we can run the database commands. What if you want to use a different database platform? Totally fine doctrine supports a bunch of different ones, so you can use whatever you want. If you want to use a different one, that's fine. As long as you can get in to create a database and one some SQL scripts. That's fine. If you haven't got anything running right now and you're thinking I was the easiest way to do this. The easiest way is to use sq a light because then you don't need any database server. It's all file waste. The only thing you'll need is some kind off little application to manage that everyone skips, for example, on Mac, I have this one here. It's called SQL pro fresco. A light on and here I've got the sample database we're gonna be using. That works totally fine. I will put in the resources section or a separate lecturer. Put a list off potential SQL like claims you could use if you want to go that route. But whichever platform you want to use find my scurrilous Fine. SQL lights. Fine. Any other platform that doctrine supports its fine. As long as you can go in there and create a database that we can then use for duck tree, then it will be okay. 7. What are entities?: before we dive in. Let's take a quick theoretical look at entities during your database, you might have a bunch of different tables. Maybe there's a customer table and there's an order table and then address table. When we translate needs into code, each one of these tables would have its own entity. So that be an address class A customer class in order. Class one table generally not always generally translates to warn NT one class and then inside that class that would represent the columns. So if you've got this table with customers and they've got idea in a name and a surname on the country in the class, each one of those would be represented by an instance variable. And then the way we get doctoring to connect this all is we have these annotations, so we might say something like, Okay, this class is gonna be an entity, and we wanted to map to a table called articles on. Then we're going to give it a bunch of field. So got his I d. That's that s a primary key. We want you to auto increment it on and wants to be a number. And then got name. That's a string on. We annotate each up of the classes and instant variables. Teoh tell doctoring what kind of data this should be so it knows when mapping it to the underlying database. 8. Creating an entity: I must go ahead and create our first entity. So in source code folder, I'm just going to start by bringing you folder. I'm gonna call Entity who Ground on Land. Let's just write some PHP code. So it's the name Space to app entity. Andi, Before we start the class, we're gonna add on annotation to say that this is an entity which is something doctrines going to read so that it knows what we want to do here on. Then we're gonna have some more intentions. First off, we want a primary key Democrats as a type into a chair. When a tell doctrine that it's an I d on that, we want it to be a generate value. So normally this will translate to an auto increment ID I d, um, will call that I d. That should save this so we could stop here right now. We've got a article classroom, and I d We've probably want some more columns. So So it's answer more looks have that's a string on. This is gonna bay. Call this name Andi to save this type in all of this out again. Let's create one for the U. R l call that slug again. It's gonna be type of strength on because it's the slug form to the U R l of the blood post . We want listo have a unique constraint on as well. Um, what else do we need? Well, we probably need a body, right? Um, let's add one for image so we can. And ahead of image to it, Let's put the body in. Now the body. We want to be a text type rather than a string type. Talk about column type soon. Onda. We probably want a date published. A swell Let's make that date time type. Okay, so essentially we've got our entity here in doctrine knows everything about it, but everything's private the moment. So we need to add some gas and says, Let's do that in the next lesson. 9. Adding getters and setters: all of our variables on our entity a private. So let's add some gets and status to them, split less out in their separate lesson because you might just get bored. And when the skip it because there is nothing glamorous about it. He used to back in the day, we used to auto generate these with doctoring, but it's not really not really a big fan of it anymore. Because if you do that, then you get getter and setter for everything on. We only really want to enable access to the things that we really need to. Even though in this example I am just going to give it everything most. These ey're gonna look identical stew slug. Couple of the magna Looks like you're different. Maybe just one of them. And whilst we got, we got body and we've got published making this one slightly different. So that's type casters. Date time. We could allow in no value on baskets, bringing the time classes may not consistent. Okay, cool. So we have our entity. We have a bunch of columns that we've marked up with doctoring special annotations to tell doctoring what we want them to pay, and we've added some getting, says Tol. I was to use it as well 10. Column types: Let's look at the column types available with Doc Tree. So when we create our first end to, we marked the column up as a type string to say, We want the name property to be strength And just like the underlying database platforms, Doctrine has a wide range of column type, says things like Straighten biggest rings like taxed Boolean XGO IDs, a whole range of different numbers from introduced decimals, bigger numbers, dates and times. Andi raise, maybe even Jason. All of these you can use on you just use them like we used a string instead. That string you just put in the column type you want, and then doctrine takes care of mapping that to the underlying database platform. So, for example, string normally really easy to map because most at best, but palms have ah bar chart type. Some of them are a bit more complex. For example, my SQL doesn't have a Boolean type, said doctrine. Jokes, creates a number field with 01 in and stores the Boolean is that but doctrine takes care of all of that forest. So we just getting Boolean on the front end like a PHP true or false and doctrine handles, translating that into a zero or one. Similarly, if you use the Jason failed, for example, that will. If the Davis platform has underlying support for Jason Data, then it will use that if no Doc train will just Jason and code it and store in a big text field. But again, we don't need to worry about that. We just hell doctoring how we want the data to look in. PHP and doctrine handles translating that into whatever is relevant for that database platform. 11. Creating a database: we've grabbed our entity and now we need to hook it up to a database. So we need to go create a database. Now, if using a my SQL appeared to be my have been stack. That's easy enough to do. And let's do it together now. So just create new and I'm just going to call mine doctrine now. RM find something sensible. Okay, cool on. Let's create user for it as well. So again, hundreds continues the use name, Doctor. Now ram on the password. Let's copy and paste straight out here. There's gonna be doctoring our home as well on the authentication for PHP. You want to select Native my SQL authentication on by default? It's going to grant us all privileges on that database on. We don't need any global privileges, so you can just go, okay? And it looks good to me. We've got a database. What if you're using a separate dec best platform a different one, not my SQL Totally fine. Jets create blank database in the usual way Weapons. If you're using sq a light, then great. You don't need to do any of this. It will just work Assumes we write the code. So if using, ask you a light. Don't worry. You can skip this step 12. Bootstrapping: we create, our first entity will get a database. Now we need to connect everything together. So tell doctoring how to connect to the database so that we can start using it. Select, create a service. I'm using a little micro framework called Slim in the example code. And we're gonna create service that will connect that up so you could shove this code anywhere. But this is a good architecture to use. We need a couple of things from Doc Train. So we need the set up on way. Don't. Yeah, we do need that we need and team manager. That's the thing we need. We're gonna create little factory here. We'll save this. Just call it dead. Best factory dot PHP and we'll stick it into the services directory on. And this will create doctrine and see manager. That's first add a boolean that says, Are we in depth mode? Yes. This means that things will refresh easier, But when production that him we want to set this to false to optimize it for performance on let's give it in the matter data it needs as well. So we using annotation, which isn't the only option you can also configure it by things like XML rather than using the annotations that we used here could do that in XML, But we're not going it for this project. So we're going to call this crate annotation matter, data configuration on. We're gonna pass it the directory path so that doctrine knows where the look and we're gonna pass in our is deaf variable as well. So that will tell it to read all of the annotations on. Then we need to pass it the database parameters. So I'm going to use my SQL. You could use whatever driver you liked. I'm not Sly. Needs up a bit to be a bit prettier. Okay, so we have aren't db parameters are. And then finally, we're gonna return a new entity manager. So in a call, that factory method and we're gonna pass in the Devi Brams on the matter Decter. Okay, it looks good. So the next thing we need to do is wire this up as a service in our little application framework. If you were just writing pitch being in your own project instead of doing this, you could just say entity manager, and then you could just do whatever you wanted with this variable in this case, the way we're gonna Why this up inside of our framework is I'm going to bring the rest factory in on here. What? We have greatness, dependency, injection container, stick it appear well, just cracked function here. Watch its call that. So now, when? Anywhere in application. When we call the dependency injection container and say, Get me the database, it's gonna goto a data race factory and create this doctrine and team manager. 13. Bootstrapping in SQLite: those of you who were using ask you a light. It's a bit sympathetic. Configure the DB programs such as quickly Take a look at how we do that. Dr. Just Pio. Ask you out. Light Got it on. Then. Only need to do is pass a path in as well. Yeah, you could make this whatever you want, but if we just want to dump it into the roof of the project, it would just be something like that. And that's all you need for rescue a light. Just tell it the driver on the path you want to do it using any of the debt. Best platform. You're probably using this and put in the user name and password and host in, but ask you a lights a bit simpler. 14. Configuring the CLI: one more piece off bootstrapping we want to do is if we want to use the command line tools that Dr Incomes, we've we need to enable those as well, because this will get it to work inside of application. But if we're gonna run it from the command line, we until doctoring had to do that on it's pretty easy to do that. Especially the way we've done it. So we'll bring in all dependencies how much it's called this cli conflict up. It fee has to be called desk this exactly. So in the roof project, see light dash conflict a PHP on in here When I created and to Manager gits call Ah, database factory on. Then we're gonna Teoh Special Consul Runner And that's all we need. So is this create helper set that we need to return from the file that needs to take an entity manager on because of the way we've boot start to wrap. This is how we create that entity manager Inside the APP on, we needed to local the composer dependencies in a swell these couple of lines all you need to get the command line come pick working. So we should now be able to go over to the command line, come thick and run some commands 15. Creating the schema: must recap what we've done so far. So we've create this and to hear we've connected our application by bootstrap in doctrine and you've also configured the command line tools. Now, at this point, of course, our database is empty on. We could go in and just manually create the tables using SQL. That would be completely fine. Do that on projects. Sometimes we'd actually need to do that because doctrine can do it for us because we've already told it. Using these annotations how we want the database to look and so a week and just get a command line and we can run what's press enter into happens. The warning is that we shouldn't be running this in a production environment, which is true. But that's fine because we just here developing its great in the database, and it said it has created it successfully. So now if we go back to our database on Perfect that we are so we have this article table. Now we look a structure. It's gone. I d. With primary key in an auto increment slug with a unique reference. Great date, time, long text, everything we need Perfect. So without having to write any SQL all we've just told our entity classes What we want to look like in the database doctrine has gone ahead and translated that and the beauty years is that you can do this across any platform. So you've got this hooked up to poke progress find from the same command. I got this hooked up. Two sq a light. Fine. Run the same command and you will get a database. There are some little changes around, say, column types and what's available, but doctrine will take care of all of that for us. 16. Example data: Now that we've got everything working, I've got one little task for you. So we've got this article table, but currently it's empty. So let's insert some sample data into that. So I'm going to do this for pitch being my admin Now in the project here, we've got this assets on. We have this article's toe SQL that would just provide some examples. And all you have to do is import this file on then. Yet we've got a bunch of sample articles which will use in there for using SQL Light. This is a time you wanna pull up your little clients. So in my case here, I could just Gramp all of this sample SQL and just run into the query box and just haven't run that again. You'll then end up with a bunch of sample articles in your SQL light database. So ever platform you're using just import this little articles that SQL script from assets on that will give us some sample data to work with as we go forward and build out the example blogged 17. Primary key finds: we create our entity. We've connected to a database. We've got some data in there. So now let's look at retrieving data. And the simplest way to do that is with a primary key find. We just give it prime. Okey, we say Get is this article. Let's demonstrate how we would do that. First off, let's create an article controller. I'm just gonna copy and paste this default controller called article Controller save as article controlled a PHP in the control of directory. Andi, I use this gypsy at, but let's bring in the not found exception in case we need to throw that that spell article correctly on. Then let's change this to called view on Let's pull on article out. So we'll say, access our dependency container. They give us the database, which is the doctrine entity manager that we returned in database factory, and we're gonna use the find method. This takes two parameters that the first is the entity we want. We need to put the full path in there so app entity article cause we won't find an article , and then that's just hard coded toe one. For now, we'll say, gets the article Vaidi one. Um Then well, tell it to render a template class called a PHP on into here. We will pass the article just found good. It looks good to may. So what else do we need to do? Well, we need to create this template. We've told it to render out. So let's do that. The template in languages called mustache that we're using here, you can use any template language you want, but for for the purposes of this example, Project Moustache is already configured. So are you recommend doing that? So this article here is the article entity on. We're going to start using the Getem efforts here so we'll do a hate one tag. Let's save this. So we get some highlighting article that hitched him out. That's what we called it in the code on. Were saying, get a published date that said Date Last chips. A little helper here to say, Render a date. Ask it for the image decorative image. So let's just add a blank. Don't tag, get body. The body's got hate mail code in, so we need free curly brackets and mustache. I'm making closes. Okay, there we get a final thing to do, then it's just a wire up this route in a router. So that's good. Damn, it had AP and it's a get route. That's a article on. We'll have Variable called slug. I really liked the article controller view method that we just created. So now when the Rita sees slash article slash whatever the slow gives l send it to the article controllers view Mefford. This is the control of created which is create this view Mefford that's gonna find on article entity and render it out to this article template there. We've also just created Let's see if it works. So it's good article. We can put any thing in here at the moment on. We've gotten her out, so let's troubleshoot this in the next lesson. 18. Viewing our article: too wide. Everything up to see how article this is A super simple fix it just missing a semicolon here on if we re fresh Boom. Got a rascal. So title date image on the whole article. Brilliant. Now, the only issue here at the moment is that this doesn't doesn't really mean anything, like you can put in whatever you want and we've hard coded it to get the article idea of one. So in the next lesson, let's look at how we commit that dynamic. So based on what you passing here, you'll get the relevant article back. 19. Repositories: Let's Go had to make her control of a bit more intelligent so that it takes the slug that we're passing it on finds a relevant article less just company, obviously old code and catching on, a reference that later caused view. BK then would start modifying this here and we're going to use what are called repositories . So this is a really useful feature of doctrine that here was saying getters, the repository for the article entity. And then this gives us a load of useful functions, one of which is find a one by So find one record and then we can pass it on array, which essentially is the where clause. If you were doing in SQL Query, you would say OK, finally, one record by slug equals this while we're doing the same thing here with this repository saying fine one by slug on the way Slim works is the arcs are going to come in this array. So because in index, we have we put this place older here called slug. When we come into here, it's going to give us this arcs around, one of which is gonna be slugged and we're gonna say find one by slug on, then I just think it's not found. Look, I now, for an exception specifically and not found exception to, say, display the not found page if it is found willing to go ahead and render that same article again. So let's look at here. There's nothing called yoga, too, So we should get in fact, in order to call this properly, we need to pass the request in once. Quickly fixed that boom. Okay? Yeah. Now we've got not found error. But if we take one of the other slugs now, So let's take this morning of a NASA flow and we insert Listen to you. Our perfect. So now when you pass the slug in, it goes and finds a relevant article and pulls that back for us. 20. Finding multiple items: in the previous lesson. We used this find worn by function. Just find a specific article. What if we want to find more records while we can do that? And Teoh demonstrate that Let's go to the home page and let's list out all the articles we've got. So how do we do that? What's festival opening behind patch code? Andi, get a very good article. We'll start by calling DB and again we're gonna use the repositories. So say APP and t article instead of find one by, we're gonna call find by now the first argument we passed in us before. It's like the where clause it's like, What are we finding? And in this case, we just want to find everything soldiers passing an MP array. We can also pass in a second array, which is the ordering on in this case, we want the newest articles to be at the top. So let's say or have I published on we're gonna order that in descending, so chips like in SQL, you can do order by color, name, ask or desk. You can do the same thing here where you pass in the key here. This isn't actually the date based column name. It's the property we using here. So this could actually have a different name in the database. But we're saying we want to order it by publish in descending order. And then that passes go to template as well. So saying articles pass in the articles and then the everything will need to do is modify the home page because right now it's just hate one. So let's go ahead and list these out. There's already some sample CSS and the project to make this nice and easy on one loop through the articles. Yeah, each one that's creating article tank on inside there. Let's link it to the page, which is created so article slash slug. When you use the image, that too okay, a man. She's a hate to tag on again. It's just really lays him. Copy and paste this calling get name. So now it's gonna take through the articles that we found here using the repository which it found them all, ordered them by public state descending. And then we're just gonna look through them at B Imogen at the header in. Let's see if it works and it does perfect. So we've now got all free off our articles listed there on weekend trips. Click on them Perfect. So now received. Single on we retrieved multiple goes as well. 21. Doctrine Query Language (DQL): We got our home page and it's looking really nice. But there is a problem. We look at this article, it's not actually supposed to be published until the year 2060 which is almost certainly in the future. Unless, of course, is somehow survived and it's still relevant in 40 years time, which obviously it will not be so. We probably shouldn't display this on the home page. But how would we do that? Because we look here, we could say OK, we could say it published and give it a certain value. But in SQL, we would say, like greater than or less than how do we do that? We can't do that with repositories, unfortunately, so we're going to need to use something else and that something else is doctoring query language. This allows us to write SQL like queries to give us that extra functionality and power. But it's important to note that doctrine query language is not SQL. They are differently. Functions like differently and doing one thing and one weren't necessarily working the over . But it is what we need here. So let's look at how we do that. First of all, next, just move this code my old home page, And then we'll go ahead and modify this to use Dr Incurring. So first of all, that's right rdq l statement on we're going to select. It's like a which will be our AP and to see article. And we're going to say where a published is greater, less than or equal to current Timestamp It's a little help of function that's just built in on finally gonna order by as well again we're gonna eat, published asked Desk. So you said, find me the articles where the publish state is less than the current time stamp and then order by so that the newest articles are at the top. Nice. Okay, so what do we do with this statement? Well, we need to get a query object. I can We're gonna get the entity manager, and we're gonna use that cui eight query function and pass it in de que el we just defined . And once we've done that weekend just called query and get result on ST articles and we're already passing articles in here. So in theory, for refresh now, perfect. So it's now giving us all the articles back that were published in the past. It's no longer giving us the one that's scheduled for the future, and it's still in the correct order. 22. QueryBuilder: So far, we've retrieved data with primary key fines with the repositories on with doctrine query language. Now there is 1/4 way to do it, which is called Query Builder. And for the sake of completeness on briefly going to talk about that and wait, it's rubbish, and you don't need to bother learning it. So let's look at on example, If we wanted Teoh pulling out here, the first thing you need is to crack query buildup. You can just get this lesson. If you don't care about knowing everything about doctoring what we're gonna take that on, then we're gonna call some stuff in it. So select a we're gonna pass it from, uh, we were looking parameters in Dr Inquiry language in a future last time. Everything we need here is to say the, you know, accent again. Someone threw belts it when they're going to call this gas aquarium effort, which converts into a query. We only want to find one article, and then at this point, we can just render it out as no. So that's how you would use query builder. I don't think you ever need to use this, because if you can do things super simple. Then he's just going to use The repository is nice as lovely. It's clean. If you need to do all this messing about with where clauses and parameters and stuff, then you've got this lovely deep your help, which looks pretty similar to SQL. It's all in one big string. No messing about here. You just you're calling loads of different functions. I just looked super messy to me. I don't like it. I don't think it's any easier than using doctrine query language. But for the sake of completeness, I want to let you know that it is there, and it is something you could explore if you wanted to, but you probably won't need it. 23. Managing articles: So far, we've used doctoring to Fatchett data. But what if we want to create update, delete? The Hawk would cycle. We can do that with doctoring. Of course on. In fact, there's already a little admin console here, but at the moment, it doesn't do anything. Nothing happens, so let's look at how we can make this little work. 24. Updating: Okay, We've got our admin screen here and on it it screen as well and free. Just go into this. Been controller here on this edit function that renders out this at its screen. There's already a form that it looks good but doesn't really do anything. So let's make it do something. So if we posted this route, let's just check if it's a post, then we'll know the users posting data. And if so, we can call the SAT methods that we create on NC earlier. Yeah, so slug image on the body. We'll take these names here as well. So obviously these names come from the forms where we've create the names here. Okay, let's see what happens. So strike calling it secret of a yoga teach to and will help date on super. So it's there. But if we brush, refresh, it disappears. What's going on? Well, we've updated the entity here, but we haven't told Dr into save it back to the database. So even though we're updating the article entity and I'm passing it to the template when we render it, it's not actually saved to the database. So when we refresh, it's gone So in order to do that, we need to do some additional code round. Can we need to get entity manager on? We need to sell it to persist. The changes to article there was one additional thing we need to do is well, wow, it's to run the flush command on the entity, manage it. So by default, when you do this, it doesn't actually persist to the database until you call flush on. The reason for this is that you might be updating several entities at one time, so we might be calling this on, say, three different article entities. And then we won't want to write it all to the database at once. Or maybe you're creating some relationship. Several things in a single transaction. For example, we might have some comments on the article, and the comments need to go in with the article I D. So you need to persist it all at the same time. So you'd create everything you'd call persist on each of them, and then you just call one flush Command to tell doctoring, to write everything to the database on one final thing is, when we've done that, let's give the user a success message. We'll say our total dated successfully. Okay, great. So now let's try it. Yeah, but if we go back how it's updated and it persisted to the database as well. We can undo that change and we go back. And then we g o is now persisting these changes to the database. 25. Creating: we can now add it, our blood post. But what if we want to create a new one? What The moment this link doesn't work because there's no function. So let's go ahead and create that function. We have already got some code that looks Egypt. It looks basically identical to the Edit Farm except, it says create appear on. I'll be perfect for what we need, so we just need to go ahead and create the actual create method here. I think the best thing to do is we'll just take this at it. Method. We'll call it create, and I will make some changes for it would also need to wire up in index. But as you can see, we've already done this as well. So let's look at what changes we would want to make here. And it turns out it looks remarkably similar. So instead off creating getting an existing entity from a database which is going to say new article, bring that in appear. In fact, that's already done perfect. So this will give us a blank entity and we can then go head on, populate it with names, looked the image, the body perfect and then we just persist in the same way. So we don't need to worry about whether the entity we're trying to persist already exists. I e s an existing Postwar editing or wherever its brand new we just created Entity fill out the failed and doctrine will look doctoring will know under the hood whether it's brand new or not based on their primary keys. And if it's new, it'll created. If it exists, it will edit it. So we probably don't need the success message because that stress can give a blank forms and said, Redirect literate over the nest. Literally learning change of God is in our code where we're updating the entity instead of using an existing one we're just going to use a new one on. We're going to render out the create formas well, but we're still gonna pass the article Anteon scare had him run that and see what happens. It goes test are to go on hit the publish button and it seemed publish cannot be no. So there is one additional change we do need to make and that's we need to set published A on it and we'll just make that now. Okay, we were in the code on Boom there. We've got test rto, it's created and we can go in and we can then edit that as any existing post. So really very little difference between adding existing entities on creating new ones because it's based off thes simple objects and not small objects from regular objects that we use for entities. You can create a new one. You can pull out an existing one, and then you just tell doctoring to persist it on it does it all the work for you. 26. Deleting: we've created and edited some entities. But what if we want to remove them? Well, we've already got this little delete form here, which is pretty straightforward. Just sends an action off delete, but nothing a work at the moment because we haven't written any code to handle that. So let's go, Right, the server code. It wants to be in the edit. Mefford not live. Create method. It's easy to get them confused because they look pretty similar. But here we are in the edit. Mefford, I'm we're gonna look a request object and say Get get the action program and say is a delete. And if so, we'll do some deleting here. And if Noel Jets fall through to be a regular edit and to delete it pretty straightforward , we're going to start by getting the entity manager on. Instead of the persist method, we're gonna call the remove method, and we're gonna pass it in the article empty. So we've got the article entity already here and then just like before, we're gonna flush. So we're going to tell doctoring to run all the commands that got queued up. In this case, it's just one just removing this article entity because this page one exist anymore. Much redirect back to the Upton screen. So just like we were calling, persist here. We're calling the move method to tell Dr and we don't want this entity to exist in the database anymore. We're calling the Flush, and then we're redirecting back to the admin. Let's give it a go. See what happens on Perfect. So it's deleted it, and it's no longer in the list of the men articles that have been redirected back to. 27. What are relationships?: in this lesson will look at when we might want to use relationships and how they work. So we've got, say, a customer object and they might have a name and surname and email address. But they also might have orders. Andi, this would really be a separate table in our database. And therefore it needs to be a separate entity because the order will have a bunch of items in multiple orders. And therefore, in a relational world, it would be a separate table and therefore it's a separate entity. So how do we How do we link knows together? Well, in a relational database world, you'd have your customer table. You have the order table on and you on your orders table. You would have a customer I d. That links it to the I. D. Off customer. Pretty straightforward in doctrine, it's similar to that. But doctrine handles all the I d matching for hours. So the customers entity would simply haven't orders array containing order objects on the order entity would have a custom object that we could just call. So again, doctrine is handling a lot of this four hours, and we just need to say there's a relationship here on doctrine will hydrate all the data and handle persisting it back to the day best as well. So in this module, we're going to look at how we could add an author to an article, and we'll look at the relationship both ways and see how we manage that relationship menus that relationship in the code. 28. Adding an author entity: So far, we've done some pretty cool stuff with getting our blood posts in and out using doctrine. But unfortunately, life isn't normally a simple as just one entity where you can pull things out of a lot of the times. We've got big, complex databases with relationships, So in this module we're going to take a look at those. And for an example, we're going to try adding an offer to our blood posts. So let's start by creating an author entity. Congress offered a pitch. P We'll stick it into the entity directory, give it the entity named Space, and we're gonna pull in a little utility from doctrine called Array Collection when he was a bit further down on, then let's take it as an entity begin creating a class. So as before, let's just grab some of the annotations of article probably gonna want a name on I D. So that's been lazy and copy in pesos. And then the next thing we want to do is want toe lengthy offer to the article entity, and to do this, we're going to use a relationship sanitation. In this case, it's one too many, because one offer could have many articles. Target Entity is article and it will be mapped by. And then one final important thing we need to do his only correct instructor. We're going to say that this articles needs to be an array collection. It's a collection off objects. And so you win. If we just creating up by new offer, it's gonna have an empty array of objects. But it is still gonna be there. Okay, so let's add some gas and sounds again. That's make life easy on ourselves. Poli's because that look exactly the same for the name on the I d. And then we're gonna add one at the Boston could get articles. I don't need a set up for this because you would never sat the array collection. You would only get in and modifier, and that's going to return the articles. Okay, so great. This entity offer with an i. D. And the name and we've also told it that's gonna map to articles 29. Forming a relationship: we've created our offer entity, but we also need to link that to articles and do the oversight of the relationship. Let's go do that now on under. Published What? The new key here. And this is the oversight of the relationships. Instead of using one too many were using many to one many articles could map toe one offer on Target Entity is offer. It's invest by articles. Yeah, go this or for so you know it's here were given the offer entity and we're saying it's articles, which is what we called it here and then here was saying it's mapped by offer. The reason we use map buying inverse buys because of the one to many managed to one different slight difference in tax, and we've called it offer here. Hence why we're calling offer here and then that's out to get her and set up for this because on the article want to be able to get inside the offer so they get a pretty straightforward just return that I will do so for as well on Dhere Weaken Typecast. It has an offer. Sorry. Weaken type. Check it So we make sure that the passing on or for object in on. If I was fine, we'll assign it that great. So we've now created both sides of the relationship in our entity classes. 30. Updating the schema: through linked our offer and our articles together. There is one small correction We need to make a call this target here and it should actually be target entity. But that having said, we've now link them together and we want those changes to be reflected in the database. Well, doctrine will do that for us. So if we go back to the command line, we use the schema tool again and we asked for an update this time. Okay, that looks good. So that's warning is about production again. But this is really a task rule on if he just ruin it with update, have we want it to actually run. Then we just had dash dash forced to the end and got a nice, happy green success message. So doctrines telling us it's updated the database schema to reflect this. Let's have a look. You can do this in whatever Davis money until you using. But look, it looks good here. We've got this offer table, nothing in it at the moment. Just a name in an I d. On go this article table, and it's now at least offer i d column as well toe link it in my looks. Perfect 31. Sample author data: just to you help things speed Along here, we've got a never sample SQL file. So however, this import we need, however you're loading in whatever client you using this author's dice que well, and if you just load that into your database, that will give you some sample offers and that just load that in and then we'll use that in the next lesson to give us some data. 32. Adding an author dropdown: Now that we've got these offers into our CMS, let's look at how we can add them into the edit screen so that we can control it from there . So let's close all this down and open up the admin controller. We'll go to the edit function here, and the first thing we're going on gonna want to do is just Pilar a lest off the office. So we know who we have to select from. We'll go ahead and get the entity manager and we can just use a repository here. I will do nicely and we'll say find, but I will find them all one just older than by name. Okay, Looks good on then. Here, we'll pass it down. But we use a little help of function that will, right? So just because it's pretty hard in the template in language to do a drop down, we'll move some of this logic back into the controller, and fight would commit its private function because nothing else is gonna access it. Well, say offers on literally article in a swell. Okay, so let's create an array of potential options. I will leap through the offers and each one will add a new option on Hates him. I was gonna look something like this. Andi, we'll need to pass the Yeah, that looks nice. So the first thing is the value. So we're gonna wanna pass in the I. D here. Now, the next one's going to indicate whether it's selected or not. So we'll say, Is the offer the same as they offer? That's already selected for the article. If it is, what other selected and if not no worries. And then finally, we've got the display value. So I got off the name there. Once we've looked for bringing hair, we've now got an array of these option values and soldiers implode them. Okay, looks nice. So next thing we need to do is go into the at it template and just at that offer field. So here it's a label. Before offer on lot, I select well at all. Those option tanks were created. Okay, let's see how that works. Great. So we've now at this or for drop down, and it's got all of the offers in listed in alphabetical order. Perfect. We haven't added any kowtow handle this. So let's do the handling of submission in the next lesson. 33. Making the author editable: we've added this or for drop down to the page. But when actually doing anything with it when we post it sluts, girl and change that now. So here we are in the edit and you can see here we are changing all the values on just one that do one for offer as well. But this is going to be a little different because we're posting the I D. But remember in set offer, we've been up the entity. We need to pass the or for object in there. So to do that, let's go get it. We'll get the entity manager on. We can just do a simple find here because we're sending the offer. I d we'll just pull that at Let's get prime So we'll take the offer i d. From the questions that finding off object and use that to set the offer. Cool. So let's go through on a sign each of these. So sign this one to Mackenzie, not just refresh to make sure it's stuck. It is still set unless Girlfriend Eelam Also, that's a sign, this one to Mackenzie as well. If you and al oh moves subscriber, you might recognize some of these names and we'll update those as well. Okay, great. So now all of our articles have an offer assigned to them. 34. Displaying the author: each of our articles now has an offer. So let's go ahead and actually use that and stick in on the front end. So we're gonna bring up our article template here. We say published here on Let's up the offer after this. So from the article object when you get the offer on and pull that name out and then we'll close that. Okay, let's check that much. Working on the front end on Boom now. Got the offer name there as well. 35. Author pages: We've now successfully got article offer relationship working. But wouldn't it be good if you could actually see a list of all the articles by a certain offer? Well, because we created the relationship on both sides, we can quite easily do that. Let's give that a go. And the first thing we're gonna need is to create an offer controller. So let's go ahead and steal the top half of this article Controller would call it or for controlling pitch, be call this method or for Andi. Well, I will keep this, but instead of looking for an article now gonna be looking for on offer Andi, we can actually change this to a simple find because we don't have have any slugs. So finally is given to this and say you'll find it off using the I D. Yeah, that's fine. And we'll say if it doesn't exist, then throw for for ever. And then we'll render out on offer page on that template for this as well. Often haste amount. I want to go into the templates. Was it one already there? There is evidence funds just override it. Clean that up from the put it repo Okay, so we're not gonna find an offer by the idea, and we're gonna render out this offer page, so that's link that route up. So it's a slash off i d limited controller or for controller, or for is what we call the math name. And then let's go into this article here. Andi here. Let's turn this into a link. Okay, so, yeah, not taking us to the offer page, and that's working. But it's not Lessing at articles yet, so let's look at that in the next lesson. 36. Fetching the author's articles: we've got off the page, but there's no articles on it yet, so let's look at how we could do that. There's a couple of ways the first is Let's start by getting this goat here at the end. So now if we supplied some articles, it will loop through them on Super easy Way to do it. We could just say articles equals offer, get articles, and we'll use that getter Great Earlier on. Perfect. There is one issue we normally want the newest first and we can see actually see over way around at the moment. So how could we sort out what we could have an annotation onto the or first? So here we've got this one too many when we pull out articles and we could add an order by annotation and say, published descending when we order them. Okay, so now if we re fresh perfect now the newest one is at the front. This is the simplest way. It doesn't give us much control as to how we pull the articles out because it's juts, just literally getting everything. But for small amounts of records, this could be a solution, or we'll look at something about my complex in the next lesson. 37. Fetching with DQL parameters: So we've got our offer page where articles listed out. But what happens if we wanted to make the query a bit more complex? Well, this would be a good chance to use doctrine, query language, de que el with some parameters. And we could do that something like this. So if we bring up the offer again on and let's create D'Qwell statement slept articles from the name space A. Yeah, then we're going to say where article offer equals colon. Often that's a parameter. And finally, I'm saying on the by published descending Okay, cool. So we could also have the nouveau where Klaus here something like only show me articles have been published so far and excludes articles from the future just like we did on the home page. But let's not do that for them. So let's go ahead and create Queary getting entity manager past seven. And then we need to set the value of this parameter because we've used this call on offer to say we're going faster program in here just like preparing a PDO statement on, and we're gonna pass the offer object in there, and finally we're gonna say articles equals query gap results. And then let's change this to articles and then fearing. Now, when we refresh, we should get the same thing. But using a more sophisticated query, OK, looks good. And here again we can get a Patrick's page and it pulls out Patrick's article as well. Perfect. So there we have. How an example of passing parameters in and managing relationships with doctoring. 38. Final thoughts: Congratulations on completing this class on doctrine. Our you now have Teoh use doctrine create entities, build database schemers manage relationships between different entities on do the entire could cycle of creating, deleting, modifying records. So you're all set to use doctrine. Don't forget to share a link to your complete code in the project section so your classmates can see how you've done whatever you're moving on to next. I wish you happy Programming on the best of luck. See you soon.