tiny ‘n’ smart
database layer

Odkazy: dibi | API reference

Oznámení

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

Zlozitejsi query v dibifluent – SELECT v SELECTE

před 8 lety

fliper333
Člen | 36

Ahojte,

Mam problem zapisat jeden zlozitejsi query v dibi a potreboval by som poradit.

V skratke:

Mam vyhladavaci formular (robeny cez nette), ktory mi vrati hodnoty v poli $values a tie potom potrebujem najst v databazy. Problem je, ze nie vsetky formularove polozky su stplce hlavnej tabulky.

Priklad:

Hlavna tabulka je tabulka projektov. Na projekte moze robit viacero zamestnancov → tabulka projekt-zamestnanec. Do nej ukladam aj udaj, kedy dany zamestnanec projekt ukoncil (ked na nom stale robi je tam NULL). No a ja ked si vo formulary vyberiem, ze chcem vidiet len ukoncene projekty (pri ziadnom zamestnancovi nie je NULL), tak to zacina byt trosku problem. A takychto poloziek je v tom formulari viacej. Navyse z tabulky projek SELECTujem tiez udaje z inych tabuliek (ako napriklad datum ukoncenia projektu), ktore sa nedaju riesit jednoduchym joinom.

Rozmyslal som to bud spravit cez MySQL view, alebo cez dibifluent.

Query vyzera nejak takto:

$query = dibi_BIRD::select(array(
                    'id',
                    'project_name',
                    'customer',
                    'SELECT GROUP_CONCAT(bird_user.name SEPARATOR \' | \') FROM (bird_project_user LEFT JOIN bird_user ON (bird_project_user.id_user = bird_user.id)) WHERE (bird_project_user.id_project = bird_project.id)' => 'users',
                    'ecep',
                    'id_other',
                    'SELECT COUNT(*) FROM bird_project_user WHERE bird_project_user.id_project = bird_project.id AND bird_project_user.id_user IN (SELECT id FROM bird_user WHERE type = 2) AND bird_project_user.date_completed IS NOT NULL' => 'se_working',
                    'SELECT COUNT(*) FROM bird_project_user WHERE bird_project_user.id_project = bird_project.id AND bird_project_user.id_user IN (SELECT id FROM bird_user WHERE type = 2)' => 'se_total',
                    'date_start',
                    'date_deadline',
                    'date_revised_deadline',
                    'IF((SELECT COUNT(*) FROM bird_project_user WHERE id_project = bird_project.id AND date_completed IS NULL AND id_user IN (SELECT id FROM bird_user WHERE type = 2) > 0), NULL, (SELECT MAX(date_completed) FROM bird_project_user WHERE id_project = bird_project.id AND id_user IN (SELECT id FROM bird_user WHERE type = 2)))' => 'date_completed'
                ))->from('bird_project');

                $query->leftJoin('bird_customer')->on('bird_project.id_customer = bird_customer.id');

                if ($values['id_se_group']) $query->and('id IN (SELECT id_project FROM bird_project_user WHERE id_user IN (SELECT id FROM bird_user WHERE type = 2 AND id_se_group = %i))', $values['id_se_group']);
                if ($values['id_manager']) $query->and('id IN (SELECT id_project FROM bird_project_user WHERE id_user IN (SELECT id FROM bird_user WHERE TYPE = 2 AND id_manager = %i))', $values['id_manager']);
                if ($values['id_pm']) $query->and('id IN (SELECT id_project FROM bird_project_user WHERE id_user = %i)', $values['id_pm']);
                if ($values['id_project']) $query->and('id_project = %i', $values['id_project']);
                if ($values['ecep']) $query->and('ecep = %i', $values['ecep']);
                if ($values['id_other']) $query->and('id_other = %s', $values['id_other']);
                if ($values['id_customer']) $query->and('id_customer = %i', $values['id_customer']);
                if ($values['attuid']) $query->and('id IN (SELECT id_project FROM bird_project_user WHERE id_user IN (SELECT id FROM bird_user WHERE attuid = %s))', $values['attuid']);
                if ($values['project_name']) $query->and('project_name = %~like~', $values['project_name']);
                if ($values['completed']) $query->and('IF((SELECT COUNT(*) FROM bird_project_user WHERE id_project = bird_project.id AND date_completed IS NULL AND id_user IN (SELECT id FROM bird_user WHERE `type` = 2) > 0), 0, 1) = %i', $values['completed']);
                if ($values['date_start_from']) $query->and('date_start >= %s', $values['date_start_from']);
                if ($values['date_start_to']) $query->and('date_start <= %s', $values['date_start_to']);
                if ($values['date_deadline_from']) $query->and('date_deadline >= %s', $values['date_deadline_from']);
                if ($values['date_deadline_to']) $query->and('date_deadline <= %s', $values['date_deadline_to']);
                if ($values['date_completed_from']) $query->and('IF((SELECT COUNT(*) FROM bird_project_user WHERE id_project = bird_project.id AND date_completed IS NULL AND id_user IN (SELECT id FROM bird_user WHERE `type` = 2) > 0), NULL, (SELECT MAX(date_completed) FROM bird_project_user WHERE id_project = bird_project.id AND id_user IN (SELECT id FROM bird_user WHERE `type` = 2))) >= %s', $values['date_completed_from']);
                if ($values['date_completed_to']) $query->and('IF((SELECT COUNT(*) FROM bird_project_user WHERE id_project = bird_project.id AND date_completed IS NULL AND id_user IN (SELECT id FROM bird_user WHERE `type` = 2) > 0), NULL, (SELECT MAX(date_completed) FROM bird_project_user WHERE id_project = bird_project.id AND id_user IN (SELECT id FROM bird_user WHERE `type` = 2))) <= %s', $values['date_completed_to']);
                if ($values['date_revised_deadline_from']) $query->and('date_revised_deadline >= %s', $values['date_revised_deadline_from']);
                if ($values['date_revised_deadline_to']) $query->and('date_revised_deadline <= %s', $values['date_revised_deadline_to']);
                if ($values['cr']) $query->and('id IN (SELECT id_project FROM bird_project_gps_cr WHERE gps_cr = %i)', $values['cr']);
                if ($values['sr']) $query->and('id IN (SELECT id_project FROM bird_project_gps_cr WHERE gps_cr IN (SELECT cr FROM gps_sr WHERE sr = %i))', $values['sr']);

                $result = $query->test();

