Sort and Filter a QSqlQueryModel

[toc align_right="yes" depth="2"]

Sort and Filter a QSqlQueryModel

QSortFilterSqlQueryModel is a QSqlQueryModel subclass that allows server-side sorting and filtering.

Feel free to download and test it.

Example usage

QSortFilterSqlQueryModel '''model = new QSortFilterSqlQueryModel(this);
model->setQuery("SELECT u.id, u.name, c.name FROM users AS u LEFT JOIN cities AS c ON (u.city_id = c.id)");
model->setFilterColumn("u.name"); // will filter by user name
model->setFilterFlags(Qt::MatchStartsWith);
model->setFilter("Iv");
model->select();

/''' executes this query:
SELECT u.id, u.name, c.name FROM users AS u LEFT JOIN cities AS c ON (u.city_id = c.id)
WHERE u.name LIKE 'Iv%'
*/

You can connect a QLineEdit's textChanged signal to the filter slot: filter is the same as setFilter, but it also call select();

// dynamic filtering on user input
QLineEdit '''search = new QLineEdit(this);
connect(search, SIGNAL (textChanged(QString)), model, SLOT (filter(QString)));

Sorting is done automatically by the view, using the sort implementation. You can also use setSort to set a sort order before select()ing.

h2. Warnings

It has only been tested in Windows using MySQL

• It won't work using setQuery(QSqlQuery). I didn't needed it so you have to reimplement it yourself.
• Works for me. May not work for you. :P

Code

QSortFilterSqlQueryModel.h

#ifndef QSORTFILTERSQLQUERYMODEL_H
#define QSORTFILTERSQLQUERYMODEL_H

#include <QSqlQueryModel>
#include <QSqlRecord>

class QSortFilterSqlQueryModel : public QSqlQueryModel
{
 Q_OBJECT

public:

explicit QSortFilterSqlQueryModel(QObject *parent = 0);

//void setQuery(const QSqlQuery &amp;query);
 void setQuery(const QString &amp; query, const QSqlDatabase &amp; db = QSqlDatabase::database() );

public slots:

void setFilterColumn (const QString &amp; column);
 void setFilterFlags (const Qt::MatchFlag flags);
 void setFilter( const QString &amp; filter );
 void filter( const QString &amp; filter );

void select();

virtual void setSort (int column, Qt::SortOrder order);
 virtual void sort (int column, Qt::SortOrder order);

private:

QSqlDatabase queryDB;
 QString queryClause;
 Qt::MatchFlag filterFlags;
 QString filterString;
 QString filterColumn;
 int sortKeyColumn;
 Qt::SortOrder sortOrder;

signals:

};

#endif // QSORTFILTERSQLQUERYMODEL_H

QSortFilterSqlQueryModel.cpp

1. include "QSortFilterSqlQueryModel.h"
2. include <QtSql>
3. include <QString>

QSortFilterSqlQueryModel::QSortFilterSqlQueryModel(QObject parent) :

QSqlQueryModel(parent)

{

}

void QSortFilterSqlQueryModel::setQuery(const QString &query, const QSqlDatabase &db) {

queryClause = query;
queryDB = db;

filterString.clear();
//filterColumn.clear();
filterFlags = Qt::MatchStartsWith;
sortKeyColumn = –1;
sortOrder = Qt::AscendingOrder;

}

void QSortFilterSqlQueryModel::select() {

if (queryClause.isEmpty() || (!queryDB.isValid()))
return;

QString query = queryClause;

if (!filterString.isEmpty() && !filterColumn.isEmpty()) {
QString whereClause;
QString esFilterString = filterString;
QString esFilterColumn = filterColumn;

if (filterFlags & Qt::MatchExactly) {
whereClause = "WHERE %1 = %2";
} else if (filterFlags & Qt::MatchStartsWith) {
whereClause = "WHERE %1 LIKE %2";
esFilterString.append("");
} else if (filterFlags & Qt::MatchEndsWith) {
whereClause = "WHERE %1 LIKE %2";
esFilterString.prepend("*");
} else if (filterFlags & Qt::MatchRegExp) {
whereClause = "WHERE %1 REGEXP %2";
} else if (filterFlags & Qt::MatchWildcard) {
whereClause = "WHERE %1 LIKE %2";
} else if (filterFlags & Qt::MatchContains) {
whereClause = "WHERE %1 LIKE 2";
esFilterString.append("");
esFilterString.prepend("");
} else { return; } // unhandled filterflag

QSqlDriver driver = queryDB.driver();
esFilterColumn = driver->escapeIdentifier(filterColumn, QSqlDriver::FieldName);
QSqlField field; field.setType(QVariant::String); field.setValue(esFilterString);
esFilterString = driver->formatValue(field);
if (filterFlags & Qt::MatchStartsWith || Qt::MatchEndsWith || Qt::MatchWildcard) {
esFilterString = esFilterString.replace("", "");
//if (filterFlags & Qt::MatchWildcard)
esFilterString = esFilterString.replace("", "");
}

//whereClause.arg(esFilterColumn).arg(esFilterString); // don't know why it doesn't work

whereClause = whereClause.replace("%1", esFilterColumn).replace("%2", esFilterString);
query.append(" " + whereClause);
}

if (sortKeyColumn >= 0) {

QString orderClause;
orderClause = "ORDER BY " + QString::number(sortKeyColumn+1) + " " + ((sortOrder == Qt::AscendingOrder) ? "ASC" : "DESC");
query.append(" " + orderClause);
}

QSqlQueryModel::setQuery(query, queryDB);

}

void QSortFilterSqlQueryModel::setSort(int column, Qt::SortOrder order) {

sortKeyColumn = column;
sortOrder = order;

}

void QSortFilterSqlQueryModel::sort(int column, Qt::SortOrder order) {

if ((sortKeyColumn != column) || (sortOrder != order)) {
setSort(column, order);
select();
}

}

void QSortFilterSqlQueryModel::setFilterColumn(const QString &column) {

filterColumn = column;

}

void QSortFilterSqlQueryModel::setFilter(const QString &filter) {

filterString = filter;

}

void QSortFilterSqlQueryModel::setFilterFlags(const Qt::MatchFlag flags) {

filterFlags = flags;

}

void QSortFilterSqlQueryModel::filter(const QString &filter) {

if (filterString != filter) {
setFilter(filter);
select();
}

}