Hoe kan ik een relatie leggen tussed de tabellen van "CRMDeals basic Deals@hst" and "CRMcompanies basic companies@hst"

[rmy6]

Ik kan geen unieke kolom vinden om beide tabellen met elkaar te verbinden. De ID kolom in de tabel CRMCompanies is anders dan de kolom in CRMDeals. Is de relatieverbinding via een derde tabel? Zo ja, welke tabel?

[forums]

In HubSpot kun je 1 bedrijf opvoeren als primaire relatie op een deal, en meerdere bedrijven als betrokken (zie Create and use association labels). Binnen HubSpot worden relaties tussen entiteiten zoals company en deal “associaties” genoemd.

De lijst van beschikbare associaties en hun labels zijn zichtbaar via de volgende queries:

select *
from associationtypesbyfromobjecttypetoobjecttype('DEAL', 'COMPANY')
--
-- 341 = deal_to_company_unlabeled
-- 5 = deal_to_company
--

select *
from associationlabelsbyfromobjecttypetoobjecttype('DEAL', 'COMPANY')
--
-- 341 = no label
-- 5 = "Primary"
--

Een associatie ligt tussen twee “objecten”; in dit geval enerzijds een “deal” en anderzijds een “company”. Elk object heeft een uniek nummer; de ID. Via de volgende query kun je de details opvragen van een object op ID:

select *
from   objects
where  id = 5433420328

Het veld properties is helaas nog niet geexpandeerd.

Alle Associaties van een Deal Opvragen

Helaas is er nog geen tabel voor dealassociaties. Als tijdelijke workaround kan de volgende Startup SQL gebruikt worden:

--
-- Loop over all pages that can be retrieved from the HubSpot API.
--
declare
  l_orig_system_group varchar2;
  l_after_id          number;
  l_loop              boolean;
  l_first             boolean;
begin
  l_loop     := true;
  l_first    := true;
  l_after_id := null;
  --
  while l_loop
  loop
    l_orig_system_group := 'HUBSPOT-DEALS-' || to_char(newid());    
    insert into NATIVEPLATFORMSCALARREQUESTS@hst
    ( url
    , http_method
    , orig_system_group
    )
    select 'https://api.hubapi.com/crm/v4/objects/deal?limit=100&associations=company' || case when l_after_id is not null then '&after=' || to_char(l_after_id) end url
    ,      'GET'
    ,      l_orig_system_group
    ;
    if l_first
    then
      create or replace table DealCompanies@InMemoryStorage
      as
      select jte.*
      from   NATIVEPLATFORMSCALARREQUESTS@hst nst
      join   jsontable
             ( 'results[*].associations.companies.results[*]'
               passing nst.result_text
               columns deal_id          int64    path '::ancestor.::ancestor.::ancestor.id'
               ,       object_id        int64    path 'id'
               ,       association_type varchar2 path 'type'
             ) jte
      where  nst.orig_system_group = l_orig_system_group
      ;
      l_first := false;
    else
      insert into DealCompanies@InMemoryStorage
      select jte.*, null rowid$
      from   NATIVEPLATFORMSCALARREQUESTS@hst nst
      join   jsontable
             ( 'results[*].associations.companies.results[*]'
               passing nst.result_text
               columns deal_id          int64    path '::ancestor.::ancestor.::ancestor.id'
               ,       object_id        int64    path 'id'
               ,       association_type varchar2 path 'type'
             ) jte
      where  nst.orig_system_group = l_orig_system_group
      ;
    end if;
    --
    -- Extract offset to start at.
    --
    begin
      select jte.after_id
      into   l_after_id
      from   NATIVEPLATFORMSCALARREQUESTS@hst nst
      join   jsontable
             ( 'paging'
               passing nst.result_text
               columns after_id number path 'next.after'
             ) jte
      where  nst.orig_system_group = l_orig_system_group
      ;
    exception
      when no_data_found
      then
        l_after_id := null;
        l_loop := false;
    end;
  end loop;
end;

Na het openen van de database (wat enigszins langzamer zal gaan, reken op 1 second per 100 deals in HubSpot), is er een tabel DealCompanies@InMemoryStorage beschikbaar.

[forums]

Deze vraag is automatisch gesloten na 2 weken inactiviteit. Het laatste gegeven antwoord is gemarkeerd als oplossing.

Gelieve een nieuwe vraag te stellen via een apart topic als het probleem opnieuw optreedt. Gelieve in de nieuwe vraag een link naar dit topic op te nemen door de URL er van in de tekst te plakken.