Symfony2 Doctrine ORM Composite Primary Keys

Question

I am developing an application in Symfony 2.3 with Doctrine 2.4 as ORM. The database engine I use is PostgreSQL. I'm having problems when mapping entities with composite primary keys in other tables. These keys are foreign keys in the related key.

The tables in my database have the following structure

CREATE TABLE public.establecimiento
(
  id_establecimiento integer NOT NULL,
  establecimiento character varying(100) NOT NULL,
  CONSTRAINT pk_establecimiento PRIMARY KEY (id_establecimiento )
)
WITH (
  OIDS=FALSE
);
CREATE TABLE public.establecimiento_sec
(
  id_establecimiento_sec integer NOT NULL,
  id_establecimiento integer NOT NULL,
  det_seccion character varying(40) NOT NULL,
  plano character varying(100),
  sector_ingreso character varying(254),
  sponsor_imagen_sec character varying(96000),
  CONSTRAINT pk_establecimientos_sec PRIMARY KEY (id_establecimiento_sec , id_establecimiento ),
  CONSTRAINT fk_establec_reference_establec FOREIGN KEY (id_establecimiento)
      REFERENCES public.establecimiento (id_establecimiento) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITH (
  OIDS=TRUE
);
CREATE TABLE public.establecimiento_sec_plano
(
  id_establecimiento_sec_plano integer NOT NULL,
  id_establecimiento_sec integer NOT NULL,
  id_establecimiento integer NOT NULL,
  det_plano character varying(512),
  cantidad integer NOT NULL,
  precio double precision,
  insert_charge double precision DEFAULT 0,
  descr character varying(254),
  CONSTRAINT pk_establecimiento_sec_plano PRIMARY KEY (id_establecimiento_sec_plano , id_establecimiento_sec , id_establecimiento ),
  CONSTRAINT fk_establecimiento_sec FOREIGN KEY (id_establecimiento, id_establecimiento_sec)
      REFERENCES public.establecimiento_sec (id_establecimiento, id_establecimiento_sec) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);

Defining the entity establecimientoSecPlano, $establecimientoSec variable containing the keys $establecimiento and $id_establecimiento_sec

//Entity/EstablecimientosSecPlano

/**
 * @ORM\Id
 * @ORM\ManyToOne(targetEntity="Ticketway\PruebaBundle\Entity\EstablecimientosSec")
 * @ORM\JoinColumns(
 *      @ORM\JoinColumn(name="id_establecimiento_sec", referencedColumnName="id_establecimiento_sec"),
 *      @ORM\JoinColumn(name="id_establecimiento", referencedColumnName="id_establecimiento")) 
 */
private $establecimientoSec;

//Entity/EstablecimientosSec

 /**
 * @ORM\Id
 * @ORM\ManyToOne(targetEntity="Ticketway\PruebaBundle\Entity\Establecimientos")
 * @ORM\JoinColumn(name="id_establecimiento", referencedColumnName="id_establecimiento") 
 */
private $establecimiento;

When executing the command doctrine: mapping: import I get the following error

[Doctrine\ORM\Mapping\MappingException] It is not possible to map entity 'EstablecimientoSec' with a composite primary key as part of the primary key of another entity 'EstablecimientoSecPlano#idEstablecimiento'.

I wonder if there is any way to define the entities in symfony and I can not do with doctrine.

Can I map the features in another way for the application to work correctly?

I hope my question is understood. thanks

Answer 1

You are running into this problem because your composite foreign key is another table's composite primary key. This is not a good development practice, which is why it is simply not supported by Doctrine, and I strongly doubt that it ever will be.

Solution 1 (preferred):

Add a single, auto-increment primary key to EstablecimientosSec. You can then link to that EstablecimientosSec.id instead.

Solution 2:

If changing the database structure is absolutely not possible, do not map the relationship. Instead, you can fetch the related EstablecimientosSec entities in a separate query using the composite primary key. It's not a prefect solution, but it works under these constraints. Tip: avoid querying the related objects as part of a loop.