Symfony: Loading fixtures of objects with multiple column primary key which are also foreign keys

im writing fixtures for my project in symfony, and i have preblems when trying to add fixtures to objects with a multiple column primary key, which is also foreign key to other table.

Here's the schema in MySql Workbench:

I generated the sql code with workbench, and then generated the schema.yml file with the symfony task php symfony doctrine:build-schema.

this is the sql generated for these tables:

-- -----------------------------------------------------
-- Table `mydb`.`MetodoEnvio`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`MetodoEnvio` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`MetodoEnvio` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `nombre` VARCHAR(45) NOT NULL ,
  `precio` DECIMAL(10,2) NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

-- -----------------------------------------------------
-- Table `mydb`.`ZonaEnvio`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`ZonaEnvio` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`ZonaEnvio` (
  `id` INT UNSIGNED NOT NULL ,
  `numero` INT(3) UNSIGNED NOT NULL ,
  `etiqueta` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`, `numero`) ,
  INDEX `fk_ZonaEnvio_MetodoEnvio` (`id` ASC) ,
  INDEX `numeroZona_INDEX` (`numero` ASC) ,
  CONSTRAINT `fk_ZonaEnvio_MetodoEnvio`
    FOREIGN KEY (`id` )
    REFERENCES `mydb`.`MetodoEnvio` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

-- -----------------------------------------------------
-- Table `mydb`.`PrecioEnvio`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`PrecioEnvio` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`PrecioEnvio` (
  `idMetodo` INT UNSIGNED NOT NULL ,
  `zona` INT(3) UNSIGNED NOT NULL ,
  `inicioRango` DECIMAL(10,2) NOT NULL COMMENT 'Marca el inicio del rango de peso para el cual este precio aplica.' ,
  `finRango` DECIMAL(10,2) NULL COMMENT 'Marca el fin del rango de peso para el cual este precio aplica.' ,
  `precio` DECIMAL(10,2) NOT NULL ,
  PRIMARY KEY (`idMetodo`, `inicioRango`, `finRango`, `zona`) ,
  INDEX `fk_PrecioEnvio_ZonaEnvio` (`idMetodo` ASC, `zona` ASC) ,
  CONSTRAINT `fk_PrecioEnvio_ZonaEnvio`
    FOREIGN KEY (`idMetodo` , `zona` )
    REFERENCES `mydb`.`ZonaEnvio` (`id` , `numero` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

-- -----------------------------------------------------
-- Table `mydb`.`IntervaloMetodoEnvio`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`IntervaloMetodoEnvio` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`IntervaloMetodoEnvio` (
  `idMetodo` INT UNSIGNED NOT NULL ,
  `zona` INT(3) UNSIGNED NOT NULL ,
  `intervalo` DECIMAL(10,2) NOT NULL ,
  `precio` DECIMAL(10,2) NOT NULL ,
  PRIMARY KEY (`idMetodo`, `zona`) ,
  INDEX `fk_IntervaloMetodoEnvio_ZonaEnvio` (`idMetodo` ASC, `zona` ASC) ,
  CONSTRAINT `fk_IntervaloMetodoEnvio_ZonaEnvio`
    FOREIGN KEY (`idMetodo` , `zona` )
    REFERENCES `mydb`.`ZonaEnvio` (`id` , `numero` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

this is the schema generated for these tables:

MetodoEnvio:
  connection: doctrine
  tableName: MetodoEnvio
  columns:
    id:
      type: integer(4)
      fixed: false
      unsigned: true
      primary: true
      autoincrement: true
    nombre:
      type: string(45)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
    precio:
      type: 'decimal(10, 2)'
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
  relations:
    TiendaOrden:
      local: id
      foreign: metodoenvio
      type: many
    ZonaEnvio:
      local: id
      foreign: id
      type: many
ZonaEnvio:
  connection: doctrine
  tableName: ZonaEnvio
  columns:
    id:
      type: integer(4)
      fixed: false
      unsigned: true
      primary: true
      autoincrement: false
    numero:
      type: integer(4)
      fixed: false
      unsigned: true
      primary: true
      autoincrement: false
    etiqueta:
      type: string(255)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
  relations:
    MetodoEnvio:
      local: id
      foreign: id
      type: one
    IntervaloMetodoEnvio:
      local: id
      foreign: idmetodo
      type: many
    IntervaloMetodoEnvio_2:
      class: IntervaloMetodoEnvio
      local: numero
      foreign: zona
      type: many
    PrecioEnvio:
      local: id
      foreign: idmetodo
      type: many
    PrecioEnvio_2:
      class: PrecioEnvio
      local: numero
      foreign: zona
      type: many
PrecioEnvio:
  connection: doctrine
  tableName: PrecioEnvio
  columns:
    idmetodo:
      type: integer(4)
      fixed: false
      unsigned: true
      primary: true
      autoincrement: false
    zona:
      type: integer(4)
      fixed: false
      unsigned: true
      primary: true
      autoincrement: false
    iniciorango:
      type: 'decimal(10, 2)'
      fixed: false
      unsigned: false
      primary: true
      autoincrement: false
    finrango:
      type: 'decimal(10, 2)'
      fixed: false
      unsigned: false
      primary: true
      autoincrement: false
    precio:
      type: 'decimal(10, 2)'
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
  relations:
    ZonaEnvio:
      local: idmetodo
      foreign: id
      type: one
    ZonaEnvio_2:
      class: ZonaEnvio
      local: zona
      foreign: numero
      type: one
IntervaloMetodoEnvio:
  connection: doctrine
  tableName: IntervaloMetodoEnvio
  columns:
    idmetodo:
      type: integer(4)
      fixed: false
      unsigned: true
      primary: true
      autoincrement: false
    zona:
      type: integer(4)
      fixed: false
      unsigned: true
      primary: true
      autoincrement: false
    intervalo:
      type: 'decimal(10, 2)'
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
    precio:
      type: 'decimal(10, 2)'
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
  relations:
    ZonaEnvio:
      local: idmetodo
      foreign: id
      type: one
    ZonaEnvio_2:
      class: ZonaEnvio
      local: zona
      foreign: numero
      type: one

Then, i wrote the following fixtures:

MetodoEnvio:
  personal:
    nombre: Retiro Personal
    precio: 0.0

  mrw:
    nombre: MRW
    precio: 5.0

  dhl:
    nombre: DHL
    precio: -1.0
ZonaEnvio:
  dhl_zona1:
    MetodoEnvio: dhl
    numero: 1
    etiqueta: DHL (Envios en la Zona Metropolitana)

  dhl_zona5:
    MetodoEnvio: dhl
    numero: 5
    etiqueta: DHL (Envios al interior del pais)
PrecioEnvio:
  fila1_zona1:
    ZonaEnvio: dhl_zona1
    inicioRango: 0.00
    finRango: 0.50
    precio: 26.90

  fila1_zona5:
    ZonaEnvio: dhl_zona5
    inicioRango: 00
    finRango: 0.50
    precio: 59.60
IntervaloMetodoEnvio:
  dhl_zona_1:
    ZonaEnvio: dhl_zona1
    intervalo: 0.5
    precio: 3.05

  dhl_zona_5:
    ZonaEnvio: dhl_zona5
    intervalo: 0.5
    precio: 7.15

When i do the command php symfony doctrine:data-load, it returns the error:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`mydb`.`PrecioEnvio`, CONSTRAINT `fk_PrecioEnvio_ZonaEnvio` FOREIGN KEY (`idMetodo`, `zona`) REFERENCES `ZonaEnvio` (`id`, `numero`) ON DELETE NO ACTION ON UPDATE NO ACTION)

Since the error has to do with foreign keys, i think it might have something to do with my references in the schema, in lines like ZonaEnvio: dhl_zona1. How do i reference th开发者_开发百科e "parent object"? I think this is not like an inheritance but instead like a weak relationship...

Please forgive the length of my question, but i wanted to provide you with all the necessary information so you can help me!

THANK YOU VERY MUCH FOR YOUR HELP!

That is a mysql constraint error which means the value being inserted does not exist in the related table.

Your fixture format does not match my understanding of what it should be for the version 1.x symfony/doctrine combination. Take a look at this help for the proper format: http://www.symfony-project.org/doctrine/1_2/en/05-Data-Fixtures. For example, creating a row with id 1:

MetodoEnvio:
  MetodoEnvio_1:
    nombre: Retiro Personal
    precio: 0.0

Then when you need to refer to a table that relates to MetodoEnvio with id = 1, you can utilize MetodoEnvio_1, although you probably can simply use id: 1, knowing that the id will be 1.

I found what the problem was. It has to do with how the relations were defined in the schema.yml file. Notice it represents the two fields primary key defining two relations to the same class:

.
.
.
  relations:
     ZonaEnvio:
        local: idmetodo
        foreign: id
        type: one
     ZonaEnvio_2:
        class: ZonaEnvio
        local: zona
        foreign: numero
        type: one

Relatios are defined like this in PrecioEnvio and IntervaloMetodoEnvio, both weak entities of ZonaEnvio. So, I just needed to give values for both relations in my fixtures, and point them to the same instance of the ZonaEnvio object previously defined. Like this:

IntervaloMetodoEnvio:
  dhl_zona_1:
    ZonaEnvio: dhl_zona1
    ZonaEnvio_2: dhl_zona1
    intervalo: 0.5
    precio: 3.05

  dhl_zona_5:
    ZonaEnvio: dhl_zona5
    ZonaEnvio_2: dhl_zona5
    intervalo: 0.5
    precio: 7.15

PrecioEnvio:
  fila1_zona1:
    ZonaEnvio: dhl_zona1
    ZonaEnvio_2: dhl_zona1
    inicioRango: 0.00
    finRango: 0.50
    precio: 26.90

  fila1_zona5:
    ZonaEnvio: dhl_zona5
    ZonaEnvio_2: dhl_zona5
    inicioRango: 00
    finRango: 0.50
    precio: 59.60

Notice

ZonaEnvio: dhl_zona1
ZonaEnvio_2: dhl_zona1

both lines are in the same fixture definition and point to the same instance. That way, fixtures are successfully loaded.

Thanks anyways for your efforts gview!