QSqlTablemodel->setFilter()

[codeman]

Hello friends,

in my app I have three comboboxes, which I use as filter for my model.

QString str1 = combo1->currentText();
    QString str2 = combo2->currentText();
    QString str3 = combo3->currentText();
 
    QString filter1 = "field1='"+str1+"'";
    QString filter2 = "field2='"+str2+"'";
    QString filter3 = "field3= convert (datetime,'"+str3+"',120)";
 
 
    if (str1!="" && str2 != "" && str3 != "")//111
        model->setFilter( filter1 +" and "+ filter2 +" and "+filter3);
    else if (str1=="" && str2 != "" && str3 != "")//011
        model->setFilter( filter2 +" and "+filter3);
    else if (str1=="" && str2 != "" && str3 == "")//010
        model->setFilter( filter2 );
    else if (str1=="" && str2 == "" && str3 != "")//001
        model->setFilter( filter3 );
    else if (str1!="" && str2 != "" && str3 == "")//110
        model->setFilter( filter1 +" and "+ filter2 );
    else if (str1!="" && str2 == "" && str3 != "")//101
        model->setFilter( filter1 +" and "+ filter3 );
    else if (str1!="" && str2 == "" && str3 == "")//100
        model->setFilter( filter1 );
    else
    {
        model->setTable(tableName);
        model->select();
    }

QString str1 = combo1->currentText();
    QString str2 = combo2->currentText();
    QString str3 = combo3->currentText();

    QString filter1 = "field1='"+str1+"'";
    QString filter2 = "field2='"+str2+"'";
    QString filter3 = "field3= convert (datetime,'"+str3+"',120)";


    if (str1!="" && str2 != "" && str3 != "")//111
        model->setFilter( filter1 +" and "+ filter2 +" and "+filter3);
    else if (str1=="" && str2 != "" && str3 != "")//011
        model->setFilter( filter2 +" and "+filter3);
    else if (str1=="" && str2 != "" && str3 == "")//010
        model->setFilter( filter2 );
    else if (str1=="" && str2 == "" && str3 != "")//001
        model->setFilter( filter3 );
    else if (str1!="" && str2 != "" && str3 == "")//110
        model->setFilter( filter1 +" and "+ filter2 );
    else if (str1!="" && str2 == "" && str3 != "")//101
        model->setFilter( filter1 +" and "+ filter3 );
    else if (str1!="" && str2 == "" && str3 == "")//100
        model->setFilter( filter1 );
    else
    {
        model->setTable(tableName);
        model->select();
    }

To copy to clipboard, switch view to plain text mode 

As you can see I also give my combo a <""> for no filter, but I have always to check because a when I set a filter and when a filter has a "" it does not appear in my table.

So when I have only 3 filter I have to check 2^3 choises that is 8 or four filter with 2^4=16 that is much coding. Is there a elegant way to setting the filters???

[mazurekwrc]

QString text;
if( str1.length() != 0 )
  text.append( filter1 );
 
if( str2.length() != 0 )
{
  if( text.length() != 0 )
    text.append( " and " );
  text.append( filter2 );
}
 
if( str3.length() != 0 )
{
  if( text.length() != 0 )
    text.append( " and " );
  text.append( filter3 );
}
 
model->setFilter( text );

QString text;
if( str1.length() != 0 )
  text.append( filter1 );

if( str2.length() != 0 )
{
  if( text.length() != 0 )
    text.append( " and " );
  text.append( filter2 );
}

if( str3.length() != 0 )
{
  if( text.length() != 0 )
    text.append( " and " );
  text.append( filter3 );
}

model->setFilter( text );

To copy to clipboard, switch view to plain text mode 

[codeman]

I am surprised...... ok thank you very much cool solution

[Lykurg]

or use a QStringList like

if (!myStringXYZ.isEmpty())
  myStringList.append(myStringXYZ);

if (!myStringXYZ.isEmpty())
  myStringList.append(myStringXYZ);

To copy to clipboard, switch view to plain text mode 

and then get the full filter via

myStringList.join(" and ");

myStringList.join(" and ");

To copy to clipboard, switch view to plain text mode 

this will produce a more readable code if you have more filters to check.

[Lykurg]

QString filter1 = "field1='"+str1+"'";

QString filter1 = "field1='"+str1+"'";

To copy to clipboard, switch view to plain text mode 

Is that for a sql query? Then better use QSqlQuery::prepare() with bindValue() to avoid sql injection.