MySQL a příkaz SELECT

[Vladimir64]

Zase jsem se zasekl. Na formuláři mám 6 ComboBoxů, ve kterých jsou načteny jedinečné hodnoty ze šesti různých sloupců tabulky. Tyto ComboBoxy slouží k filtrování záznamů tabulky. Potřebuji, aby příkaz SELECT za klauzulí WHERE vyfiltroval záznamy podle hodnot v ComboBoxech. Příkazy:

 DataModule1.MySQLQuery.SQL.Clear;
 DataModule1.MySQLQuery.SQL.Add('SELECT * FROM results WHERE competition = :someValue AND class >= :someValue1 AND class <= :someValue2 AND round <= :someValue3 AND hometeam = :someValue4 AND awayteam = :someValue5');
 DataModule1.MySQLQuery.Params[0].AsString := MainForm.ComboBox1.Items[MainForm.ComboBox1.ItemIndex];
 DataModule1.MySQLQuery.Params[1].AsString := MainForm.ComboBox2.Items[MainForm.ComboBox2.ItemIndex];
 DataModule1.MySQLQuery.Params[2].AsString := MainForm.ComboBox6.Items[MainForm.ComboBox6.ItemIndex];
 DataModule1.MySQLQuery.Params[3].AsString := MainForm.ComboBox3.Items[MainForm.ComboBox3.ItemIndex];
 DataModule1.MySQLQuery.Params[4].AsString := MainForm.ComboBox4.Items[MainForm.ComboBox4.ItemIndex];
 DataModule1.MySQLQuery.Params[5].AsString := MainForm.ComboBox5.Items[MainForm.ComboBox5.ItemIndex];
 DataModule1.MySQLQuery.Open;

funguje pouze pokud jsou zadané hodnoty ve všech ComboBoxech. Já ale potřebuji filtrovat záznamy i když některé ComboBoxy zůstanou prázdné. Prostě pokud zvolím hodnotu pouze v jednom ComboBoxu, tak se vyfiltrují záznamy jen podle tohoto jediného kritéria, pokud ve dvou, tak se vyfiltrují záznamy podle dvou kritérií atd.
Nemůžu na to přijít, už jsem vyzkoušel složené SQL příkazy, podmínky vložené do SQL příkazu, podmínky při přiřazování hodnot do MySQLQuery.Params, zástupné symboly a nic nefunguje správně. Poradí někdo?

[vandrovnik]

No jestli dobře chápu požadavek, tak stačí do WHERE prostě dát jen ty podmínky, které mne zajímají, ne?
Tj. tu část WHERE bych si sestavil programově a do SQL dotazu tu podmínku zapisoval. A až pak nastavil hodnotu parametrů.

[Vladimir64]

ano, ale 6 ComboBoxů (podmínek) je celkem 53 možností. Prostě ze 6 podmínek lze vytvořit jednu šestici, šest pětic, deset čtveřic, dvacet trojic, deset dvojic a šest jednotlivých podmínek. Myslel jsem, že existuje snazší způsob než udělat například 53 "case" podmínek.
Ledaže... myslíš vytvořit řetězec podle toho který ComboBox udává podmínku k třídění? A potom celý tento string vložit za WHERE?

[vandrovnik]

Spíš něco jako:

Kód: [Vybrat]

procedure PridejPodminku(var Podminka: string; const NovaPodminka: string);
 begin
 if NovaPodminka<>'' then begin
  if Podminka<>'' then Podminka:=Podminka+'and';
  Podminka:=Podminka+'('+NovaPodminka+')';
 end;
end;

...

Podminka:='';
if ComboBox1.ItemIndex>=0 then PridejPodminku(Podminka, 'Pole1=:Hodnota1');
if ComboBox2.ItemIndex>=0 then PridejPodminku(Podminka, 'Pole2>=:Hodnota2');
...
DataModule1.MySQLQuery.SQL.Clear;
DataModule1.MySQLQuery.SQL.Add('SELECT * FROM results');
if Podminka<>'' then begin
 DataModule1.MySQLQuery.SQL.Add('WHERE '+Podminka);
 if ComboBox1.ItemIndex>=0
  then DataModule1.MySQLQuery.ParamByName('Pole1').AsString := ComboBox1.Items[ComboBox1.ItemIndex];
 ...
