Native Linux Space Warfare: Freespace 2
Book Review: How to Implement Design Patterns in PHP
A Simple ISAPI Filter for Authentication on IIS
Installing Xdebug for use with Eclipse or Netbeans on Linux
Scrollable Tables with Floating Header using CSS
JQuery Venetian Blinds Transition Effect

Linking Symfony to a Database with Doctrine

Sunday, 25 October 15, 6:35 pm
Now that we've covered the basics of Symfony and have created a project using the installer, let's move on to see how we can access a database from our Symfony app.

The Doctrine Project produces an Object Relational Mapper (Doctrine ORM) and the Database Abstraction Layer it is built on top of (Doctrine DBAL). The Doctrine Project is an independent open-source development team with no direct connecton to Symfony or SensioLabs. However, both the Doctrine ORM and Doctrine DBAL are included in Symfony Standard Edition and use of Doctrine is very common for Symfony websites.

To get started, open app/config/parameters.yml and fill in connection details for your database server. Here's a typical example:
# This file is auto-generated during the composer install parameters: database_driver: pdo_mysql database_host: localhost database_port: null database_name: reddit_db database_user: reddit_user database_password: supersecret
If necessary, create this user by running the following SQL:
CREATE USER 'reddit_user'@'localhost' IDENTIFIED BY 'supersecret'; GRANT ALL ON reddit_db.* TO 'reddit_user'@'localhost';
You can now create the database by running the following in your project root:
php app/console doctrine:database:create

Mapping Code Objects to Rows in a Database

The underlying principle of an ORM is that code objects are mapped one-to-one to rows in a database table. These objects are called Entities, and we're going to need one for encapsulating information about our subreddits. Create the following in src/AppBundle/Entity/Subreddit.php:
<?php   namespace AppBundle\Entity;   class Subreddit { protected $name; protected $description; }
So this class will map directly to a table in our database and its properties will map to columns in that table. To help Doctrine create the required types of columns, we use annotations on each property:
<?php   namespace AppBundle\Entity;   use Doctrine\ORM\Mapping as ORM;   /** * @ORM\Entity */ class Subreddit { /** * @ORM\Column(type="string", length=255) * @ORM\Id */ protected $name;   /** * @ORM\Column(type="text") */ protected $description; }
We can now have Doctrine create the database table for this Entity:
php app/console doctrine:schema:update --force
You'll have noticed that we created the properties of the Subreddit class as protected, so in order to be able to do anything useful with our ORM, we need getters and setters for these. Doctrine can generate them as required with the following command:
php app/console doctrine:generate:entities AppBundle/Entity/Subreddit

Saving an Entity to the Database

To create a new item in the database, all we have to do is create an instance of our Entity class, populate it with the required values, and persist it to the database. Add the following action to SubredditController:
/** * @Route("/r/create/{subreddit}", name="createSubreddit") */ public function createAction($subreddit) { // Instantiate the Entity class $sr = new \AppBundle\Entity\Subreddit();   // Set its name and description $sr->setName($subreddit); $sr->setDescription('This is a subreddit about ' . $subreddit);   // Get the ORM manager $em = $this->getDoctrine()->getManager();   // Save the Entity $em->persist($sr);   // Now redirect to the view subreddit action return $this->redirectToRoute('subreddit', array('subreddit' => $subreddit)); }
Note the call to redirectToRoute() on the last line, which returns a response containing a 302 redirect to the named controller, with the parameters specified in the second array argument.

Fetching an Item from the Database

To retrive an item from the database, we need to use its "repository". The repository is basically a PHP class whose only job is to fetch entities of a particular class. We get it by calling Doctrine's getRepository() method, passing in the Entity class name. There's a shorthand form for the class name, so we can either use $this->getDoctrine()->getRepository('AppBundle\Entity\Subreddit') or the equivalent shorthand $this->getDoctrine()->getRepository('AppBundle:Subreddit'). Once we have the repository for the Entity we're interested in, we can call various methods on it to get one or more instances of that Entity according to our needs. The find() method returns the Entity with the passed-in primary key. There are similar methods for all the other properties of the Entity e.g. findByDescription() returns all Entities with a matching description.

Let's modify our view action to fetch and display the subreddit description:
/** * @Route("/r/{subreddit}", name="subreddit") */ public function viewAction($subreddit) { // Fetch the subreddit repository $repository = $this->getDoctrine()->getRepository('AppBundle:Subreddit');   // Get the subreddit entity for the request $sr = $repository->find($subreddit);   // render our view, passing in view parameters as an array return $this->render('subreddit/view.html.twig', array( 'base_dir' => realpath($this->container->getParameter('kernel.root_dir').'/..'), 'name' => $subreddit, // if the subreddit doesn't exist, $sr will be null so we display a suitable message 'description' => $sr ? $sr->getDescription() : "There doesn't seem to be anything here" )); }
You'll also need to update subreddit/view.html.twig to show the description.

Some More Advanced Stuff with Twig

Let's build on what we've got so far to create a page listing all subreddits. The controller is fairly straightforward - we get all the subreddits from the Subreddit repository, and pass them to the view:
/** * @Route("/", name="listSubreddits") */ public function listAction() { // Fetch the subreddit repository $repository = $this->getDoctrine()->getRepository('AppBundle:Subreddit');   // Get all subreddits $sr = $repository->findAll();   return $this->render('subreddit/list.html.twig', array( 'base_dir' => realpath($this->container->getParameter('kernel.root_dir').'/..'), 'subreddits' => $sr )); }
In the view template, we will iterate over all the subreddits using Twig's for..in construct:
{% extends 'base.html.twig' %}   {% block body %} <div id="wrapper"> <div id="container"> <ul> {% for subreddit in subreddits %} <li> <a href="{{ path('subreddit', { 'subreddit': subreddit.name }) }}">{{ subreddit.name }}</a> {{ subreddit.description }} </li> {% endfor %} </ul> </div> </div> {% endblock %}
Pretty simple right? Note the use of the Twig path() function, which builds the URL for each subreddit using the controller name and a list of the parameters it needs. The other thing to note is that the dot operator . is used to access object properties, equivalent to PHP's -> operator. The getter for such properties is called implicitly.

Please enter your comment in the box below. Comments will be moderated before going live. Thanks for your feedback!

Cancel Post

/xkcd/ The Wreck of the Edmund Fitzgerald