Difference between revisions of "Sort and Filter a QSqlQueryModel"

From Qt Wiki
Jump to: navigation, search
m (bug)
(Fixed code display for cpp implementation)
 
Line 1: Line 1:
 
  
 
[[Category:HowTo]]
 
[[Category:HowTo]]
Line 36: Line 35:
  
 
== Warnings ==
 
== Warnings ==
''' It has only been tested in Windows using MySQL
+
''' 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.
 
* 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
 
* Works for me. May not work for you. :P
Line 92: Line 91:
  
 
=== QSortFilterSqlQueryModel.cpp ===
 
=== QSortFilterSqlQueryModel.cpp ===
 
+
<syntaxhighlight lang="c++">
<code>
 
 
#include "QSortFilterSqlQueryModel.h"
 
#include "QSortFilterSqlQueryModel.h"
 
#include <QtSql>
 
#include <QtSql>
 
#include <QString>
 
#include <QString>
  
QSortFilterSqlQueryModel::QSortFilterSqlQueryModel(QObject *parent) :
+
QSortFilterSqlQueryModel::QSortFilterSqlQueryModel(QObject* parent)
QSqlQueryModel(parent)
+
    : QSqlQueryModel(parent) {}
{
 
 
 
}
 
  
void QSortFilterSqlQueryModel::setQuery(const QString &query, const QSqlDatabase &db)
+
void QSortFilterSqlQueryModel::setQuery(const QString& query,
{
+
                                        const QSqlDatabase& db) {
queryClause = query;
+
    queryClause = query;
queryDB = db;
+
    queryDB = db;
  
filterString.clear();
+
    filterString.clear();
//filterColumn.clear();
+
    // filterColumn.clear();
filterFlags = Qt::MatchStartsWith;
+
    filterFlags = Qt::MatchStartsWith;
sortKeyColumn = –1;
+
    sortKeyColumn = –1;
sortOrder = Qt::AscendingOrder;
+
    sortOrder = Qt::AscendingOrder;
 
}
 
}
  
void QSortFilterSqlQueryModel::select()
+
void QSortFilterSqlQueryModel::select() {
{
+
    if (queryClause.isEmpty() || (!queryDB.isValid()))
 +
        return;
  
if (queryClause.isEmpty() || (!queryDB.isValid()))
+
    QString query = queryClause;
return;
 
  
QString query = queryClause;
+
    if (!filterString.isEmpty() && !filterColumn.isEmpty()) {
 +
        QString whereClause;
 +
        QString esFilterString = filterString;
 +
        QString esFilterColumn = filterColumn;
  
  if (!filterString.isEmpty() && !filterColumn.isEmpty()) {
+
        if (filterFlags &
QString whereClause;
+
                Qt::MatchExactly) { // totaly wrong :) Qt::MatchExacly = 0 & 0
QString esFilterString = filterString;
+
            whereClause = "WHERE %1 = %2";
QString esFilterColumn = filterColumn;
+
        } 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
  
if (filterFlags & Qt::MatchExactly) {  //totaly wrong :) Qt::MatchExacly = 0 & 0
+
        QSqlDriver* driver = queryDB.driver();
whereClause = "WHERE %1 = %2";
+
        esFilterColumn =
} else if (filterFlags & Qt::MatchStartsWith) {
+
            driver->escapeIdentifier(filterColumn, QSqlDriver::FieldName);
whereClause = "WHERE %1 LIKE %2";
+
        QSqlField field;
esFilterString.append("'''");
+
        field.setType(QVariant::String);
} else if (filterFlags & Qt::MatchEndsWith) {
+
        field.setValue(esFilterString);
whereClause = "WHERE %1 LIKE %2";
+
        esFilterString = driver->formatValue(field);
esFilterString.prepend("*");
+
        if (filterFlags & Qt::MatchStartsWith || Qt::MatchEndsWith ||
} else if (filterFlags & Qt::MatchRegExp) {
+
                Qt::MatchWildcard) {
whereClause = "WHERE %1 REGEXP %2";
+
            esFilterString = esFilterString.replace("", "");
} else if (filterFlags & Qt::MatchWildcard) {
+
            // if (filterFlags & Qt::MatchWildcard)
whereClause = "WHERE %1 LIKE %2";
+
            esFilterString = esFilterString.replace("*", "");
} else if (filterFlags & Qt::MatchContains) {
+
        }
whereClause = "WHERE %1 LIKE 2";
 
esFilterString.append("'''");
 
esFilterString.prepend("'''");
 
} else { return; } // unhandled filterflag
 
  
QSqlDriver *driver = queryDB.driver();
+
        // whereClause.arg(esFilterColumn).arg(esFilterString); // don't know why it
esFilterColumn = driver->escapeIdentifier(filterColumn, QSqlDriver::FieldName);
+
        // doesn't work
QSqlField field; field.setType(QVariant::String); field.setValue(esFilterString);
+
        whereClause =
esFilterString = driver->formatValue(field);
+
            whereClause.replace("%1", esFilterColumn).replace("%2", esFilterString);
if (filterFlags & Qt::MatchStartsWith || Qt::MatchEndsWith || Qt::MatchWildcard) {
+
        query.append(" " + whereClause);
esFilterString = esFilterString.replace("", "");
+
    }
//if (filterFlags & Qt::MatchWildcard)
 
esFilterString = esFilterString.replace("*", "");
 
}
 
  
//whereClause.arg(esFilterColumn).arg(esFilterString); // don't know why it doesn't work
+
    if (sortKeyColumn >= 0) {
whereClause = whereClause.replace("%1", esFilterColumn).replace("%2", esFilterString);
+
        QString orderClause;
query.append(" " + whereClause);
+
        orderClause = "ORDER BY " + QString::number(sortKeyColumn + 1) + " " +
}
+
                      ((sortOrder == Qt::AscendingOrder) ? "ASC" : "DESC");
 
+
        query.append(" " + orderClause);
if (sortKeyColumn >= 0) {
+
    }
QString orderClause;
 
orderClause = "ORDER BY " + QString::number(sortKeyColumn+1) + " " + ((sortOrder == Qt::AscendingOrder) ? "ASC" : "DESC");
 
query.append(" " + orderClause);
 
}
 
 
 
QSqlQueryModel::setQuery(query, queryDB);
 
  
 +
    QSqlQueryModel::setQuery(query, queryDB);
 
}
 
}
  