Neviem ako mam spravne zapisat tie vnorene selecty, pretoze ked tam dam [], tak aj vo vyslednom query su [] namiesto uvodozviek. Ked tam nedam nic, tak mi bodku (tecku) medzi tabulkou a stlpcom da do apostrofov ale pred tabulku a za stlpec uvodzovky neda

Neviem ci riesit tento problem cez dibifluent je stastna cesta, ak poznate lepsiu alternativu, budem velmi rad ak mi poradite, alebo ma nasmerujete spravnym smerom.

Vopred vdaka,

Filip

před 8 lety

HosipLan
Moderator | 4693

zkusil bych místo $query->and() napsat $query->where(), defaultně totiž podmínky spojuje pomocí AND :)

Jo, a $query->test(); nevrací výsledek, pouze vypíše dotaz.

Editoval HosipLan (23. 11. 2011 7:44)

před 8 lety

Milo
Nette Core | 1119

Klauzuli SELECT použij bez pole. Ve zdrojovém kódu DibiFluent se dočteš, jaký modifikátor se pro jakou klauzuli pro pole použije.

$query = dibi::select(
    'id',
    'project_name',
    'customer',
    '(SELECT GROUP_CONCAT([bird_user.name] SEPARATOR %s', ' | ', ')
    FROM (bird_project_user LEFT JOIN bird_user ON (bird_project_user.id_user = bird_user.id)) WHERE (bird_project_user.id_project = bird_project.id) AS [users]'
);

