时间戳

首页 > 解决方案 > 如何使用联系人数据返回客户数据，如果客户没有联系人，则返回客户数据

typescript - 如何使用联系人数据返回客户数据，如果客户没有联系人，则返回客户数据

问题描述

在我的应用程序中，我有两个名为clientsand的表，contacts并且clients可以有很多contacts关于此的表，我们可以知道在contactshave the内部client_id，我想创建一个对名为的特定路由的查询，该路由/clients/:id将返回客户端数据与该客户端的所有联系人连接有。所以我想退货

{
    client: [
     {
      name_client:...,
      email_client:...,
      telephone_client:....,
      contacts: [
        allcontacts
      ]
     }
    ]
}

但我没有得到我想要的，我发现这个查询的其他问题

select
    distinct clients.id as client_id,
    clients.name as client_name,
    clients.email as client_email,
    clients.telephone as client_telephone,
    contacts.id as contact_id,
    contacts.name as contact_name
from
    contacts
 inner join
    clients
    on contacts.client_id = clients.id
where
    contacts.client_id = 'd9935b6d-0497-4a45-b472-ed3b4f85cd60'

如果我的客户没有联系人，将返回一个空对象，但我不会这样，我想返回客户数据。

在此处输入图像描述

标签： typescriptpostgresqltypeorm

解决方案

请将您的查询更改为：

select distinct clients.id as client_id,
       clients.name as client_name,
       clients.email as client_email,
       clients.telephone as client_telephone,
       contacts.id as contact_id,
       contacts.name as contact_name
  from clients
       left join contacts
              on contacts.client_id = clients.id
 where clients.client_id = 'd9935b6d-0497-4a45-b472-ed3b4f85cd60'

left join返回所有匹配的clients行（可能只有一个）以及任何contacts匹配的行client_id。如果没有这样的行，那么contacts.id和contacts.name将是null。

如果你想让 PostgreSQL 从这里为你构建一个 json 文档，那么使用jsonb函数来为你完成它：

with base as (
  select distinct clients.id as client_id,
         clients.name as client_name,
         clients.email as client_email,
         clients.telephone as client_telephone,
         contacts.id as contact_id,
         contacts.name as contact_name
    from clients
         left join contacts
                on contacts.client_id = clients.id
   where clients.client_id = 'd9935b6d-0497-4a45-b472-ed3b4f85cd60'
), aggregate_contacts as (
  select client_id, client_name, client_email, client_telephone,
         jsonb_agg(
           case 
             when contact_id is null then '{}'::jsonb
             else jsonb_build_object( 
                'contact_id', contact_id, 'contact_name', contact_name
             )
           end
         ) as contacts
    from base
   group by client_id, client_name, client_email, client_telephone
)
select to_jsonb(aggregate_contacts) as result
  from aggregate_contacts;

使用lateral join：

select to_jsonb(q) as result
  from (select clients.id as client_id,
               clients.name as client_name,
               clients.email as client_email,
               clients.telephone as client_telephone,
               case
                 when jsonb_agg(x) = '[null]'::jsonb then '[]'::jsonb
                 else jsonb_agg(x)
               end as contacts
          from clients 
               left join lateral 
                    (select id as contact_id, name as contact_name
                       from contacts
                      where id = clients.id) x on true
         where client_id = 'd9935b6d-0497-4a45-b472-ed3b4f85cd60'
         group by clients.id, clients.name, clients.email, clients.telephone
       ) as q

推荐阅读