end;


[Jan Fiala]

Mohl bys to udělat i bez dynamického generování WHERE, ale moc optimální by ten dotaz nebyl. Něco jako:

Kód: [Vybrat]

SELECT *
  FROM results
  WHERE competition = case when :someValue ='' then competition else :someValue end  AND
      class >= case when :someValue1 = '' then class else :someValue1 end AND
      class <= case when :someValue2 = '' then class else :someValue2 end AND
      ...
Nezkousel jsem, zda to nebude zdvojovat parametry.

[Vladimir64]

díky, to je ono:

procedure TMainForm.ComboBoxChange(Sender: TObject);
begin
 Podminka := '';
 if ComboBox1.ItemIndex >= 0 then PridejPodminku(Podminka, 'competition = :Hodnota1');
 if ComboBox2.ItemIndex >= 0 then PridejPodminku(Podminka, 'class >= :Hodnota2');
 if ComboBox6.ItemIndex >= 0 then PridejPodminku(Podminka, 'class <= :Hodnota3');
 if ComboBox3.ItemIndex >= 0 then PridejPodminku(Podminka, 'round <= :Hodnota4');
 if ComboBox4.ItemIndex >= 0 then PridejPodminku(Podminka, 'hometeam = :Hodnota5');
 if ComboBox5.ItemIndex >= 0 then PridejPodminku(Podminka, 'awayteam = :Hodnota6');
 DataModule1.MySQLQuery.SQL.Clear;
 DataModule1.MySQLQuery.SQL.Add('SELECT * FROM results');
 if Podminka <> '' then begin
    DataModule1.MySQLQuery.SQL.Add('WHERE ' + Podminka);
    if ComboBox1.ItemIndex >= 0 then
        DataModule1.MySQLQuery.ParamByName('Hodnota1').AsString := ComboBox1.Items[ComboBox1.ItemIndex];
    if ComboBox2.ItemIndex >= 0 then
        DataModule1.MySQLQuery.ParamByName('Hodnota2').AsString := ComboBox2.Items[ComboBox2.ItemIndex];
    if ComboBox6.ItemIndex >= 0 then
        DataModule1.MySQLQuery.ParamByName('Hodnota3').AsString := ComboBox6.Items[ComboBox6.ItemIndex];
    if ComboBox3.ItemIndex >= 0 then
        DataModule1.MySQLQuery.ParamByName('Hodnota4').AsString := ComboBox3.Items[ComboBox3.ItemIndex];
    if ComboBox4.ItemIndex >= 0 then
        DataModule1.MySQLQuery.ParamByName('Hodnota5').AsString := ComboBox4.Items[ComboBox4.ItemIndex];
    if ComboBox5.ItemIndex >= 0 then
        DataModule1.MySQLQuery.ParamByName('Hodnota6').AsString := ComboBox5.Items[ComboBox5.ItemIndex];
 end;
 DataModule1.MySQLQuery.Open;
 end;

procedure TMainForm.PridejPodminku(var Podminka: string; const NovaPodminka: string);
 begin
 if NovaPodminka <> '' then begin
    if Podminka <> '' then Podminka := Podminka + ' and ';
    Podminka := Podminka + '(' + NovaPodminka +')';
 end;
end;

Jak jednoduché, všechna čest. Děkuji

[Vladimir64]

Mohl bys to udělat i bez dynamického generování WHERE, ale moc optimální by ten dotaz nebyl. Něco jako:

Kód: [Vybrat]

SELECT *
  FROM results
  WHERE competition = case when :someValue ='' then competition else :someValue end  AND
      class >= case when :someValue1 = '' then class else :someValue1 end AND
      class <= case when :someValue2 = '' then class else :someValue2 end AND
      ...
