我有 2 个表:comment
和 user
comment
我有以下列:id
、user_id
、conversation_id
、body
...
在user
我有以下列:id
,username
...
我想用教义执行以下查询:
"SELECT c.*, u.username FROM comment c LEFT JOIN user u on c.user_id = u.id WHERE c.conversation_id = '5'"
换句话说,当我获得评论列表时,我想显示user
表中的name
。
我知道如何在SQL中做到这一点,但我无法在教义中做到这一点。
它应该看起来像这样:
$q = $this
->createQueryBuilder('u')
->where('u.conversationId = :conversationId')
->setParameter('conversationId', $conversationId)
->getQuery();
教义评论实体:
Test'SocialBundle'Entity'Comment:
type: entity
table: comment
repositoryClass: Test'SocialBundle'Entity'CommentRepository
fields:
id:
type: integer
id: true
generator:
strategy: AUTO
conversationId:
type: integer
nullable: false
column: conversation_id
userId:
type: integer
nullable: false
column: user_id
body:
type: string
nullable: false
length: '300'
lifecycleCallbacks: { }
原则用户实体:
Test'UserBundle'Entity'User:
type: entity
table: user
repositoryClass: Test'UserBundle'Entity'UserRepository
fields:
id:
type: integer
id: true
generator:
strategy: AUTO
username:
type: string
length: '100'
nullable: true
column: username
name:
type: string
length: '100'
nullable: true
column: name
manyToMany:
roles:
targetEntity: Role
joinTable:
name: user_role
joinColumns:
user_id:
referencedColumnName: id
inverseJoinColumns:
role_id:
referencedColumnName: id
lifecycleCallbacks: { }
谢谢!
您必须从 CommentRepository 调用请求,它必须看起来像
$qb = $this->createQueryBuilder('c');
$qb->select('c, u.username')
->leftJoin('c.userId', 'u')
->where($qb->expr()->eq('c.conversationId', $conversationId));
$qb->getQuery();