$where = array();   // Pro toto pole se použije modifikátor %and
if ($values['ecep']) {
    // Jednoduchá situace, přeloží se jako AND ecep = '123',
    // ale pokud bude hodnota NULL tak jako ecep IS NULL
    $where['ecep'] = $values['ecep'];
}

if ($values['ecep']) {
    // Ta samá situace, jen chceme specifikovat, jaký modifikátor použít pro hodnotu $values['ecep']
    $where['ecep%i'] = $values['ecep'];
}


if ($values['id_se_group']) {
    // Podmínka není skalár, na toto pole se aplikuje modifikátor %ex (dibi syntax)
    $where[] = array('[id] IN (SELECT [id_project] FROM bird_project_user WHERE id_user IN (SELECT id FROM bird_user WHERE type = 2 AND id_se_group = %i))', $values['id_se_group']);
}


$query->where($where);
$query->test();

před 8 lety

fliper333
Člen | 36

HospiLan diky za odpoved. JJ viem ze test() vypise dotaz, ale o to prave ide. Ten dotaz ktory mi to vygeneruje nema spravnu syntax (uvodzoky / apostrofy) sa zle generuju. Pravdepodobne je chyba v mojom query, lenze neviem ako inak to zapisat. Prikladam obrazok pre znazornenie:

Obrazok

před 8 lety

fliper333
Člen | 36

Milo vdaka, tvoje riesenie je presne to co som hladal. Uz funguje syntax aj vo vetve SELECT, aj vo vetve WHERE.

Este sa spytam ci je toto dobre riesenie mojej situacie (pouzit dibifluent). Rozmyslal som aj o vytverenie MySQL VIEW (povodne som to tak mal, ale strasne tazko sa tam hladali chyby, lebo ten VIEW bol velmi dlhy a komplikovany).

Este raz vdaka

před 8 lety

Milo
Nette Core | 1119

Nevím jak odpovědět na otázku použít/nepoužít DibiFluent. Když používáš dibi tak proč ne. A poskládat takhle velký SQL dotaz bude hodně práce tak či onak.

View by asi pomohlo v otázce výkonosti subselectů. Další věc je, jak často se zadaná kritéria filtrování používají. Např. jak často se filtruje podle id_pm? Protože jestli často, určitě by stálo za to tabulku bird_project_user najoinovat, protože ji máš v subselectech minimálně 6×. Pak se zápis zase o kousek zjednoduší.

Dal bych si tu práci a maximum subselectů bych vyhodil a tabulky na (left)joinoval + komentáře proč. Často to vede ke zjednodušení. A když si dáš záležet, bude to i přehledné. Odsazování se může zdát hloupé, ale mě pomáhá lépe se ve složitých dotazech orientovat a debugovat je.

// Třeba tohle...
$sql = 'SELECT COUNT(*) FROM bird_project_user WHERE bird_project_user.id_project = bird_project.id AND bird_project_user.id_user IN (SELECT id FROM bird_user WHERE type = 2) AND bird_project_user.date_completed IS NOT NULL';

// ...může vypadat takhle
$sql = '
    SELECT
        COUNT(*)
    FROM
        bird_project_user
    WHERE
        bird_project_user.id_project = bird_project.id
        AND
        bird_project_user.id_user IN ( -- Admini všech skupin
            SELECT
                id
            FROM
                bird_user
            WHERE
                type = 2
        )
        AND
        bird_project_user.date_completed IS NOT NULL
';

// Nebo tohle...
$sql = array('id IN (SELECT id_project FROM bird_project_user WHERE id_user IN (SELECT id FROM bird_user WHERE type = 2 AND id_se_group = %i))', $values['id_se_group']);

// ...takhle
$sql = array('
    id IN (
        SELECT
            id_project
        FROM
            bird_project_user
        WHERE
            id_user IN ( -- Admini z vybrané skupiny
                SELECT
                    id
                FROM
                    bird_user
                WHERE
                    type = 2
                    AND
                    id_se_group = %i', $values['id_se_group'], '
            )
    )');

před 8 lety

fliper333
Člen | 36

Diky Milo, presne toto som potreboval ;)