Nezkousel jsem, zda to nebude zdvojovat parametry.

Podobně složené SELECT příkazy jsem zkoušel nesčetněkrát. Bez výsledku (což neznamená, že to nejde). Líbí se mi ale elegantní řešení viz. výše. Přesto děkuji za reakci.

[Jan Fiala]

Když už přidáváš podmínky a skládáš select, pak to můžeš udělat v jednom kroku tak, že tam pošleš rovnou hodnotu v apostrofech, nemusíš přidávat parametry  a pak je plnit

Kód: [Vybrat]

if ComboBox1.ItemIndex >= 0 then PridejPodminku(Podminka, 'competition = ', ComboBox1.Text);
 if ComboBox2.ItemIndex >= 0 then PridejPodminku(Podminka, 'class >= ', ComboBox2.Text);
...
procedure TMainForm.PridejPodminku(var Podminka: string; const NovaPodminka, Hodnota: string);
 begin
 if NovaPodminka <> '' then begin
    if Podminka <> '' then Podminka := Podminka + ' and ';
    Podminka := Podminka + '(' + NovaPodminka + QuotedString(Hodnota) + ')';
 end;
end;


[pf1957]

Když už přidáváš podmínky a skládáš select, pak to můžeš udělat v jednom kroku tak, že tam pošleš rovnou hodnotu v apostrofech, nemusíš přidávat parametry  a pak je plnit

Taky se ta podminka da zapsat jako

Kód: SQL [Vybrat]

1. WHERE (1=1)
2.  

a pak jen pridavat podminky, treba

Kód: SQL [Vybrat]

1. AND NovaPodminka
2.  

[vandrovnik]

Když už přidáváš podmínky a skládáš select, pak to můžeš udělat v jednom kroku tak, že tam pošleš rovnou hodnotu v apostrofech, nemusíš přidávat parametry  a pak je plnit

Parametry mi připadají lepší, protože nemusí řešit, jestli se náhodou uživatel nepokouší o nějaké SQL injection, jestli nehledá apostrof, jestli nejsou na vstupu znaky, které tenhle SQL server vyhodnocuje nějak speciálně apod.

[pf1957]

Parametry mi připadají lepší, protože nemusí řešit, jestli se náhodou uživatel nepokouší o nějaké SQL injection, jestli nehledá apostrof, jestli nejsou na vstupu znaky, které tenhle SQL server vyhodnocuje nějak speciálně apod.

Lepsi to je, jen mene pohodlne, protoze vedle vlastniho textu musis vytvaret seznam hodnot pojmenovanych parametru. Vzdy jsme to tak delali - hned na zacatku nas z naivnich pokusu o escapovani vylecili cizi jmena s apostrofem uvnitr v jednom projektu.

[Jan Fiala]

Parametry mi připadají lepší, protože nemusí řešit, jestli se náhodou uživatel nepokouší o nějaké SQL injection, jestli nehledá apostrof, jestli nejsou na vstupu znaky, které tenhle SQL server vyhodnocuje nějak speciálně apod.

A proto tam pouzivam na texty QuotedStr(), který by případné apostrofy zdvojil

[Vladimir64]

napadlo mě ještě jedno řešení. Nezkoušel jsem ho, ale myslím, že by fungovalo. Místo jedné procedury ComboBoxChange, obsloužit tuto proceduru u každého ComboBoxu. Teda ComboBox1Change .. ComboBox6Change, a v každé přidat podmínku.

[Stanislav Hruška]

Píšeš o pridaní podmienky. A keď tam už tá podmienka bude? Len zmením jej hodnotu. Bude to dobre?

[Vladimir64]

no, nezkoušel jsem to. Použil jsem řešení viz výše. To mě nenapadlo, že běžně nastane situace, kdy už byla u daného ComboBoxu podmínka vložena a je potřeba ji jen změnit. Obávám se, že jen tak jednoduše ta podmínka změnit nepůjde.