Výživný SQL dotaz
napsal InzaAneb, jak ROZHODNĚ nepsat SQL…
To, co uvidíte na následujících řádcích, skutečně JE SQL dotaz – jeden!
Nevím jestli stále, ale svého času BYL nasazen na PRODUKČNÍM(!) serveru, jedné PHP aplikace na jejíž úpravách jsem se podílel…
Jestli jste ještě nikdy neviděli noční můru a KATA všech serverů, tak tady je… (názvy entit a atributů byly z pochopitelných důvodů pozměněny…)
SELECT v.id, m.RI AS category_id, c.categorie AS category_name, c.first_name AS category_rank, c.icon
FROM positions AS v
INNER JOIN company_tables.mapping AS m ON ( m.klient_id = '284' AND m.LE = '1' AND m.RE = '9' AND m.LI=v.id )
INNER JOIN company_employers.categories AS c ON (c.id = m.RI)
LEFT JOIN company_employers.vb_pubstatus AS vbps ON (vbps.id=v.vb_pubstatus)
WHERE
v.klient_id = '284'
AND v.publiceer='1'
AND (`function` LIKE '%keyword\'s%' OR `locatie` LIKE '%keyword\'s%' OR `property` LIKE '%keyword\'s%'
OR `profile` LIKE '%keyword\'s%' OR `adv_profile` LIKE '%keyword\'s%' OR `func` LIKE '%keyword\'s%'
OR `functiee` LIKE '%keyword\'s%' OR `branche` LIKE '%keyword\'s%' OR `regio` LIKE '%keyword\'s%'
OR `functiegr` LIKE '%keyword\'s%' OR `itemdn` LIKE '%keyword\'s%' OR `money` LIKE '%keyword\'s%'
OR `dienstvb` LIKE '%keyword\'s%' OR `gewuren` LIKE '%keyword\'s%' OR `itemdr` LIKE '%keyword\'s%'
OR `functienaam` LIKE '%keyword\'s%' OR `wervaring` LIKE '%keyword\'s%' OR `beoordeling` LIKE '%keyword\'s%'
OR `verzekering` LIKE '%keyword\'s%' OR `adv_next_category` LIKE '%keyword\'s%' OR `opmerkingen` LIKE '%keyword\'s%'
OR `trefwoorden` LIKE '%keyword\'s%' OR `kenmerken` LIKE '%keyword\'s%' OR `adv_property` LIKE '%keyword\'s%'
OR `duur` LIKE '%keyword\'s%' OR `criteria_1` LIKE '%keyword\'s%' OR `criteria_2` LIKE '%keyword\'s%'
OR `criteria_3` LIKE '%keyword\'s%' OR `criteria_4` LIKE '%keyword\'s%'
OR `criteria_5` LIKE '%keyword\'s%' OR `criteria_6` LIKE '%keyword\'s%'
OR `criteria_7` LIKE '%keyword\'s%' OR `property_title` LIKE '%keyword\'s%' OR `profile_title` LIKE '%keyword\'s%'
OR `adv_profile_title` LIKE '%keyword\'s%' OR `func_title` LIKE '%keyword\'s%' OR `adv_function_next_title` LIKE '%keyword\'s%'
OR `adv_property_title` LIKE '%keyword\'s%' OR `Photo` LIKE '%keyword\'s%' OR `File` LIKE '%keyword\'s%' OR `Thumbnail` LIKE '%keyword\'s%'
OR `function_1` LIKE '%keyword\'s%' OR `function_2` LIKE '%keyword\'s%' OR `function_3` LIKE '%keyword\'s%'
OR `function_4` LIKE '%keyword\'s%' OR `function_5` LIKE '%keyword\'s%' OR `function_6` LIKE '%keyword\'s%'
OR `function_7` LIKE '%keyword\'s%' OR `profile_1` LIKE '%keyword\'s%' OR `profile_2` LIKE '%keyword\'s%'
OR `profile_3` LIKE '%keyword\'s%' OR `profile_4` LIKE '%keyword\'s%' OR `profile_5` LIKE '%keyword\'s%'
OR `profile_6` LIKE '%keyword\'s%' OR `profile_7` LIKE '%keyword\'s%' OR `adv_profile_1` LIKE '%keyword\'s%'
OR `adv_profile_2` LIKE '%keyword\'s%' OR `adv_profile_3` LIKE '%keyword\'s%' OR `adv_profile_4` LIKE '%keyword\'s%'
OR `adv_profile_5` LIKE '%keyword\'s%' OR `adv_profile_6` LIKE '%keyword\'s%' OR `adv_profile_7` LIKE '%keyword\'s%'
OR `func_1` LIKE '%keyword\'s%' OR `func_2` LIKE '%keyword\'s%' OR `func_3` LIKE '%keyword\'s%' OR `func_4` LIKE '%keyword\'s%' OR `func_5` LIKE '%keyword\'s%' OR `func_6` LIKE '%keyword\'s%' OR `func_7` LIKE '%keyword\'s%' OR `adv_function_next_1` LIKE '%keyword\'s%' OR `adv_function_next_2` LIKE '%keyword\'s%' OR `adv_function_next_3` LIKE '%keyword\'s%' OR `adv_function_next_4` LIKE '%keyword\'s%' OR `adv_function_next_5` LIKE '%keyword\'s%' OR `adv_function_next_6` LIKE '%keyword\'s%' OR `adv_function_next_7` LIKE '%keyword\'s%' OR `property_1` LIKE '%keyword\'s%' OR `property_2` LIKE '%keyword\'s%' OR `property_3` LIKE '%keyword\'s%' OR `property_4` LIKE '%keyword\'s%' OR `property_5` LIKE '%keyword\'s%' OR `property_6` LIKE '%keyword\'s%' OR `property_7` LIKE '%keyword\'s%' OR `adv_property_1` LIKE '%keyword\'s%' OR `adv_property_2` LIKE '%keyword\'s%' OR `adv_property_3` LIKE '%keyword\'s%' OR `adv_property_4` LIKE '%keyword\'s%' OR `adv_property_5` LIKE '%keyword\'s%' OR `adv_property_6` LIKE '%keyword\'s%' OR `adv_property_7` LIKE '%keyword\'s%' OR `profile_1_title` LIKE '%keyword\'s%' OR `profile_2_title` LIKE '%keyword\'s%' OR `profile_3_title` LIKE '%keyword\'s%' OR `profile_4_title` LIKE '%keyword\'s%' OR `profile_5_title` LIKE '%keyword\'s%' OR `profile_6_title` LIKE '%keyword\'s%' OR `profile_7_title` LIKE '%keyword\'s%'
OR `adv_profile_1_title` LIKE '%keyword\'s%' OR `adv_profile_2_title` LIKE '%keyword\'s%' OR `adv_profile_3_title` LIKE '%keyword\'s%'
OR `adv_profile_4_title` LIKE '%keyword\'s%' OR `adv_profile_5_title` LIKE '%keyword\'s%'
OR `adv_profile_6_title` LIKE '%keyword\'s%' OR `adv_profile_7_title` LIKE '%keyword\'s%' OR `func_1_title` LIKE '%keyword\'s%'
OR `func_2_title` LIKE '%keyword\'s%' OR `func_3_title` LIKE '%keyword\'s%' OR `func_4_title` LIKE '%keyword\'s%'
OR `func_5_title` LIKE '%keyword\'s%' OR `func_6_title` LIKE '%keyword\'s%' OR `func_7_title` LIKE '%keyword\'s%'
OR `property_1_title` LIKE '%keyword\'s%' OR `property_2_title` LIKE '%keyword\'s%' OR `property_3_title` LIKE '%keyword\'s%'
OR `property_4_title` LIKE '%keyword\'s%' OR `property_5_title` LIKE '%keyword\'s%' OR `property_6_title` LIKE '%keyword\'s%'
OR `property_7_title` LIKE '%keyword\'s%' OR `adv_property_1_title` LIKE '%keyword\'s%' OR `adv_property_2_title` LIKE '%keyword\'s%'
OR `adv_property_3_title` LIKE '%keyword\'s%' OR `adv_property_4_title` LIKE '%keyword\'s%' OR `adv_property_5_title` LIKE '%keyword\'s%'
OR `adv_property_6_title` LIKE '%keyword\'s%' OR `adv_property_7_title` LIKE '%keyword\'s%' OR `address` LIKE '%keyword\'s%'
OR `city` LIKE '%keyword\'s%' OR `state` LIKE '%keyword\'s%' OR `zipcode` LIKE '%keyword\'s%' OR `adv_function_next_1_title` LIKE '%keyword\'s%'
OR `adv_function_next_2_title` LIKE '%keyword\'s%' OR `adv_function_next_3_title` LIKE '%keyword\'s%' OR `adv_function_next_4_title` LIKE '%keyword\'s%'
OR `adv_function_next_5_title` LIKE '%keyword\'s%' OR `adv_function_next_6_title` LIKE '%keyword\'s%' OR `adv_function_next_7_title` LIKE '%keyword\'s%'
OR `ref_nr_varchar` LIKE '%keyword\'s%' OR `p_shortlist_multi` LIKE '%keyword\'s%' OR `p_multi` LIKE '%keyword\'s%'
OR `application_url` LIKE '%keyword\'s%' OR `contact_email` LIKE '%keyword\'s%' OR `q_info` LIKE '%keyword\'s%'
OR `criteria_8` LIKE '%keyword\'s%' OR `criteria_9` LIKE '%keyword\'s%' OR `criteria_10` LIKE '%keyword\'s%'
OR `criteria_11` LIKE '%keyword\'s%' OR `criteria_12` LIKE '%keyword\'s%' OR `criteria_13` LIKE '%keyword\'s%'
OR `criteria_14` LIKE '%keyword\'s%' OR `criteria_15` LIKE '%keyword\'s%' OR `criteria_16` LIKE '%keyword\'s%'
OR `criteria_17` LIKE '%keyword\'s%' OR `criteria_18` LIKE '%keyword\'s%' OR `criteria_19` LIKE '%keyword\'s%'
OR `criteria_20` LIKE '%keyword\'s%' OR `criteria_21` LIKE '%keyword\'s%' OR `criteria_22` LIKE '%keyword\'s%'
OR `criteria_23` LIKE '%keyword\'s%' OR `criteria_24` LIKE '%keyword\'s%' OR `criteria_25` LIKE '%keyword\'s%'
OR `criteria_26` LIKE '%keyword\'s%' OR `criteria_27` LIKE '%keyword\'s%' OR `criteria_28` LIKE '%keyword\'s%'
OR `criteria_29` LIKE '%keyword\'s%' OR `criteria_30` LIKE '%keyword\'s%' OR `criteria_31` LIKE '%keyword\'s%'
OR `criteria_32` LIKE '%keyword\'s%' OR `criteria_33` LIKE '%keyword\'s%' OR `criteria_34` LIKE '%keyword\'s%'
OR `criteria_35` LIKE '%keyword\'s%' OR `criteria_36` LIKE '%keyword\'s%' OR `criteria_37` LIKE '%keyword\'s%'
OR `criteria_38` LIKE '%keyword\'s%' OR `criteria_39` LIKE '%keyword\'s%' OR `criteria_40` LIKE '%keyword\'s%'
OR `criteria_41` LIKE '%keyword\'s%' OR `criteria_42` LIKE '%keyword\'s%' OR `criteria_43` LIKE '%keyword\'s%'
OR `criteria_44` LIKE '%keyword\'s%' OR `criteria_45` LIKE '%keyword\'s%' OR `criteria_46` LIKE '%keyword\'s%'
OR `criteria_47` LIKE '%keyword\'s%' OR `criteria_48` LIKE '%keyword\'s%' OR `criteria_49` LIKE '%keyword\'s%'
OR `criteria_50` LIKE '%keyword\'s%' OR `temp1` LIKE '%keyword\'s%' OR `start_assigment` LIKE '%keyword\'s%'
OR `duration_assigment` LIKE '%keyword\'s%' OR `tarif` LIKE '%keyword\'s%')
GROUP BY v.id
)
UNION
(
SELECT v.id, v.categorie AS category_id, c.categorie AS category_name, c.first_name AS category_rank, c.icon
FROM positions AS v
LEFT JOIN company_employers.categories AS c ON (c.id = v.categorie)
LEFT JOIN company_employers.vb_pubstatus AS vbps ON (vbps.id=v.vb_pubstatus)
WHERE
v.klient_id = '284'
AND v.publiceer='1'
AND (`function` LIKE '%keyword\'s%' OR `locatie` LIKE '%keyword\'s%' OR `property` LIKE '%keyword\'s%' OR `profile` LIKE '%keyword\'s%'
OR `adv_profile` LIKE '%keyword\'s%' OR `func` LIKE '%keyword\'s%' OR `functiee` LIKE '%keyword\'s%' OR `branche` LIKE '%keyword\'s%'
OR `regio` LIKE '%keyword\'s%' OR `functiegr` LIKE '%keyword\'s%' OR `itemdn` LIKE '%keyword\'s%' OR `money` LIKE '%keyword\'s%'
OR `dienstvb` LIKE '%keyword\'s%' OR `gewuren` LIKE '%keyword\'s%' OR `itemdr` LIKE '%keyword\'s%' OR `functienaam` LIKE '%keyword\'s%'
OR `wervaring` LIKE '%keyword\'s%' OR `beoordeling` LIKE '%keyword\'s%' OR `verzekering` LIKE '%keyword\'s%' OR `adv_next_category` LIKE '%keyword\'s%'
OR `opmerkingen` LIKE '%keyword\'s%' OR `trefwoorden` LIKE '%keyword\'s%' OR `kenmerken` LIKE '%keyword\'s%' OR `adv_property` LIKE '%keyword\'s%'
OR `duur` LIKE '%keyword\'s%' OR `criteria_1` LIKE '%keyword\'s%' OR `criteria_2` LIKE '%keyword\'s%'
OR `criteria_3` LIKE '%keyword\'s%' OR `criteria_4` LIKE '%keyword\'s%' OR `criteria_5` LIKE '%keyword\'s%'
OR `criteria_6` LIKE '%keyword\'s%' OR `criteria_7` LIKE '%keyword\'s%' OR `property_title` LIKE '%keyword\'s%'
OR `profile_title` LIKE '%keyword\'s%' OR `adv_profile_title` LIKE '%keyword\'s%' OR `func_title` LIKE '%keyword\'s%'
OR `adv_function_next_title` LIKE '%keyword\'s%' OR `adv_property_title` LIKE '%keyword\'s%' OR `Photo` LIKE '%keyword\'s%' OR `File` LIKE '%keyword\'s%'
OR `Thumbnail` LIKE '%keyword\'s%' OR `function_1` LIKE '%keyword\'s%' OR `function_2` LIKE '%keyword\'s%' OR `function_3` LIKE '%keyword\'s%'
OR `function_4` LIKE '%keyword\'s%' OR `function_5` LIKE '%keyword\'s%' OR `function_6` LIKE '%keyword\'s%' OR `function_7` LIKE '%keyword\'s%'
OR `profile_1` LIKE '%keyword\'s%' OR `profile_2` LIKE '%keyword\'s%' OR `profile_3` LIKE '%keyword\'s%'
OR `profile_4` LIKE '%keyword\'s%' OR `profile_5` LIKE '%keyword\'s%' OR `profile_6` LIKE '%keyword\'s%'
OR `profile_7` LIKE '%keyword\'s%' OR `adv_profile_1` LIKE '%keyword\'s%' OR `adv_profile_2` LIKE '%keyword\'s%'
OR `adv_profile_3` LIKE '%keyword\'s%' OR `adv_profile_4` LIKE '%keyword\'s%' OR `adv_profile_5` LIKE '%keyword\'s%'
OR `adv_profile_6` LIKE '%keyword\'s%' OR `adv_profile_7` LIKE '%keyword\'s%' OR `func_1` LIKE '%keyword\'s%'
OR `func_2` LIKE '%keyword\'s%' OR `func_3` LIKE '%keyword\'s%' OR `func_4` LIKE '%keyword\'s%' OR `func_5` LIKE '%keyword\'s%'
OR `func_6` LIKE '%keyword\'s%' OR `func_7` LIKE '%keyword\'s%' OR `adv_function_next_1` LIKE '%keyword\'s%' OR `adv_function_next_2` LIKE '%keyword\'s%'
OR `adv_function_next_3` LIKE '%keyword\'s%' OR `adv_function_next_4` LIKE '%keyword\'s%' OR `adv_function_next_5` LIKE '%keyword\'s%' OR `adv_function_next_6` LIKE '%keyword\'s%'
OR `adv_function_next_7` LIKE '%keyword\'s%' OR `property_1` LIKE '%keyword\'s%' OR `property_2` LIKE '%keyword\'s%' OR `property_3` LIKE '%keyword\'s%'
OR `property_4` LIKE '%keyword\'s%' OR `property_5` LIKE '%keyword\'s%' OR `property_6` LIKE '%keyword\'s%' OR `property_7` LIKE '%keyword\'s%'
OR `adv_property_1` LIKE '%keyword\'s%' OR `adv_property_2` LIKE '%keyword\'s%' OR `adv_property_3` LIKE '%keyword\'s%' OR `adv_property_4` LIKE '%keyword\'s%'
OR `adv_property_5` LIKE '%keyword\'s%' OR `adv_property_6` LIKE '%keyword\'s%' OR `adv_property_7` LIKE '%keyword\'s%' OR `profile_1_title` LIKE '%keyword\'s%'
OR `profile_2_title` LIKE '%keyword\'s%' OR `profile_3_title` LIKE '%keyword\'s%' OR `profile_4_title` LIKE '%keyword\'s%'
OR `profile_5_title` LIKE '%keyword\'s%' OR `profile_6_title` LIKE '%keyword\'s%' OR `profile_7_title` LIKE '%keyword\'s%'
OR `adv_profile_1_title` LIKE '%keyword\'s%' OR `adv_profile_2_title` LIKE '%keyword\'s%' OR `adv_profile_3_title` LIKE '%keyword\'s%'
OR `adv_profile_4_title` LIKE '%keyword\'s%' OR `adv_profile_5_title` LIKE '%keyword\'s%' OR `adv_profile_6_title` LIKE '%keyword\'s%'
OR `adv_profile_7_title` LIKE '%keyword\'s%' OR `func_1_title` LIKE '%keyword\'s%' OR `func_2_title` LIKE '%keyword\'s%'
OR `func_3_title` LIKE '%keyword\'s%' OR `func_4_title` LIKE '%keyword\'s%' OR `func_5_title` LIKE '%keyword\'s%'
OR `func_6_title` LIKE '%keyword\'s%' OR `func_7_title` LIKE '%keyword\'s%' OR `property_1_title` LIKE '%keyword\'s%'
OR `property_2_title` LIKE '%keyword\'s%' OR `property_3_title` LIKE '%keyword\'s%' OR `property_4_title` LIKE '%keyword\'s%'
OR `property_5_title` LIKE '%keyword\'s%' OR `property_6_title` LIKE '%keyword\'s%' OR `property_7_title` LIKE '%keyword\'s%'
OR `adv_property_1_title` LIKE '%keyword\'s%' OR `adv_property_2_title` LIKE '%keyword\'s%' OR `adv_property_3_title` LIKE '%keyword\'s%'
OR `adv_property_4_title` LIKE '%keyword\'s%' OR `adv_property_5_title` LIKE '%keyword\'s%' OR `adv_property_6_title` LIKE '%keyword\'s%'
OR `adv_property_7_title` LIKE '%keyword\'s%' OR `address` LIKE '%keyword\'s%' OR `city` LIKE '%keyword\'s%' OR `state` LIKE '%keyword\'s%'
OR `zipcode` LIKE '%keyword\'s%' OR `adv_function_next_1_title` LIKE '%keyword\'s%' OR `adv_function_next_2_title` LIKE '%keyword\'s%'
OR `adv_function_next_3_title` LIKE '%keyword\'s%' OR `adv_function_next_4_title` LIKE '%keyword\'s%' OR `adv_function_next_5_title` LIKE '%keyword\'s%'
OR `adv_function_next_6_title` LIKE '%keyword\'s%' OR `adv_function_next_7_title` LIKE '%keyword\'s%' OR `ref_nr_varchar` LIKE '%keyword\'s%'
OR `p_shortlist_multi` LIKE '%keyword\'s%' OR `p_multi` LIKE '%keyword\'s%' OR `application_url` LIKE '%keyword\'s%'
OR `contact_email` LIKE '%keyword\'s%' OR `q_info` LIKE '%keyword\'s%' OR `criteria_8` LIKE '%keyword\'s%'
OR `criteria_9` LIKE '%keyword\'s%' OR `criteria_10` LIKE '%keyword\'s%' OR `criteria_11` LIKE '%keyword\'s%'
OR `criteria_12` LIKE '%keyword\'s%' OR `criteria_13` LIKE '%keyword\'s%' OR `criteria_14` LIKE '%keyword\'s%'
OR `criteria_15` LIKE '%keyword\'s%' OR `criteria_16` LIKE '%keyword\'s%' OR `criteria_17` LIKE '%keyword\'s%'
OR `criteria_18` LIKE '%keyword\'s%' OR `criteria_19` LIKE '%keyword\'s%' OR `criteria_20` LIKE '%keyword\'s%'
OR `criteria_21` LIKE '%keyword\'s%' OR `criteria_22` LIKE '%keyword\'s%' OR `criteria_23` LIKE '%keyword\'s%'
OR `criteria_24` LIKE '%keyword\'s%' OR `criteria_25` LIKE '%keyword\'s%' OR `criteria_26` LIKE '%keyword\'s%'
OR `criteria_27` LIKE '%keyword\'s%' OR `criteria_28` LIKE '%keyword\'s%' OR `criteria_29` LIKE '%keyword\'s%'
OR `criteria_30` LIKE '%keyword\'s%' OR `criteria_31` LIKE '%keyword\'s%' OR `criteria_32` LIKE '%keyword\'s%'
OR `criteria_33` LIKE '%keyword\'s%' OR `criteria_34` LIKE '%keyword\'s%' OR `criteria_35` LIKE '%keyword\'s%'
OR `criteria_36` LIKE '%keyword\'s%' OR `criteria_37` LIKE '%keyword\'s%' OR `criteria_38` LIKE '%keyword\'s%'
OR `criteria_39` LIKE '%keyword\'s%' OR `criteria_40` LIKE '%keyword\'s%' OR `criteria_41` LIKE '%keyword\'s%'
OR `criteria_42` LIKE '%keyword\'s%' OR `criteria_43` LIKE '%keyword\'s%' OR `criteria_44` LIKE '%keyword\'s%'
OR `criteria_45` LIKE '%keyword\'s%' OR `criteria_46` LIKE '%keyword\'s%' OR `criteria_47` LIKE '%keyword\'s%'
OR `criteria_48` LIKE '%keyword\'s%' OR `criteria_49` LIKE '%keyword\'s%' OR `criteria_50` LIKE '%keyword\'s%'
OR `temp1` LIKE '%keyword\'s%' OR `start_assigment` LIKE '%keyword\'s%' OR `duration_assigment` LIKE '%keyword\'s%'
OR `tarif` LIKE '%keyword\'s%'
)
GROUP BY v.id
)
Přátelé, NIKDY takové dotazy nepište a NIKDY nevytvářejte databáze, kde jsou takové dotazy nutné…
Související příspěvky:
Štítky: jak nepsat SQL, MySQL, SQL, SQL dotaz

Tomáš "Inza" Jukin
Honza "zahon" Sládek
Z twitteru...
Oh my gád!!!
Tak takhle se to dělá, pokud potřebuješ koupit nový HW
Tomu člověku bych dal facku…
Ouu!!!
Vau.. Tak něco takového vidím poprvé, nu je vidět jací „programátoři“ se na světě nacházejí.
Možná bych to už dal do vtípků :D
Asi taky začnu zveřejňovat věci, které jsem viděl v cizích aplikacích :))