Sort and Filter a QSqlQueryModel

From Qt Wiki
Revision as of 14:43, 6 October 2017 by NetZwerg (talk | contribs) (Fixed code display for cpp implementation)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.


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.

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 &query);
 void setQuery(const QString & query, const QSqlDatabase & db = QSqlDatabase::database() );

public slots:

void setFilterColumn (const QString & column);
 void setFilterFlags (const Qt::MatchFlag flags);
 void setFilter( const QString & filter );
 void filter( const QString & 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

#include "QSortFilterSqlQueryModel.h"
#include <QtSql>
#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) {  // totaly wrong :) Qt::MatchExacly = 0 & 0
            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();
    }
}