php - 如何使用 Doctrine 和 postgreSQL 获得左连接的第一个结果

Question

在一个使用 Doctrine 2 和 postgreSQL 数据库的项目中，我在实体 Gynecologist 和他的电子邮件和电话号码之间有一个简单的一对多关系。我想查询数据库，为每个人获取一行，其中包含第一个插入的电子邮件和号码。我希望得到如下结果:

-------------------
SURNAME  | NAME  | EMAIL           | TEL. NR.|
Surname1 | Name1 | email1@gmail.it | number1 |
-------------------

我试过:

$columns = array('g.id', 'g.companyName', 'g.surname', 'g.name', 'e.email', 't.number');

$queryBuilder = $this->getDoctrine()
    ->getRepository('AppBundle:Gynecologist')
    ->createQueryBuilder('g')
    ->select($columns)
    ->join('g.emails', 'e')
    ->join('g.telephoneNumbers', 't')
    ->orderBy('g.surname', 'ASC')
    ->addOrderBy('g.name', 'ASC')
    ->groupBy('g.id')
    ->addGroupBy('g.companyName')
    ->addGroupBy('g.surname')
    ->addGroupBy('g.name')
    ->addGroupBy('e.email')
    ->addGroupBy('t.number');

这是生成的查询:

SELECT g0_.id AS id_0, g0_.company_name AS company_name_1, g0_.surname AS surname_2, g0_.name AS name_3, g1_.email AS email_4, g2_.number AS number_5 
FROM gynecologists 
g0_ INNER JOIN gynecologists_emails g1_ ON g0_.id = g1_.gynecologist_id 
INNER JOIN gynecologists_telephone_numbers g2_ ON g0_.id = g2_.gynecologist_id
GROUP BY g0_.id, g0_.company_name, g0_.surname, g0_.name, g1_.email, g2_.number 
ORDER BY g0_.surname ASC, g0_.name ASC 
LIMIT 500 OFFSET 0

根据 StackOverflow 用户收到的建议，我也尝试了:

$columns = array('DISTINCT g.id', 'g.companyName', 'g.surname', 'g.name', 'e.email', 't.number');

$queryBuilder = $this->getDoctrine()
    ->getRepository('AppBundle:Gynecologist')
    ->createQueryBuilder('g')
    ->select($columns)
    ->join('g.emails', 'e')
    ->join('g.telephoneNumbers', 't')
    ->orderBy('g.surname', 'ASC')
    ->addOrderBy('g.name', 'ASC');

SELECT DISTINCT g0_.id AS id_0, g0_.company_name AS company_name_1, g0_.surname AS surname_2, g0_.name AS name_3, g1_.email AS email_4, g2_.number AS number_5 
FROM gynecologists g0_ 
INNER JOIN gynecologists_emails g1_ ON g0_.id = g1_.gynecologist_id 
INNER JOIN gynecologists_telephone_numbers g2_ ON g0_.id = g2_.gynecologist_id
ORDER BY g0_.surname ASC, g0_.name ASC 
LIMIT 500 OFFSET 0

但结果总是:

-------------------
SURNAME  | NAME  | EMAIL           | TEL. NR.|
Surname1 | Name1 | email1@gmail.it | number1 |
Surname1 | Name1 | email1@gmail.it | number2 |
Surname1 | Name1 | email2@gmail.it | number1 |
Surname1 | Name1 | emai21@gmail.it | number2 |
-------------------

谢谢。

Answer 1

我认为它应该适合你:

$columns = array('DISTINCT g.id', 'g.companyName', 'g.surname', 'g.name', 'e.email', 't.number');

$queryBuilder = $this->getDoctrine()
    ->getRepository('AppBundle:Gynecologist')
    ->createQueryBuilder('g')
    ->select($columns)
    ->join('g.emails', 'e')
    ->join('g.telephoneNumbers', 't')
    ->orderBy('g.surname', 'ASC')
    ->addOrderBy('g.name', 'ASC');