QtSql Dynamic Select query with multiple WHERE filters

I need to execute any select query using the same method.
So, the query my has to filter the selected data using one value or more.
Filters are stored in a map that has the column name as the key and the filtering value as its value.
So my Question is : how to add filters dynamically into Select statement?
What I tried:

1. QSqlQueryModel Class: I could create a QSqlQueryModel object and set a query to retrieve the whole table data but, I could not find any functionality in this class to filter this data :
```
   QSqlQueryModel *model = new QSqlQueryModel;
   model->setQuery("SELECT * FROM employee");
```
2. QSqlTableModel: This class is used to view table data in a qtableView,I can use this class to read table data and then filter this data like this (I have not tried this yet):
```
 QSqlTableModel *model = new QSqlTableModel
 model->setTable("employee");
 model->setEditStrategy(QSqlTableModel::OnManualSubmit);
 model->select();
 model->setFilter("colum5 > 10");
 // I can use after that data() method to retrieve filtered data. 
```
3. For Loop I thought about using for loop to add filters directly but, I would prefer a better way because I believe that QT offers a such service.

The method shall looks like this:

/**
 * @brief getData executes sql select query.
 * @param query [out] QSqlQuery query object after executing the query.
 * @param queryFilters [in] map of query filters (column-name, filter- 
   value).
 * @param table [in] table name.
 * @return 
 */
bool getData(QSqlQuery &query, std::map<std::string,QVariant> &queryFilters, 
std::string &table){
        bool status = false;
        std::string queryText = "SELECT * from " + table + " WHERE  ";
        // I should apply filters from queryFilters map here.
        return status;
}

Solution

There are several ways you could do this.

Using a for-loop with `std::map`.

Use a for loop to iterate through your key-pair values.

bool getData(QSqlQuery &query, const std::map<std::string,QVariant> &queryFilters,
             std::string &table)
{
    // ...

    std::string queryText = "SELECT * from " + table + " WHERE";
    
    for (auto it = queryFilters.begin(); it != queryFilters.end(); )
    {
        queryText += " " + it->first + "='" + it->second.toString().toStdString() + "'";
    
        it++;
        // check the iterator isn't the last iterator
        if (it != queryFilters.end())
            queryText += " AND";    // separate your "filters" using an AND
    }

    // ...      
}

Using a for-loop with `QMap`.

But heck this is Qt so why not take advantage of the QMap, QStringList, and QString QTL types.

bool getData(QSqlQuery &query, const QMap<QString, QVariant> &queryFilters,
             const QString &table)
{
    //  ...
    
    QString queryText = "SELECT * from " + table + " WHERE ";
    QStringList filters;
    foreach (const QString &filterKey, queryFilters.keys())
        filters << filterKey + "='" + queryFilters.value(filterKey).toString() + "'";
    
    queryText += filters.join(" AND ");
    
    //  ...
    
}

Note that foreach is a Qt-defined macro. See the foreach keyword.

For other QTL types you might want to be aware of, see containers.

QSqlQueryModel???

I can't tell from your question and comments whether you actually have an sql table model/view/widget in the background or whether you're using something else entirely.

I thought about using loop for this matter.But, I thought that there is a better way using some qt classes like : QSqlQueryModel

For sure, just browsing through the documentation, QSqlQueryModel doesn't have a filter feature.

But... QSqlTableModel does have this feature. The plus side is, that if you already have a QSqlQueryModel sitting somewhere, you could upgrade it to a QSqlTableModel since the latter inherits the former. But again, I don't have enough information to make judgements so I'm just leading you around in the dark here.

Hopefully, this answer sheds some light on your predicament along with the reminder of how you could ask a better question to obtain more accurate responses.

QtSql Dynamic Select query with multiple WHERE filters

Using a for-loop with std::map.

Using a for-loop with QMap.

QSqlQueryModel???

Using a for-loop with `std::map`.

Using a for-loop with `QMap`.