Blog entry

Annice 2020-12-06 PHP, Symfony, MySQL, Doctrine

When I started re-building Annice.se based on PHP with Symfony, I was hoping that PHP would have some equivalent to Lambda expressions with LINQ (Language-Integrated Query). Lambda expressions with LINQ was namely something I used quite frequently within C# ASP.NET Core concerning filtering of lists and arrays, which in turn was oftently based on database queries.

A kind of equivalent to the above within PHP is Doctrine. Moreover, something I have used a bit so far in conjunction with the re-building of Annice.se is Doctrine ORM (Object-Relational Mapper) with Symfony.

As the ORM acronyme implies, the purpose with this query language is to ease the PHP objects' (entities') interaction with one or many relational databases, in which the database tables reflect the entities used in the PHP code, as well as where the table attributes (columns) are mapped to the different properties of the PHP entities.

Furthermore, what appealed to me the most with Doctrine ORM was that it also allows you to use raw SQL code, i.e. like the actual queries used in the end when they are executed to the database. In cases where you use sub-queries and/or different joins to reach different data, I have to admit that I prefer using pure SQL code.

Given that you have PHP Symfony installed with a configured database connection, further prerequisits to run Doctrine ORM within Symfony are to have the package manager Composer installed. In turn, that enables you to easily install support for the Doctrine package. Within C# ASP.NET Core you talk about NuGet packages, why I am also using terms like "packages" in this case. But it should be mentioned that bundles is the more accurate term to use for this within the Symfony framework. However, a more detailed guide for this can be found on Symfony's documention page: Databases and the Doctrine ORM.

Anyhow, the purpose with this post was really to list some Doctrine ORM queries I have collected, which I find useful to go back to as starting points regarding different database quiries. And to finally enable the function of Doctrine, you need to call/import an EntityManagerInterface where you want to use Doctrine ORM, e.g. in a controller.

The following code illustrates a holistic context of where the EntityManagerInterface has been autowired via a controller constructor, which is useful when Doctrine quiries are used in many action methods in the same controller class. Moreover, the constructor injected Entity Manager service can then be applied on a Doctrine query to get posts from a database table in action methods like the example below. Also, the following code snippets are based on Symfony 5.1.8 with Doctrine 2, which in turn requires at least PHP 7.1:

namespace App\Controller;

use App\Entity\Entry;
use Symfony\Component\Routing\Annotation\Route;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Method;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Doctrine\ORM\EntityManagerInterface;

class EntryController extends AbstractController
{
    private $entityManager;

    /**
     * Inject dependencies.
     */
    public function __construct(EntityManagerInterface $entityManager)
    {
        $this->entityManager = $entityManager;
    }

    /**
     * @Route("/entry/entry_list", name="entry_list")
     * @Method({"GET"})
     */
    public function entryListAction()
    {
        // Call the entity manager service:
        $em = $this->entityManager;
        // Get all entries from DB via Doctrine entity mapping and return the result in descending order by entry date:
        $entries = $em->getRepository(Entry::class)->findBy([], ['date' => 'DESC']);

        // Finally, pass the entry list to be reached via an "entries" variable from a twig template/view:
        return $this->render('entries/entry_list.html.twig', [
            "entries" => $entries
        ]);
    }
}

If you would like to formulate the same query without having to autowire the EntityManagerInterface via a constructor, i.e. only use it directly from some single method. Then you can exclude the dependency injection into the constructor and instead call the service as below:

 public function entryListAction()
 {
     // Call the entity manager service:
     $em = $this->getDoctrine()->getManager();
     // Get all entries from DB via Doctrine entity mapping and return the result in descending order by entry date:
     $entries = $em->getRepository(Entry::class)->findBy([], ['date' => 'DESC']);

     // ...

If you would want to get a specific Entry object based on a specific condition in Doctrine ORM, this could look like the code snippet below (given that the Entity Manager service has been imported and instantiated):

$entry = $em->getRepository(Entry::class)->createQueryBuilder("alias")
    ->select("alias")
    ->where("alias.email = :email")
    ->setParameter("email", "user@email.com")
    ->getQuery()
    ->getOneOrNullResult();

By choosing to return the above result with the method getOneOrNullResult(), it will return null if there are not hits, meaning that this could be handled directly in a PHP statement without risking the application to crash. Otherwise, a corresponding method for this would be to use the getSingleResult() method. However, this would throw an exception in case of no hits, or in case when more than one hit occurs.

In addition, if you would like to fetch objects based on several conditions and return the result into an array in descending order by date, it can look like this:

$entries = $em->getRepository(Entry::class)->createQueryBuilder("alias")
    ->select("alias")
    ->where("alias.date = :date")
    ->andWhere("alias.categoryid = :categoryid")
    ->setParameters(["date" => "2020-12-06", "categoryid" => 1])
    ->orderBy("alias.date", "DESC")
    ->getQuery()
    ->getResult();

Furthermore, you can use the following code snippet if you would like to get the same result as above, but formulated in raw MySQL code with Doctrine:

$entries = $em->createQuery(
    "SELECT alias FROM App\Entity\Entry alias
     WHERE alias.date = :date AND alias.categoryid = :categoryid
     ORDER BY alias.date DESC"
)->setParameters(["date" => "2020-12-06", "categoryid" => 1])
 ->getResult();

Within relational databases where many-to-many relations occur between tables, you sometimes need to fetch values via a linking table. In turn, the linking table shall contain at least reference/foreign keys pointing to all the primary keys for each table where the many-to-many relation takes place.

If a business rule says that an entry can have many categories while every category can be linked to many entries, we have a many-to-many relation between the entry table and the category table. Moreover, the linking table connecting these two tables must consist of at least one reference key pointing to the primary key in the entry table, and one reference key pointing to the primary key in the category table.

Let us say that you would like to use raw MySQL with Doctrine to fetch all categories related to an entry, which in turn shall be returned as a category list sorted in alphabetic order by category name. Then the query for this can look like the following code snippet:

$entryCategories = $em->createQuery(
    "SELECT c FROM App\Entity\Category c
     WHERE c.id IN (
         SELECT IDENTITY(ec.categoryId) FROM App\Entity\EntryCategories ec
         WHERE ec.entryId = :entryid
     )
     ORDER BY c.name ASC"
)->setParameter("entryid", $entryIdInput)
 ->getResult();

In order for the above query to work it requires - except that the EntityManagerInterface has been imported and instantiated - also that the concerning entities "Category" and "EntryCategories" are available in the entity layer of the PHP project. Also, these entities need to be imported in the PHP class in which the query is used. Furthermore, the query above presupposes that the entity "Category" has properties named "id" and "name", as well as the linking entity "EntryCategories" has properties named "categoryId" and "entryId".

« Go back

Doctrine ORM with PHP Symfony