Doctrine\DBAL\Schema\SchemaException: How to include one or more FKs in a declared table index

Question

I am using Symfony/YaML/Doctrine to store data in an MySQL backend. I want to create an index (to speed queries), using some of the FKs I have declared. However, when I try to generate the schema, I get the following error:

[Doctrine\DBAL\Schema\SchemaException] There is no column with name 'to_user_id' on table 'pms'

When I comment out the index declaration, I am able to generate the table without any problems. This is what my YaML file looks like:

AppBundle\Entity\PrivateMessage:
    type: entity
    table: pms
    repositoryClass: PMSRepository
    id:
        id:
            type: integer
            generator: { strategy: AUTO }

    manyToOne:
        from_user:
            targetEntity: User
            joinColumn:
                name: from_user_id
                referencedColumnName: id
                nullable: false
                onDelete: RESTRICT
                onUpdate: CASCADE

    manyToOne:
        to_user:
            targetEntity: User
            joinColumn:
                name: to_user_id
                referencedColumnName: id
                nullable: false
                onDelete: RESTRICT
                onUpdate: CASCADE                

    manyToOne:
        message_type:
            targetEntity: PrivateMessageType
            joinColumn:
                name: pms_type_id
                referencedColumnName: id
                nullable: false
                onDelete: RESTRICT
                onUpdate: CASCADE  


    manyToOne:
        message_status:
            targetEntity: PrivateMessageStatus
            joinColumn:
                name: pms_status_id
                referencedColumnName: id
                nullable: false
                onDelete: RESTRICT
                onUpdate: CASCADE  

    indexes:
        idx_pms:
            columns: [from_user_id, to_user_id, created_at]          


    fields:
        subject:
            type: text
            nullable: false

        body:
            type: text
            nullable: false

        attach_1:
            type: string
            length: 128
            nullable: true

        attach_2:
            type: string
            length: 128
            nullable: true

        attach_3:
            type: string
            length: 128
            nullable: true

        created_at:
            type: datetime

ScayTrase ScayTrase · Accepted Answer · 2016-05-23T06:07:20

The problem is simple: do not split all the assotiations in several manyToOne. Correct variant should be like

AppBundle\Entity\PrivateMessage:
    type: entity
    table: pms
    repositoryClass: PMSRepository
    id:
        id:
            type: integer
            generator: { strategy: AUTO }

    manyToOne:
        from_user:
            targetEntity: User
            joinColumn:
                name: from_user_id
                referencedColumnName: id
                nullable: false
                onDelete: RESTRICT
                onUpdate: CASCADE
        to_user:
            targetEntity: User
            joinColumn:
                name: to_user_id
                referencedColumnName: id
                nullable: false
                onDelete: RESTRICT
                onUpdate: CASCADE                
        message_type:
            targetEntity: PrivateMessageType
            joinColumn:
                name: pms_type_id
                referencedColumnName: id
                nullable: false
                onDelete: RESTRICT
                onUpdate: CASCADE  
        message_status:
            targetEntity: PrivateMessageStatus
            joinColumn:
                name: pms_status_id
                referencedColumnName: id
                nullable: false
                onDelete: RESTRICT
                onUpdate: CASCADE  

    indexes:
        idx_pms:
            columns: [from_user_id, to_user_id, created_at]          


    fields:
        subject:
            type: text
            nullable: false

        body:
            type: text
            nullable: false

        attach_1:
            type: string
            length: 128
            nullable: true

        attach_2:
            type: string
            length: 128
            nullable: true

        attach_3:
            type: string
            length: 128
            nullable: true

        created_at:
            type: datetime

Doctrine\DBAL\Schema\SchemaException: How to include one or more FKs in a declared table index

1 Answers