如何获得左连接的第一个结果与 Doctrine 和 postgreSQL


How to get the first result of a left join with Doctrine and postgreSQL

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

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

谢谢。

我认为它应该适合您:

$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');