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;
}