Database schema belongs to migrations — field note 0158
Keep application schema changes explicit and reviewable. This deterministic field note expands the demo dataset.
Demostración ORM
Lemonade Blog
Un pequeño blog construido con Doctrine ORM y SQLite.
Doctrine ORM · SQLite · ManyToMany
Resultado ORM en vivo
Artículos con la etiqueta “Database”
271 artículos
Repository queries with Doctrine — field note 0171
Repositories keep ORM query intent close to the model. This deterministic field note expands the demo dataset.
Transactions around one connection — field note 0172
Do not assume separate database connections share atomic work. This deterministic field note expands the demo dataset.
Database indexes for real queries — field note 0178
Add indexes for the access patterns the application performs. This deterministic field note expands the demo dataset.
Deployment without surprise schema changes — field note 0189
Keep runtime startup separate from schema evolution. This deterministic field note expands the demo dataset.
Events after a successful write — field note 0190
Dispatch events only once state changes are durable. This deterministic field note expands the demo dataset.
ManyToMany authors in a blog — field note 0191
An article can credit several contributors without a primary-author rule. This deterministic field note expands the demo dataset.
Reducing database round trips — field note 0196
Fetch the graph needed for one page and avoid N+1 queries. This deterministic field note expands the demo dataset.
Deploying a Lemonade application — field note 0201
A focused deployment checklist keeps configuration aligned. This deterministic field note expands the demo dataset.
Doctrine ORM as an application integration — field note 0202
Doctrine stays in app code and resolves only when it is needed. This deterministic field note expands the demo dataset.
CLI commands for repeatable work — field note 0205
Small command classes keep operational work discoverable. This deterministic field note expands the demo dataset.
Database schema belongs to migrations — field note 0208
Keep application schema changes explicit and reviewable. This deterministic field note expands the demo dataset.
Cómo funciona este blog
La integración de Doctrine ORM detrás del ejemplo
El feed paginado del blog lee los ID de la página actual mediante toIterable(); un lote de grafo limitado carga después autores y etiquetas sin consultas N+1.
Configuración del provider
DoctrineServiceProvider.php
Registra Doctrine como un servicio normal de la aplicación mediante un provider de Lemonade.
<?php
declare(strict_types=1);
namespace App\Providers;
use App\Repository\OrmDemoArticleRepository;
use App\Repository\OrmDemoAuthorRepository;
use App\Repository\OrmDemoTagRepository;
use App\Services\DoctrineEntityManagerFactory;
use App\Services\OrmDemoService;
use Doctrine\ORM\EntityManagerInterface;
use Lemonade\Framework\Container\ContainerInterface;
use Lemonade\Framework\Core\ServiceProviderInterface;
final class DoctrineServiceProvider implements ServiceProviderInterface
{
public function register(ContainerInterface $container): void
{
$container->singleton(DoctrineEntityManagerFactory::class, DoctrineEntityManagerFactory::class);
$container->singleton(
EntityManagerInterface::class,
static fn (ContainerInterface $container): EntityManagerInterface => $container
->get(DoctrineEntityManagerFactory::class)
->create(),
);
$container->set(OrmDemoArticleRepository::class, OrmDemoArticleRepository::class);
$container->set(OrmDemoTagRepository::class, OrmDemoTagRepository::class);
$container->set(OrmDemoAuthorRepository::class, OrmDemoAuthorRepository::class);
$container->singleton(OrmDemoService::class, OrmDemoService::class);
}
}
Entidad de artículo
OrmDemoArticle.php
Relaciona varios autores y varias etiquetas mediante asociaciones ManyToMany.
<?php
declare(strict_types=1);
namespace App\Entity;
use DateTimeImmutable;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;
use InvalidArgumentException;
#[ORM\Entity]
#[ORM\Table(name: 'orm_demo_articles')]
final class OrmDemoArticle
{
#[ORM\Id]
#[ORM\GeneratedValue]
#[ORM\Column(type: 'integer')]
private ?int $id = null;
#[ORM\Column(type: 'string', length: 255)]
private string $title;
#[ORM\Column(type: 'text')]
private string $perex;
/** @var Collection<int, OrmDemoAuthor> */
#[ORM\ManyToMany(targetEntity: OrmDemoAuthor::class, inversedBy: 'articles', cascade: ['persist'])]
#[ORM\JoinTable(name: 'orm_demo_article_authors')]
private Collection $authors;
#[ORM\Column(type: 'datetime_immutable')]
private DateTimeImmutable $createdAt;
/** @var Collection<int, OrmDemoTag> */
#[ORM\ManyToMany(targetEntity: OrmDemoTag::class, inversedBy: 'articles', cascade: ['persist'])]
#[ORM\JoinTable(name: 'orm_demo_article_tags')]
private Collection $tags;
/**
* @param non-empty-list<OrmDemoAuthor> $authors
*/
public function __construct(string $title, string $perex, array $authors, DateTimeImmutable $createdAt)
{
if ($authors === []) {
throw new InvalidArgumentException('An ORM demo article must have at least one author.');
}
$this->title = $title;
$this->perex = $perex;
$this->createdAt = $createdAt;
$this->authors = new ArrayCollection();
$this->tags = new ArrayCollection();
foreach ($authors as $author) {
$this->addAuthor($author);
}
}
public function id(): ?int
{
return $this->id;
}
public function title(): string
{
return $this->title;
}
public function perex(): string
{
return $this->perex;
}
/** @return list<OrmDemoAuthor> */
public function authors(): array
{
return $this->authors->toArray();
}
public function addAuthor(OrmDemoAuthor $author): void
{
if (!$this->authors->contains($author)) {
$this->authors->add($author);
}
}
public function createdAt(): DateTimeImmutable
{
return $this->createdAt;
}
/** @return Collection<int, OrmDemoTag> */
public function tags(): Collection
{
return $this->tags;
}
public function addTag(OrmDemoTag $tag): void
{
if (!$this->tags->contains($tag)) {
$this->tags->add($tag);
}
}
}
Entidad de autor
OrmDemoAuthor.php
Los autores son entidades independientes identificadas por un nickname único.
<?php
declare(strict_types=1);
namespace App\Entity;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;
#[ORM\Entity]
#[ORM\Table(name: 'orm_demo_authors')]
final class OrmDemoAuthor
{
#[ORM\Id]
#[ORM\GeneratedValue]
#[ORM\Column(type: 'integer')]
private ?int $id = null;
#[ORM\Column(type: 'string', length: 80, unique: true)]
private string $nickname;
/** @var Collection<int, OrmDemoArticle> */
#[ORM\ManyToMany(targetEntity: OrmDemoArticle::class, mappedBy: 'authors')]
private Collection $articles;
public function __construct(string $nickname)
{
$this->nickname = $nickname;
$this->articles = new ArrayCollection();
}
public function id(): ?int
{
return $this->id;
}
public function nickname(): string
{
return $this->nickname;
}
}
Entidad de etiqueta
OrmDemoTag.php
Las etiquetas son entidades compartidas conectadas a los artículos mediante una tabla join.
<?php
declare(strict_types=1);
namespace App\Entity;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;
#[ORM\Entity]
#[ORM\Table(name: 'orm_demo_tags')]
final class OrmDemoTag
{
#[ORM\Id]
#[ORM\GeneratedValue]
#[ORM\Column(type: 'integer')]
private ?int $id = null;
#[ORM\Column(type: 'string', length: 80)]
private string $name;
#[ORM\Column(type: 'string', length: 80, unique: true)]
private string $slug;
/** @var Collection<int, OrmDemoArticle> */
#[ORM\ManyToMany(targetEntity: OrmDemoArticle::class, mappedBy: 'tags')]
private Collection $articles;
public function __construct(string $name, string $slug)
{
$this->name = $name;
$this->slug = $slug;
$this->articles = new ArrayCollection();
}
public function id(): ?int
{
return $this->id;
}
public function name(): string
{
return $this->name;
}
public function slug(): string
{
return $this->slug;
}
}
Repository y paginación
OrmDemoArticleRepository.php
Combina filtros opcionales con paginación a nivel de SQL y evita consultas N+1.
<?php
declare(strict_types=1);
namespace App\Repository;
use App\Entity\OrmDemoArticle;
use App\Services\DoctrineEntityManagerFactory;
use Doctrine\ORM\EntityManagerInterface;
final class OrmDemoArticleRepository
{
private const GRAPH_BATCH_SIZE = 25;
public function __construct(private readonly DoctrineEntityManagerFactory $entityManagerFactory)
{
}
public function paginate(int $page, int $perPage, ?string $tag = null, ?string $author = null): OrmDemoArticlePage
{
$perPage = max(1, $perPage);
$count = $this->entityManager()
->createQueryBuilder()
->select('COUNT(DISTINCT article.id)')
->from(OrmDemoArticle::class, 'article');
if ($tag !== null) {
$count
->innerJoin('article.tags', 'tag')
->andWhere('tag.slug = :tag')
->setParameter('tag', $tag);
}
if ($author !== null) {
$count
->innerJoin('article.authors', 'author')
->andWhere('author.nickname = :author')
->setParameter('author', $author);
}
$total = (int) $count->getQuery()->getSingleScalarResult();
$page = min(max(1, $page), max(1, (int) ceil($total / $perPage)));
$idsQuery = $this->entityManager()
->createQueryBuilder()
->select('DISTINCT article.id AS id')
->from(OrmDemoArticle::class, 'article');
if ($tag !== null) {
$idsQuery
->innerJoin('article.tags', 'tag')
->andWhere('tag.slug = :tag')
->setParameter('tag', $tag);
}
if ($author !== null) {
$idsQuery
->innerJoin('article.authors', 'author')
->andWhere('author.nickname = :author')
->setParameter('author', $author);
}
$idsQuery
->orderBy('article.createdAt', 'DESC')
->addOrderBy('article.id', 'DESC')
->setFirstResult(($page - 1) * $perPage)
->setMaxResults($perPage);
$articles = (function () use ($idsQuery): iterable {
$entityManager = $this->entityManager();
$ids = [];
foreach ($idsQuery->getQuery()->toIterable() as $row) {
$ids[] = (int) $row['id'];
if (count($ids) === self::GRAPH_BATCH_SIZE) {
yield from $this->fetchGraphBatch($entityManager, $ids);
$ids = [];
$entityManager->clear();
}
}
if ($ids !== []) {
yield from $this->fetchGraphBatch($entityManager, $ids);
$entityManager->clear();
}
})();
return new OrmDemoArticlePage($articles, $total, $page);
}
/** @param list<int> $ids @return iterable<OrmDemoArticle> */
private function fetchGraphBatch(EntityManagerInterface $entityManager, array $ids): iterable
{
/** @var list<OrmDemoArticle> $fetched */
$fetched = $entityManager->createQueryBuilder()
->select('article, author, tag')
->from(OrmDemoArticle::class, 'article')
->leftJoin('article.authors', 'author')
->leftJoin('article.tags', 'tag')
->where('article.id IN (:ids)')
->setParameter('ids', $ids)
->getQuery()
->getResult();
$byId = [];
foreach ($fetched as $article) {
$byId[$article->id() ?? 0] = $article;
}
foreach ($ids as $id) {
if (isset($byId[$id])) {
yield $byId[$id];
}
}
}
public function hasArticles(): bool
{
return $this->entityManager()->getRepository(OrmDemoArticle::class)->count([]) > 0;
}
public function add(OrmDemoArticle $article): void
{
$this->entityManager()->persist($article);
$this->entityManager()->flush();
}
/** @param iterable<OrmDemoArticle> $articles */
public function addAll(iterable $articles, int $batchSize): void
{
$entityManager = $this->entityManager();
$processed = 0;
$batchSize = max(1, $batchSize);
foreach ($articles as $article) {
$entityManager->persist($article);
++$processed;
if ($processed % $batchSize === 0) {
$entityManager->flush();
}
}
$entityManager->flush();
}
private function entityManager(): EntityManagerInterface
{
return $this->entityManagerFactory->create();
}
}
Cómo funciona el filtrado
Los artículos pueden tener varios autores y varias etiquetas. Ambas asociaciones se mapean en Doctrine ORM como ManyToMany. Al filtrar por autor, Doctrine conecta los artículos mediante la tabla de unión orm_demo_article_authors y los selecciona por author.nickname. Al filtrar por etiqueta, utiliza la tabla de unión orm_demo_article_tags con una condición sobre tag.slug. Los dos filtros se pueden combinar.