suncica2222 0 Junior Poster

I have association table in mysql that I want to translate into doctrine yaml mappings. List_id and user_id are at the same time composite primary and foreign keys. I'm confused weather I should have in listUserTb entity class both $userId and $user ($listId, $list) fields? Or can id_str from users_tb be automaticaly resolved to user_id in list_user_tb using just $user field in entity class? I've read this article http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/tutorials/composite-primary-keys.html#identity-through-foreign-entities

Basicaly I want to trannslate this sql, I'm also not sure do I need bidirectional association (oneToMany from users_tb and list_tb to list_user_tb) or just manyToOne from ListUserTb is enough?

sql

CREATE TABLE IF NOT EXISTS list_tb (
    list_id               VARCHAR(255) PRIMARY KEY, 
    country               VARCHAR(255) NOT NULL
);

CREATE TABLE IF NOT EXISTS users_tb (           
    id_str          VARCHAR(255) PRIMARY KEY,             
    name            VARCHAR(255) NOT NULL,
    statuses_count  INT(10)      NOT NULL   
);

CREATE TABLE IF NOT EXISTS list_user_tb (
    list_id         VARCHAR(255),
    user_id         VARCHAR(255),
    priority        INT(10)  NOT NULL
    PRIMARY KEY (list_id, user_id)
);

ALTER TABLE list_user_tb 
    ADD CONSTRAINT fk_list_user_tb_list_tb FOREIGN KEY (list_id) REFERENCES list_tb(list_id);

ALTER TABLE list_user_tb 
    ADD CONSTRAINT fk_list_user_tb_users_tb FOREIGN KEY (user_id) REFERENCES users_tb(id_str);

ListUser.yml

ListUserTb:
    type: entity
    table: list_user_tb
    indexes:
        IDX_6D2EC7B13DAE168B:
            columns:
                - list_id
    id: 
        list:
            associationKey: true
        user:
            associationKey: true
    fields:
        priority:
            type: integer
            nullable: true
            unsigned: false
            default: '1'
    manyToOne:
        list:
            targetEntity: ListTb
            cascade: {  }
            mappedBy: null
            inversedBy: null
            joinColumns:
                list_id:
                    referencedColumnName: list_id
            orphanRemoval: false
        user:
            targetEntity: UsersTb
            cascade: {  }
            mappedBy: null
            inversedBy: null
            joinColumns:
                id_str:
                    referencedColumnName: id_str
            orphanRemoval: false
    lifecycleCallbacks: {  }

ListUserTb entity class

/**
 * ListUserTb
 */
class ListUserTb
{
    /**
     * @var string
     */
    private $userId;

    /**
     * @var string
     */
    private $listId;

    /**
     * @var integer
     */
    private $priority;

    /**
     * @var \ListTb
     */
    private $list;

    /**
     * @var \UsersTb
     */
    private $user;

}
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.