Fetching Objects from the Database using Doctrine and Symfony

CRUD stands for Create, Read, Update and Delete, the basic operations performed on the database. In this article, I will show you how to perform C(R)UD with Doctrine.

In Doctrine and Symfony, there are multiple ways of accomplishing the same task. This gives you flexibility to pick the best approach suitable for each situation. Therefore we shall explore these different methods for each method of the fetching and object(i.e Read in CRUD).

The act of fetching records/object from the database is very easier using doctrine. Most of these are performed on Doctrine_Table objects, which is obtained by $this->getDoctrine()->getRepository() and passing the model class name to it. When you query for a particular type of object, you always use what's known as its repository.

$repository = $this->getDoctrine()
    ->getRepository('AppBundle:Product');

The AppBundle:Product string is a shortcut you can use anywhere in Doctrine instead of the full class name of the entity (i.e. AppBundle\Entity\Product). As long as your entity lives under the Entity namespace of your bundle, this will work.

Once you have your repository, you have access to all sorts of helpful methods:
find()
The find() method fetches a record by the Primary Key value.

// query by the primary key (usually "id")
$product = $repository->find($id);

findOneBy*()
This is a magic method. You can simply append the column name, and it will search the table by that column.

// dynamic method names to find based on a column value
$product = $repository->findOneByName('foo');

You can also take advantage of the useful findOneBy method to easily fetch objects based on multiple conditions:

// query for one product matching by name and price
$product = $repository->findOneBy(
    array('name' => 'foo', 'price' => 19.99)
);

It fetches only one record.

findBy*()
Another magic method, which works similarly, but fetches multiple rows. It returns a Doctrine_Collection object.

// find a group of products based on an arbitrary column value
$products = $repository->findByPrice(19.99);

You can also take advantage of the useful findBy method to easily fetch objects based on multiple conditions:

// query for all products matching the name, ordered by price
$products = $repository->findBy(
    array('name' => 'foo'),
    array('price' => 'ASC')
);

DQL
It stands for Doctrine Query Language. It is actually a major feature. Most advanced queries are performed using DQL. You can use Doctrine's native SQL-like language called DQL to make a query for this:

$em = $this->getDoctrine()->getManager();
$query = $em->createQuery(
    'SELECT p
    FROM AppBundle:Product p
    WHERE p.price > :price
    ORDER BY p.price ASC'
)->setParameter('price', '19.99');

$products = $query->getResult();
// to get just one result:
// $product = $query->setMaxResults(1)->getOneOrNullResult();

If you're comfortable with SQL, then DQL should feel very natural. The biggest difference is that you need to think in terms of "objects" instead of rows in a database. For this reason, you select from the AppBundle:Product object (an optional shortcut for AppBundle\Entity\Product) and then alias it as p.
The getResult() method returns an array of results. To get only one result, you can use getOneOrNullResult():

$product = $query->setMaxResults(1)->getOneOrNullResult();

Doctrine's Query Builder
Instead of writing a DQL string, you can use a helpful object called the QueryBuilder to build that string for you. This is useful when the actual query depends on dynamic conditions, as your code soon becomes hard to read with DQL as you start to concatenate strings:

$repository = $this->getDoctrine()
    ->getRepository('AppBundle:Product');

// createQueryBuilder automatically selects FROM AppBundle:Product
// and aliases it to "p"
$query = $repository->createQueryBuilder('p')
    ->where('p.price > :price')
    ->setParameter('price', '19.99')
    ->orderBy('p.price', 'ASC')
    ->getQuery();

$products = $query->getResult();
// to get just one result:
// $product = $query->setMaxResults(1)->getOneOrNullResult();

The QueryBuilder object contains every method necessary to build your query. By calling the getQuery() method, the query builder returns a normal Query object, which can be used to get the result of the query.
Source: http://symfony.com/doc/current/book/doctrine.html#querying-for-objects