Autor Zpráva
Nikolaj
Profil *
Zdravím, nevím si rady s jedním SQL dotazem, kterým chci vytvořit pohled přes více tabulek, který budu poté volat.

SQL příkaz je:
CREATE OR REPLACE VIEW w_contact_persons AS (
    SELECT DISTINCT ON (up.id) up.id, (COALESCE(up.title_before,'') || ' ' || COALESCE(up.first_given_name,'') || ' ' || up.surname || ' ' || COALESCE(up.title_after,'')) AS name, 
            COALESCE((cc.country_code_prefix || ud.telephone),'') AS tel, 
             COALESCE(ue.email,'' ) AS email, COALESCE(up.function, '') AS function, po.organisation_unit_id AS ou_id
    FROM    persons_x_organisation_units po, 
            persons up, 
            dialing ud, 
            dialing_country_codes cc, 
            emails ue 
    WHERE   po.person_id = up.id AND
            up.is_active = true AND 
            (up.primary_email_id = ue.id OR up.primary_email_id IS NULL)  AND 
            (up.primary_telephone_id = ud.id OR up.primary_telephone_id IS NULL) AND 
            ud.country_code_id = cc.id
);

Jak jde vidět z příkazu, jedná se o spojení tabulek s údaji o kontaktech.
tabulka persons má povinný sloupec pouze surname (obsahuje FK na dialing, emails)
tabulka dialing obsahuje telefonní čísla (obsahuje FK na country_codes)
tabulka country_codes je číselník tel. předvoleb jako např. +420 apod.
tabulka emails obsahuje emaily
tabulka persons_x_organisation_units je vazební a zajišťuje kardinalitu M:N


Abych se však dostal k jádru pudla, vše funguje perfektně pokud jsou u řádku v tabulce persosns nastaveny cizí klíče na email a telefon, pokud tomu tak není, nezobrazí se nic. Ono je to celkem logické, ale nějak mi to dneska nemyslí a neumím přijít na to, jak zobrazit i osoby, které nemají zadaný email a telefon.

Věřím, že je to jako obvykle nějaká stupidita...

Děkuji předem za náměty
juriad
Profil
Nikolaj:
Chceš použít LEFT JOIN.
...
FROM persons_x_organisation_units po
    JOIN persons up
        ON po.person_id = up.id
    LEFT JOIN dialing ud
        ON up.primary_telephone_id = ud.id
    JOIN dialing_country_codes cc
        ON up.primary_email_id = ue.id
    LEFT JOIN emails ue
        ON ud.country_code_id = cc.id
Nikolaj
Profil *
No alternativně to lze udělat takto:

CREATE OR REPLACE VIEW t_w_u_contact_persons AS (
    SELECT DISTINCT ON (up.id) up.id, (COALESCE(up.title_before,'') || ' ' || COALESCE(up.first_given_name,'') || ' ' || up.surname || ' ' || COALESCE(up.title_after,'')) AS name, 
            CASE WHEN up.primary_telephone_id IS NOT NULL THEN  COALESCE((cc.country_code_prefix || ud.telephone),'') ELSE '' END AS tel, 
            CASE WHEN up.primary_email_id IS NOT NULL THEN  COALESCE (ue.email,'' ) ELSE '' END AS email, COALESCE(up.function, '') AS function, po.organisation_unit_id AS ou_id
    FROM persons_x_organisation_units po
    JOIN persons up
        ON po.person_id = up.id
    LEFT JOIN dialing ud
        ON (up.primary_telephone_id = ud.id OR up.primary_telephone_id IS NULL)
    JOIN dialing_country_codes cc
        ON ud.country_code_id = cc.id
    LEFT JOIN emails ue
        ON (up.primary_email_id = ue.id OR up.primary_email_id IS NULL)
    WHERE   
            up.is_active = true
);

Nevím proč, ale zdá se mi to s těmi case docela pomalé :) Nějaké tipy na zrychlení?
Camo
Profil
Nikolaj:
To AS za názvom pohľadu tam patrí? Tak isto sa mi nezdá to DISTINC ON. Je to MySQL či niečo iné?
Nikolaj
Profil *
Camo:
No AS tam nemusí samozřejmě být, ale ja potřebuji, ať vystupem je sloupec pojmenovaný např. email. Jinak distinct tam nemá být, to je relikt....
Alphard
Profil
Nikolaj:
Měl jsem rozepsanou odpověď s použitím left join, [#2] juriad mě předběhl. Takže jeho řešení funguje a už se řeší jen výkon? Nebo problém přetrvává?
Proč máte v on( or is null)? Řešení, tak jak bylo uvedno ([#2]), nefunguje?

A uveďte současnou podobu dotazu, když už to někdo čte a řekne, že je blbě distinct, nehodí se napsat, že tam vůbec není :-)
Camo
Profil
Nikolaj:
Ja by som povedal, že to AS tam nie že nemusí byť, ale že tam nemá byť vôbec. to isté platí ohľadom toho ON pri distinct.
Nikolaj
Profil *
Alphard:
Takže abych to uvedl na pravou míru:
1) pro mou potřebu tam DISTINCT musím mít, protože db schéma, které jsem zde nastínil je pouze malá část a vážu dotaz ještě dále...

2) OR je použít z toho důvodu, že FK na tabulkách např. email a dialing nemá povinnou hodnotu (protože email a telefon nejsou povinné položky např. kontaktu. Při tvorbě nového, tedy v případě, že není zadán email, telefon nebo obojí, tak se ani nevytvoří příslušné řádky v uvedených tabulkách)


Důsledkem tedy je, že řešení #3 je vhodné pro mé použití. Obecně, pokud by byly hodnoty FK povinné, tak OR nepoužijeme.

V tuto chvíli se mi jedná o jedinou věc, jak tento dotaz zrychlit. Ty case se mi tam nelíbí...
Kajman
Profil
Nikolaj:

Pro jakou databázi ten view sestavujete?
Nikolaj
Profil *
Kajman:
postgresql
Kajman
Profil
Nikolaj:

Přijde mi, že to opravdu děláte příliš komplikovaně, pokud je jeden člověk maximálně v jedné jednotce organizace, zkusil bych
SELECT up.id,
       Concat_ws(' ', up.title_before, up.first_given_name, up.surname,
       up.title_after)                              AS name,
       Concat(cc.country_code_prefix, ud.telephone) AS tel,
       ue.email,
       up.function,
       po.organisation_unit_id                      AS ou_id
FROM   persons_x_organisation_units po
       JOIN persons up
         ON po.person_id = up.id
       LEFT JOIN dialing ud
              ON up.primary_telephone_id = ud.id
       LEFT JOIN dialing_country_codes cc
              ON ud.country_code_id = cc.id
       LEFT JOIN emails ue
              ON up.primary_email_id = ue.id
WHERE  up.is_active = true

Pokud tam je m:n relace, tak např. group by s vhodnou agregační funkci kolem po.organisation_unit_id.

Pomalost není v použití case, ale právě v těch nesmyslných podmínkách typu "OR up.primary_telephone_id IS NULL", které osobě bez vyplněného telefonu připojí všechny řádky s telefony a k těm všem nesmyslným řádků klidně ještě dalším připojením pro každý řádek ještě všechny řádky z tabulky emailů.

Vaše odpověď

Mohlo by se hodit


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm: