When I started out using Symfony I was frustrated at the lack of instruction on how to achieve an objective, unfortunately for the most part this is still true, I love Symfony but I do feel like it's now lagging behind in instruction and event features to other frameworks like Laravel.
That being said, one of the joys of using Symfony is the doctrone ORM seamless integration. It makes constructing complex algorithms fun, well if that's your cup of tea, that is.
There are some questions the present themselves when thinking about database querying within a Symfony repository:
1. Can we write short and efficient SQL queries?
2. Can we reuse query methods and return a query or data?
3. Can we run coding standard checks on our SQL queries?
Before we answer these questions, let's have a look at the problem we are solving.
If we submit a form in a controller and want to use that information to return some database records, we have to do something like this:
#[Route('/some/route', name: 'some_route')]
public function index(Request $request): Response
{
$emailForm->handleRequest($request);
if ($emailForm->isSubmitted() && $emailForm->isValid()) {
$emailAddress = (string) $emailForm->get('email')->getData();
$email = $this->emailRepository->findByEmailAddress($emailAddress);
// return something...
}
}
From looking at this we know that `$emailAddress` should be a string, but we have to cast it as a string because `getData()` returns mixed. In my book this is a bit unpredictable and bad practice.
Image if we could get an object from the form and pass it in to the `findOneByEmailAddress()` and then access a property like `$email->getAddress()` and this property would always return a string. hmm, seems like a better approach.
To demonstrate this, I'll be using an event filter form as an example, here is the filter form type:
public function buildForm(FormBuilderInterface $builder, array $options): void
{
$builder
->add('keyword', TextType::class, [
'label' => $this->translator->trans('event-filter-title-placeholder'),
'attr' => [
],
'required' => false,
'row_attr' => [
'class' => 'form-floating',
],
])
->add('period', CustomEnumType::class, [
'label' => false,
'class' => EventFilterDateRangeEnum::class,
'row_attr' => [
'class' => 'form-floating',
],
'expanded' => true,
'multiple' => false,
])
->add('category', EntityType::class, [
'required' => false,
'class' => Category::class,
'choice_label' => 'title',
'choice_translation_domain' => true,
'autocomplete' => true,
'row_attr' => [
'class' => 'form-floating',
],
])
->add('country', EntityType::class, [
'class' => Country::class,
'choice_label' => 'name',
'row_attr' => [
'class' => 'form-floating',
],
]);
}
A good rule of thumb, whenever possible use objects over arrays. This is no different, so the `data_class` type is a DTO (DataTransferObject) called `EventFilterDto` in here are all of the form fields defined as properties, like so:
<?php
declare(strict_types=1);
namespace App\DataTransferObject;
use App\Entity\City;
use App\Entity\Country;
use App\Entity\Event\Category;
use App\Enum\EventFilterDateRangeEnum;
final class EventFilterDto
{
private null|string $keyword = null;
private null|EventFilterDateRangeEnum $period = EventFilterDateRangeEnum::TODAY;
private null|Category $category = null;
private null|Country $country = null;
public function getKeyword(): ?string
{
return $this->keyword;
}
public function setKeyword(?string $keyword): void
{
$this->keyword = $keyword;
}
public function getPeriod(): null|EventFilterDateRangeEnum
{
return $this->period;
}
public function setPeriod(null|EventFilterDateRangeEnum $period): void
{
$this->period = $period;
}
public function getCategory(): ?Category
{
return $this->category;
}
public function setCategory(?Category $category): void
{
$this->category = $category;
}
public function getCountry(): ?Country
{
return $this->country;
}
public function setCountry(?Country $country): void
{
$this->country = $country;
}
}
Here we are we are defining our form with have a keyword, period (date range), category and country.
So now when the form is submitted in the controller we get a solid `EventFilterDto` object, woop!
#[Route(path: '/', name: 'events')]
public function index(Request $request): Response
{
$eventFilterDto = new EventFilterDto();
$eventFilter = $this->createForm(EventFilterType::class, $eventFilterDto);
$events = $this->eventRepository->findByFilter(eventFilterDto: $eventFilterDto, isQuery: true);
$eventFilter->handleRequest($request);
if ($eventFilter->isSubmitted() && $eventFilter->isValid()) {
$events = $this->eventRepository->findByFilter(eventFilterDto: $eventFilterDto, isQuery: true);
return $this->render('events/index.html.twig', [
'eventFilter' => $eventFilter,
'events' => $events
]);
}
return $this->render('events/index.html.twig', [
'eventFilter' => $eventFilter,
'events' => $events
]);
}
Pretty neat and readable, but also nothing too ground breaking. as you can see we are passing the `findByFilter` method the `eventFilterDto` but also a bool `isQuery`
But now we need to explore the `findByFilter` further method inside the `EventRepository`.
/**
* @return Query|array<int, Event>
*/
public function findByFilter(EventFilterDto $eventFilterDto, bool $isQuery = false): Query|array
{
$qb = $this->createQueryBuilder('event');
if ($eventFilterDto->getPeriod() instanceof EventFilterDateRangeEnum) {
$this->findByPeriod(period: $eventFilterDto->getPeriod(), qb: $qb);
}
if ($eventFilterDto->getCategory() instanceof Category) {
$this->findByCategory(category: $eventFilterDto->getCategory(), qb: $qb);
}
if (! empty($eventFilterDto->getKeyword())) {
$this->findByTitle(keyword: $eventFilterDto->getKeyword(), qb: $qb);
}
$qb->andWhere(
$qb->expr()->eq('event.isPrivate', ':false')
)->setParameter('false', false);
$qb->orderBy('event.startAt', Order::Ascending->value);
if ($isQuery) {
return $qb->getQuery();
}
return $qb->getQuery()->getResult();
}
I hate check for null values, because it tells us what we don't want is there, but it doesn't tell use if what we want is there. Therefore, I always check if something is an instance of the object I need, if not, i'm not interested.
This filter method is 24 lines long, and yet is checking the period and/or category selected, keyword entered and if the event is private. If that's not compact, I don't know what is.
But how are we able to reuse these methods `findByTitle`, `findByCategory` and `findByPeriod`.
let's start with `findByTitle`
/**
* @return Query|array<int, Event>
*/
public function findByTitle(string $keyword, QueryBuilder $qb = null, bool $isQuery = false): Query|array
{
if (! $qb instanceof QueryBuilder) {
$qb = $this->createQueryBuilder('event');
}
$result = $qb;
$qb->andWhere(
$qb->expr()->like($qb->expr()->lower('event.title'), ':title')
)->setParameter('title', '%' . strtolower($keyword) . '%');
$qb->leftJoin('event.eventGroup', 'event_group');
$qb->orWhere(
$qb->expr()->like($qb->expr()->lower('event_group.name'), ':name')
)->setParameter('name', '%' . strtolower($keyword) . '%');
if ($isQuery) {
return $result->getQuery();
}
return $result->getQuery()->getResult();
}
First of all we check if the `$qb` parameter is an instance of QueryBuilder, if there isn't an instance, then it a stand alone usage and therefore we need to create an QueryBuilder object.
Next, we have to store the `$qb` as `$result` This is there to hold a reference to the original state of the query builder before any changes are made to it, ensuring that the method can return either the modified query or its result based on the caller's preference.
With this strategy you can create very complex queries and reuse them alone or within a bigger query.
Now for the kicker, because we are using PHP and not SQL, we can run our coding standard checks like Phpstan, Easy Coding Standard and Rector on our SQL queries.
in conclusion, we have create short, efficient, reusable, chain-able, decoupled SQL queries that can be fixed and/or updated by our coding standards. How cool is that!