tiny ‘n’ smart
database layer

Odkazy: dibi | API reference

Oznámení

Omlouváme se, provoz fóra byl ukončen

Hlavobol obecný, DIBI fluent, problem s join a SUM

Úvod » Dibi
Upozornění: Tohle vlákno je hodně staré.

před 8 lety

demonic
Člen | 20

Zdravím kolegové a zároven' moc prosím o radu.
DB je rozdělena na 6 úrovní zakázky, každá nese nazev, id a cizí klíč z vyšší úrovně(potomek rodič)
5 úroven' má ještě tabulku level5_zakazka_polozky ze které se čerpají všechna data.
Všechny záznamy z level5_zakazka_polozky Mají přiděleno level6_zakazka_id z intervalu 1–7 (1 materiál MA, 2 Mzdy MZ, 3 Stroje ST atpod.)

Předchozí dotaz funguje ale trvá minutu a déle na poměrně málo záznamech (cca 15 000) jak by jste jej optimalizovali?
Potřebuji to v zápisu DIBIFLUENT protože je to pro gridito.
V klasickém query bych to asi nějak spáchal ale tady mě nenepadlo nic lepšího než tahle šílenost..

Prosím o pomoc

public function __construct(\DibiConnection $db, $id = NULL)
{                            //nakl.sumaL5 as naklady
    parent::__construct($db->select("l2.*, COUNT(p.id) as row_count,
                         SUM(l5_polozky.mnozstvi * l5_polozky.cena_mj) as naklady,
                     SUM(l5_polozkyMA.mnozstvi * l5_polozkyMA.cena_mj) as ma,
                     SUM(l5_polozkyMZ.mnozstvi * l5_polozkyMZ.cena_mj) as mz,
                     SUM(l5_polozkyST.mnozstvi * l5_polozkyST.cena_mj) as st

                     atd ..

                    ")->from("::level2_zakazka l2")->where("level1_zakazka_id= %i", $id)->groupBy("l2.id")
                    //->select('(SELECT SUM(mnozstvi*cena_mj) FROM level5_polozky WHERE level6_zakazka_id = 2 AND level5_zakazka_id = l5.id) as naklady')
                        //->leftJoin('(SELECT * FROM ::level5_polozky WHERE level6_zakazka_id = 3 GROUP BY l5.id) l5_polozkyST')->on('l5.id = l5_polozkyST.level5_zakazka_id ')
                    ->leftJoin('::level3_zakazka l3')->on('l2.id = l3.level2_zakazka_id ')
                    ->leftJoin('::level4_zakazka l4')->on('l3.id = l4.level3_zakazka_id ')
                    ->leftJoin('::level5_zakazka l5')->on('l4.id = l5.level4_zakazka_id ')
                    ->leftJoin('::level5_polozky l5_polozky')->on('l5.id = l5_polozky.level5_zakazka_id ')
                    ->leftJoin('(SELECT * FROM ::level5_polozky WHERE level6_zakazka_id = 1) l5_polozkyMA')->on('l5_polozky.id = l5_polozkyMA.id ')
                    ->leftJoin('(SELECT * FROM ::level5_polozky WHERE level6_zakazka_id = 2) l5_polozkyMZ')->on('l5_polozky.id = l5_polozkyMZ.id ')
                    ->leftJoin('(SELECT * FROM ::level5_polozky WHERE level6_zakazka_id = 3) l5_polozkyST')->on('l5_polozky.id = l5_polozkyST.id ')
                    ->leftJoin('(SELECT * FROM ::level5_polozky WHERE level6_zakazka_id = 4) l5_polozkyD')->on('l5_polozky.id = l5_polozkyD.id ')
                    ->leftJoin('(SELECT * FROM ::level5_polozky WHERE level6_zakazka_id = 5) l5_polozkyC')->on('l5_polozky.id = l5_polozkyC.id ')
                    ->leftJoin('(SELECT * FROM ::level5_polozky WHERE level6_zakazka_id = 6) l5_polozkyN')->on('l5_polozky.id = l5_polozkyN.id ')
                    ->leftJoin('(SELECT * FROM ::level5_polozky WHERE level6_zakazka_id = 7) l5_polozkySL')->on('l5_polozky.id = l5_polozkySL.id ')
                    ->leftJoin('::percentage p')->on('l5.id = p.level5_zakazka_id ')

                    );
}

Zkoušel jsem i styl zápisu SUM(DISTINCT (SELECT SUM(mnozstvi*cena_mj, to sice dává ‚správné hodnoty‘ avšak díky DISTINCT pouze do momentu, kdy se neobjeví stejné sumy.

Děkuji za jakoukoliv pomoc

Editoval demonic (30. 4. 2011 21:01)

před 8 lety

Milo
Nette Core | 1119

První co mě napadá je použít vícenásobnou podmínku joinu namísto subselectu (pokud to DB umí).

dibi::query("
SELECT
    l2.*,
    COUNT(p.id) as row_count,
    SUM(l5_polozky.mnozstvi   * l5_polozky.cena_mj  ) as naklady,
    SUM(l5_polozkyMA.mnozstvi * l5_polozkyMA.cena_mj) as ma,
    SUM(l5_polozkyMZ.mnozstvi * l5_polozkyMZ.cena_mj) as mz,
    SUM(l5_polozkyST.mnozstvi * l5_polozkyST.cena_mj) as st
FROM
    ::level2_zakazka l2
    LEFT JOIN ::level3_zakazka l3         ON l2.id = l3.level2_zakazka_id
    LEFT JOIN ::level4_zakazka l4         ON l3.id = l4.level3_zakazka_id
    LEFT JOIN ::level5_zakazka l5         ON l4.id = l5.level4_zakazka_id
    LEFT JOIN ::level5_polozky l5_polozky ON l5.id = l5_polozky.level5_zakazka_id

    LEFT JOIN ::level5_polozky l5_polozkyMA ON l5_polozky.id = l5_polozkyMA.id AND level6_zakazka_id = 1
    LEFT JOIN ::level5_polozky l5_polozkyMZ ON l5_polozky.id = l5_polozkyMZ.id AND level6_zakazka_id = 2
    LEFT JOIN ::level5_polozky l5_polozkyST ON l5_polozky.id = l5_polozkyST.id AND level6_zakazka_id = 3
    LEFT JOIN ::level5_polozky l5_polozkyD  ON l5_polozky.id = l5_polozkyD.id  AND level6_zakazka_id = 4
    LEFT JOIN ::level5_polozky l5_polozkyC  ON l5_polozky.id = l5_polozkyC.id  AND level6_zakazka_id = 5
    LEFT JOIN ::level5_polozky l5_polozkyN  ON l5_polozky.id = l5_polozkyN.id  AND level6_zakazka_id = 6
    LEFT JOIN ::level5_polozky l5_polozkySL ON l5_polozky.id = l5_polozkySL.id AND level6_zakazka_id = 7
    LEFT JOIN ::percentage     p            ON l5.id         = p.level5_zakazka_id
WHERE
    level1_zakazka_id= %i", $id, "
GROUP BY
    l2.id
");

Anebo takto, pokud umí DB převést boolean na INT.

dibi::query("
SELECT
        l2.*,
        COUNT(p.id) as row_count,
        SUM(l5_polozky.mnozstvi * l5_polozky.cena_mj  ) as naklady,
        SUM(l5_polozky.mnozstvi * l5_polozky.cena_mj * (level6_zakazka_id = 1)) as ma,
        SUM(l5_polozky.mnozstvi * l5_polozky.cena_mj * (level6_zakazka_id = 2)) as mz,
        SUM(l5_polozky.mnozstvi * l5_polozky.cena_mj * (level6_zakazka_id = 3)) as st
FROM
        ::level2_zakazka l2
        LEFT JOIN ::level3_zakazka l3         ON l2.id = l3.level2_zakazka_id
        LEFT JOIN ::level4_zakazka l4         ON l3.id = l4.level3_zakazka_id
        LEFT JOIN ::level5_zakazka l5         ON l4.id = l5.level4_zakazka_id
        LEFT JOIN ::level5_polozky l5_polozky ON l5.id = l5_polozky.level5_zakazka_id
WHERE
        level1_zakazka_id= %i", $id, "
GROUP BY
        l2.id
");