void QSortFilterSqlQueryModel::setSort(int column, Qt::SortOrder order)
+
void QSortFilterSqlQueryModel::setSort(int column, Qt::SortOrder order) {
{
+
    sortKeyColumn = column;
sortKeyColumn = column;
+
    sortOrder = order;
sortOrder = order;
 
 
}
 
}
  
void QSortFilterSqlQueryModel::sort(int column, Qt::SortOrder order)
+
void QSortFilterSqlQueryModel::sort(int column, Qt::SortOrder order) {
{
+
    if ((sortKeyColumn != column) || (sortOrder != order)) {
if ((sortKeyColumn != column) || (sortOrder != order)) {
+
        setSort(column, order);
setSort(column, order);
+
        select();
select();
+
    }
}
 
 
}
 
}
  
void QSortFilterSqlQueryModel::setFilterColumn(const QString &column)
+
void QSortFilterSqlQueryModel::setFilterColumn(const QString& column) {
{
+
    filterColumn = column;
filterColumn = column;
 
 
}
 
}
  
void QSortFilterSqlQueryModel::setFilter(const QString &filter) {
+
void QSortFilterSqlQueryModel::setFilter(const QString& filter) {
filterString = filter;
+
    filterString = filter;
 
}
 
}
  
void QSortFilterSqlQueryModel::setFilterFlags(const Qt::MatchFlag flags)
+
void QSortFilterSqlQueryModel::setFilterFlags(const Qt::MatchFlag flags) {
{
+
    filterFlags = flags;
filterFlags = flags;
 
 
}
 
}
  
void QSortFilterSqlQueryModel::filter(const QString &filter)
+
void QSortFilterSqlQueryModel::filter(const QString& filter) {
{
+
    if (filterString != filter) {
if (filterString != filter) {
+
        setFilter(filter);
setFilter(filter);
+
        select();
select();
+
    }
}
 
 
}
 
}
 +
</syntaxhighlight>

Latest revision as of 14:43, 6 October 2017


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

  1. ifndef QSORTFILTERSQLQUERYMODEL_H
  2. define QSORTFILTERSQLQUERYMODEL_H
  1. include <QSqlQueryModel>
  2. 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:

};

  1. 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();
    }
}