Symfony：加载具有多列主键的对象的装置，这些也是外键

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.

我正在为symfony中的项目编写灯具,并且在尝试将灯具添加到具有多列主键的对象时也有问题,这也是其他表的外键。

Here's the schema in MySql Workbench:

这是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.

我使用workbench生成了sql代码,然后使用symfony任务php symfony doctrine:build-schema生成了schema.yml文件。

this is the sql generated for these tables:

这是为这些表生成的sql:

-- -----------------------------------------------------
-- 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:

当我执行命令php symfony doctrine:data-load时,它返回错误:

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 the "parent object"? I think this is not like an inheritance but instead like a weak relationship...

由于错误与外键有关,我认为这可能与我在模式中的引用有关,比如ZonaEnvio:dhl_zona1。我如何引用“父对象”?我认为这不像是继承,而是像弱关系......

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!

非常感谢您的帮助!

2 个解决方案

#1

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

这是一个mysql约束错误,这意味着相关表中不存在要插入的值。

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:

你的夹具格式与我对版本1.x symfony / doctrine组合应该是什么的理解不符。请查看此帮助以获取正确的格式:http://www.symfony-project.org/doctrine/1_2/en/05═-Fixtures。例如,创建一个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.

然后当你需要引用一个与id = 1的MetodoEnvio相关的表时,你可以使用MetodoEnvio_1,尽管你可能只需要使用id:1,知道id为1。

#2

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:

我发现了问题所在。它与schema.yml文件中如何定义关系有关。请注意,它表示定义与同一个类的两个关系的两个字段主键:

.
.
.
  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:

Relatios在PrecioEnvio和IntervaloMetodoEnvio中定义如下,这两者都是ZonaEnvio的弱实体。所以,我只需要为我的灯具中的两个关系赋予值,并将它们指向先前定义的ZonaEnvio对象的相同实例。喜欢这个:

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!

无论如何,谢谢你的努力gview!