Databases and theDoctrine ORM

Install Doctrine

 composer require symfony/orm-pack
 composer require --dev symfony/maker-bundle

Configure the Database

• stored as an environment variable called DATABASE_URL
• .env

Create an Entity Class

• make:entity

Migrations: Create the Database Tables/Schema

• make:migration (generate migration file)
• doctrine:migrations:migrate (migrate)

Migrations & Adding more Fields

①how to add a new field property

• make:entity
• edit the class file
  + make:entity --regenerate

②make some changes

• make:entity --regenerate --overwrite

Persist Objects to the Databse

• example

$entityManager = $this->getDoctrine()->getManager();
$product = new Product();
$product->setName('Keyboard');
$product->setPrice(1999);
$product->setDescription('Ergonomic and stylish!');
// tell Doctrine you want to (eventually) save the Product (no queries yet)
$entityManager->persist($product);
// actually executes the queries (i.e. the INSERT query)
$entityManager->flush();

• examine

php bin/console doctrine:query:sql 'SELECT * FROM product'

Fetch Objects from the Database

$repository = $this->getDoctrine()->getRepository(Product::class);
// look for a single Product by its primary key (usually "id")
$product = $repository->find($id);
// look for a single Product by name
$product = $repository->findOneBy(['name' => 'Keyboard']);
// or find by name and price
$product = $repository->findOneBy([
    'name' => 'Keyboard',
    'price' => 1999,
]);
// look for multiple Product objects matching the name, ordered by price
$products = $repository->findBy(
    ['name' => 'Keyboard'],
    ['price' => 'ASC']
);
// look for *all* Product objects
$products = $repository->findAll();

Automatically Fetch Objects(ParamConverter)

• SensioFrameworkExtraBundle
• composer require sensio/framework-extra-bundle
• example

use App\Entity\Product;
/**
 * @Route("/product/{id}", name="product_show")
 */
public function show(Product $product)
{
    // use the Product!
}

Update an Object

/**
 * @Route("/product/edit/{id}")
 */
public function update($id)
{
    $entityManager = $this->getDoctrine()->getManager();
    $product = $entityManager->getRepository(Product::class)->find($id);

    if (!$product) {
        throw $this->createNotFoundException(
            'No product found for id '.$id
        );
    }

    $product->setName('New product name!');
    $entityManager->flush();

    return $this->redirectToRoute('product_show', [
        'id' => $product->getId()
    ]);
}

Delete an Object

$entityManager->remove($product);
$entityManager->flush();

Query for Objects: The Repository

• a more complex query
• example

public function findAllGreaterThanPrice($price): array
{
     // automatically knows to select Products
     // the "p" is an alias you'll use in the rest of the query
     $qb = $this->createQueryBuilder('p')
         ->andWhere('p.price > :price')
         ->setParameter('price', $price)
         ->orderBy('p.price', 'ASC')
         ->getQuery();

     return $qb->execute();

     // to get just one result:
     // $product = $qb->setMaxResults(1)->getOneOrNullResult();
 }

Query with DQL or SQL

• Doctrine Query Language

// src/Repository/ProductRepository.php
// ...

public function findAllGreaterThanPrice($price): array
{
    $entityManager = $this->getEntityManager();

    $query = $entityManager->createQuery(
        'SELECT p
        FROM App\Entity\Product p
        WHERE p.price > :price
        ORDER BY p.price ASC'
    )->setParameter('price', $price);

    // returns an array of Product objects
    return $query->execute();
}

• SQL

public function findAllGreaterThanPrice($price): array
{
    $conn = $this->getEntityManager()->getConnection();

    $sql = '
        SELECT * FROM product p
        WHERE p.price > :price
        ORDER BY p.price ASC
        ';
    $stmt = $conn->prepare($sql);
    $stmt->execute(['price' => $price]);

    // returns an array of arrays (i.e. a raw data set)
    return $stmt->fetchAll();
}

Relationships and Associations

• manager database relationships
• ManyToOne, OneToMany, OneToOne and ManyToMany

Dummy Data Fixtures

• programmatically load testing data into your project