Oznámení
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:
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 ;)