Symfony - Day 5.5 - Working with the Database

Previously, I configured Propel, built my database, and built my models. Now I want to use those models to do some work in the database.

A New Route
I'm going to create three new routes for the various actions I want to perform on my database: select, insert, update, delete.

I'm going to update Company/DemoBundle/Resources/config/routing.yml and add these lines:


company_demo_insert:
    pattern: /db/insert/{name}/{price}/{desc}
    defaults: { _controller: CompanyDemoBundle:Default:insert }
    
company_demo_view:
    pattern: /db/view/{id}
    defaults: { _controller: CompanyDemoBundle:Default:view }

company_demo_change_price:
    pattern: /db/update/{id}/{price}
    defaults: { _controller: CompanyDemoBundle:Default:update }
    
company_demo_delete:
    pattern: /db/delete/{id}
    defaults: { _controller: CompanyDemoBundle:Default:delete }

This sets up four new routes that will let us perform some basic functions on our database.


Accessing the Model
To have access to the model I need to update my DefaultController class to use the Product classes. Additionally, I want to return Response objects so I'm updating my class to this:


use Company\DemoBundle\Model\Product;
use Company\DemoBundle\Model\ProductQuery;
use Symfony\Bundle\FrameworkBundle\Controller\Controller;
use Symfony\Component\HttpFoundation\Response;

class DefaultController extends Controller
{
   ...



Persisting an Object
We can use our first route company_demo_insert to create our first record in our database. In our DefaultController we need to create the appropriate controller:


public function insertAction($name, $price, $desc)
{
     $product = new Product();
     $product->setName($name);
     $product->setPrice($price);
     $product->setDescription($desc);
        
     $product->save();
        
     return new Response("Added: " . $product->getId());
}

Fairly straightforward right? We defined the controller as insertAction and we define the three arguments we require: $name, $price, and $desc. From there we simply create a new Product (note this was the name of our table in the schema file) and set the name, price, and description using the methods that were created by Propel. When we're done we need to call the save() method to save the changes to the database.

Now I can navigate to the route in my browser: 

[LOCALHOST PATH]/app_dev.php/db/insert/Product A/9.99/Our first Product

My browser pages simply spits back "Added: 1" which is great. This means that our first record with an id of 1 has been added. If I run it again I would get "Added: 2" since the id would be 2.



Fetching an Object
We can use our second route company_demo_view to get an object out of the database. In our DefaultController we need to create the appropriate controller:


public function viewAction($id)
{
     $product = ProductQuery::create()
                ->findPk($id);
        
     if(!$product)
     {
         throw $this->createNotFoundException('Not found');
     }
        
     return new Response($product->getPrice());
}

Again this should be pretty simple. The ProductQuery class was created by Propel when we created our models. Here we create a query that finds a row by the primary key (the id column) and then we simply output the price of the item in the response. We also include a check to make sure the product was found. If not we use the createNotFoundException method of the parent Controller class to return a 404 error that tells the user the item was not found.

So navigating to the route

[LOCALHOST PATH]/app_dev.php/db/view/1

spits out: "9.99". Awesome. Well not really, but it is a big step.


Updating an Object
Prices tend to change, so we need a way to update the price of an existing item. We've created the company_demo_change_price for just this purpose. Our route requires the id and price so we'll accept these as arguments: (note that the order of the arguments is not important)


public function updateAction($price, $id)
{
        $product = ProductQuery::create()
                ->findPk($id);
        
        if(!$product)
        {
            throw $this->createNotFoundException('Not found');
        }
        
        $product->setPrice($price);
        $product->save();
        
        return new Response('Updated');
}

This is really a combination of the methods we've used already. We find the product like we did in the viewAction and then set the price and save like we did in insertAction. Again we check for the existence of the product before making changes.

Let's try it out. Here I'm going to update the price from $9.99 to $5.99.

[LOCALHOST PATH]/app_dev.php/db/update/1/5.99

And it tells me "Updated". If I view my price now:

[LOCALHOST PATH]/app_dev.php/db/view/1

I get "5.99". Done. :)


Deleting an Object
So we've sold out of our item. We need to remove it from the database. All we need is the id of the item and we can remove it:


public function deleteAction($id)
{
     $product = ProductQuery::create()
                ->findPk($id);
        
     if(!$product)
     {
         throw $this->createNotFoundException('Not found');
     }
        
     $product->delete();
        
     return new Response('Deleted');
}

Everything here should look familiar, except the delete function which is self explanatory. Navigating to the route:

[LOCALHOST PATH]/app_dev.php/db/delete/1

I see: "Deleted". Checking my database I see the record no longer exists and when I navigate to the view route I get a 404 error.