0

I'm trying to get all other SELECTs from an SQL query by preg_match_all function.

Regex

/\((\sSELECT\s((?>[^()]+)|(?R))*)\)/gi

SQL

SELECT SQL_CALC_FOUND_ROWS ukoly.*, kategorie.kat_seznam, IF(DATE_FORMAT(ukoly.termin, '%d.%m.%Y')='00.00.0000','-',DATE_FORMAT(ukoly.termin, '%d.%m.%Y')) AS dat_termin, DATEDIFF(ukoly.termin,CURDATE()) AS rozdil, users_resitel.prijmeni AS res_prijmeni, users_resitel.jmeno AS res_jmeno, users_zadavatel.prijmeni AS zad_prijmeni, users_zadavatel.jmeno AS zad_jmeno, IF(COALESCE(pocty_podukolu.celkem,0)=0,0,1) AS rodic, pocty_podukolu.celkem AS podukoly_celkem, pocty_podukolu.dnes_expir AS podukoly_expir, ukoly.id AS _rodic_id, EXISTS ( SELECT * FROM ukoly LEFT JOIN users AS users_resitel ON users_resitel.cislo = ukoly.resitel_id LEFT JOIN users AS users_zadavatel ON users_zadavatel.cislo = ukoly.vytvoril WHERE nadrazeny_id = _rodic_id ) AS podukoly_dostupne FROM ukoly LEFT JOIN users AS users_resitel ON users_resitel.cislo = ukoly.resitel_id LEFT JOIN users AS users_zadavatel ON users_zadavatel.cislo = ukoly.vytvoril LEFT JOIN ( SELECT COUNT(id) AS celkem, COUNT(IF((DATEDIFF(termin, CURDATE()) <= 0), 1, NULL)) AS dnes_expir, nadrazeny_id FROM ukoly WHERE stav = 1 AND ( soukromy = 0 OR vytvoril = 1 OR resitel_id = 1 ) GROUP BY nadrazeny_id ) AS pocty_podukolu ON pocty_podukolu.nadrazeny_id = ukoly.id LEFT JOIN ( SELECT ukoly_kategorie_ukoly.ukol_id, GROUP_CONCAT(DISTINCT ukoly_kategorie.nazev ORDER BY ukoly_kategorie_ukoly.kategorie_id SEPARATOR ';') AS kat_seznam FROM ukoly_kategorie_ukoly LEFT JOIN ukoly_kategorie ON ukoly_kategorie.id_kategorie = ukoly_kategorie_ukoly.kategorie_id WHERE ukoly_kategorie_ukoly.stav = 1 GROUP BY ukoly_kategorie_ukoly.ukol_id ) AS kategorie ON kategorie.ukol_id = ukoly.id WHERE ukoly.stav = 1 AND ( ukoly.soukromy = 0 OR ukoly.vytvoril = 1 OR ukoly.resitel_id = 1 ) AND ( ukoly.nadrazeny_id = 0 OR ( ukoly.nadrazeny_id != 0 AND ukoly.nadrazeny_id NOT IN ( SELECT ukoly.id FROM ukoly LEFT JOIN users AS users_resitel ON users_resitel.cislo = ukoly.resitel_id LEFT JOIN users AS users_zadavatel ON users_zadavatel.cislo = ukoly.vytvoril WHERE ukoly.stav = 1 AND ( ukoly.soukromy = 0 OR ukoly.vytvoril = 1 OR ukoly.resitel_id = 1 ))))

Demo: Click

At the moment I managed to get at least one record, but I need to get them all and I really don't know what to do...

As a result, I need to get this:

1. SELECT * FROM ukoly LEFT JOIN users AS users_resitel ON users_resitel.cislo = ukoly.resitel_id LEFT JOIN users AS users_zadavatel ON users_zadavatel.cislo = ukoly.vytvoril WHERE nadrazeny_id = _rodic_id
2. SELECT COUNT(id) AS celkem, COUNT(IF((DATEDIFF(termin, CURDATE()) <= 0), 1, NULL)) AS dnes_expir, nadrazeny_id FROM ukoly WHERE stav = 1 AND ( soukromy = 0 OR vytvoril = 1 OR resitel_id = 1 ) GROUP BY nadrazeny_id
3. SELECT ukoly_kategorie_ukoly.ukol_id, GROUP_CONCAT(DISTINCT ukoly_kategorie.nazev ORDER BY ukoly_kategorie_ukoly.kategorie_id SEPARATOR ';') AS kat_seznam FROM ukoly_kategorie_ukoly LEFT JOIN ukoly_kategorie ON ukoly_kategorie.id_kategorie = ukoly_kategorie_ukoly.kategorie_id WHERE ukoly_kategorie_ukoly.stav = 1 GROUP BY ukoly_kategorie_ukoly.ukol_id
4. SELECT ukoly.id FROM ukoly LEFT JOIN users AS users_resitel ON users_resitel.cislo = ukoly.resitel_id LEFT JOIN users AS users_zadavatel ON users_zadavatel.cislo = ukoly.vytvoril WHERE ukoly.stav = 1 AND ( ukoly.soukromy = 0 OR ukoly.vytvoril = 1 OR ukoly.resitel_id = 1 )

I will be grateful for any help, thank you.

Improve this question
3
  • \((\sSELECT\s((?>[^()]+)?(\(((?>[^()]+)|(?-2))*\)))*(?>[^()]+)?)\) Commented Oct 28, 2020 at 13:34
  • 1
    omg, thx so much! You saved a lot of my time, really thank you. Commented Oct 28, 2020 at 14:21
  • 1
    It will be easy to break, do not use regex to parse SQL. Besides, the regex above is too cumbersome, all it wants to say is \((\sSELECT\s([^()]*(\((?:[^()]++|(?2))*\)))*[^()]*)\) (demo) Commented Oct 28, 2020 at 15:47
Related questions
279
JOIN two SELECT statement results
1
Select a unique name from Users that have more than 5 titles. SQL
2
SPARK SQL select from group by select result
Load 6 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.