Generate auto increment ID with composite primary key

Question

I would like to give an entity (Invoice, Order, Reservation, etc.) a unique sequence number, but only unique within that year. So the first invoice of every year (or another field, such as Customer) starts has id 1. That means that there can be a composite primary key (year, id) or one primary key (i.e. invoice_id) and two other columns that are unique together.

My question: What's the best way to give an object a unique combination of an auto-generated ID and another value using Doctrine2 and Symfony2?

Doctrine limitations on composite keys

Doctrine can't assign an auto generated ID to an entity with a composite primary key (http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/tutorials/composite-primary-keys.html):

Every entity with a composite key cannot use an id generator other than “ASSIGNED”. That means the ID fields have to have their values set before you call EntityManager#persist($entity).

Setting the sequence number manually

So I have to assign a ID manually. In order to do that, I've tried to look for the highest ID in a certain year and give the new entity that ID + 1. I doubt that's the best way and even if it is, I haven't found the best (DRY) way to do it. Since I think this is a generic question and I want to prevent the XY-problem, I've started this question.

Vanilla options: only with MyISAM

I found the 'vanilla' MySQL/MyISAM solution based on this answer:

CREATE TABLE IF NOT EXISTS `invoice` (
  `year` int(1) NOT NULL,
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`year`,`id`)
) ENGINE=MyISAM;

That won't work because of the Doctrine2 limitations, so I'm looking for a Doctrine ORM equivalent of this vanilla MySQL solution.

Other solutions

There is a solution for InnoDB as well: Defining Composite Key with Auto Increment in MySQL

Answer 1

The ORM equivalent of the pre-insert trigger solution you're linking to would be a lifecycle callback. You can read more about them here.

A naive solution would look something like this.

services.yml

services:
    invoice.listener:
        class: MyCompany\CompanyBundle\EventListener\InvoiceListener
        tags :
            - { name: doctrine.event_subscriber, connection: default }

InvoiceListener.php

<?php

namespace MyCompany\CompanyBundle\EventListener;

use Symfony\Component\EventDispatcher\EventDispatcherInterface;
use Doctrine\Common\EventSubscriber;
use Doctrine\ORM\Event\OnFlushEventArgs;
use Doctrine\ORM\Event\PostFlushEventArgs;

use MyCompany\CompanyBundle\Entity\Invoice;

class InvoiceListener implements EventSubscriber {

    protected $invoices;

    public function getSubscribedEvents() {
        return [
            'onFlush',
            'postFlush'
        ];
    }

    public function onFlush(OnFlushEventArgs $event) {
        $this->invoices = [];
        /* @var $em \Doctrine\ORM\EntityManager */
        $em = $event->getEntityManager();
        /* @var $uow \Doctrine\ORM\UnitOfWork */
        $uow = $em->getUnitOfWork();

        foreach ($uow->getScheduledEntityInsertions() as $entity) {
            if ($entity instanceof Invoice) {
                $this->invoices[] = $entity;
            }
        }
    }

    public function postFlush(PostFlushEventArgs $event) {
        if (!empty($this->invoices)) {

            /* @var $em \Doctrine\ORM\EntityManager */
            $em = $event->getEntityManager();

            foreach ($this->invoices as $invoice) {
                // Get all invoices already in the database for the year in question
                $invoicesToDate = $em
                    ->getRepository('MyCompanyCompanyBundle:Invoice')
                    ->findBy(array(
                        'year' => $invoice->getYear()
                        // You could include e.g. clientID here if you wanted
                        // to generate a different sequence per client
                    );
                // Add your sequence number
                $invoice->setSequenceNum(count($invoicesToDate) + 1);

                /* @var $invoice \MyCompany\CompanyBundle\Entity\Invoice */
                $em->persist($invoice);
            }

            $em->flush();
        }
    }
}