Sort and Filter a QSqlQueryModel: Difference between revisions
AutoSpider (talk | contribs) (Convert ExpressionEngine section headers) |
AutoSpider (talk | contribs) (Decode HTML entity names) |
||
Line 61: | Line 61: | ||
explicit QSortFilterSqlQueryModel(QObject *parent = 0); | explicit QSortFilterSqlQueryModel(QObject *parent = 0); | ||
//void setQuery(const QSqlQuery & | //void setQuery(const QSqlQuery &query); | ||
void setQuery(const QString & | void setQuery(const QString & query, const QSqlDatabase & db = QSqlDatabase::database() ); | ||
public slots: | public slots: | ||
void setFilterColumn (const QString & | void setFilterColumn (const QString & column); | ||
void setFilterFlags (const Qt::MatchFlag flags); | void setFilterFlags (const Qt::MatchFlag flags); | ||
void setFilter( const QString & | void setFilter( const QString & filter ); | ||
void filter( const QString & | void filter( const QString & filter ); | ||
void select(); | void select(); | ||
Line 106: | Line 106: | ||
} | } | ||
void QSortFilterSqlQueryModel::setQuery(const QString & | void QSortFilterSqlQueryModel::setQuery(const QString &query, const QSqlDatabase &db) | ||
{ | { | ||
queryClause = query; | queryClause = query; | ||
Line 126: | Line 126: | ||
QString query = queryClause; | QString query = queryClause; | ||
if (!filterString.isEmpty() & | if (!filterString.isEmpty() && !filterColumn.isEmpty()) { | ||
QString whereClause; | QString whereClause; | ||
QString esFilterString = filterString; | QString esFilterString = filterString; | ||
QString esFilterColumn = filterColumn; | QString esFilterColumn = filterColumn; | ||
if (filterFlags & | if (filterFlags & Qt::MatchExactly) { | ||
whereClause = "WHERE %1 = %2"; | whereClause = "WHERE %1 = %2"; | ||
} else if (filterFlags & | } else if (filterFlags & Qt::MatchStartsWith) { | ||
whereClause = "WHERE %1 LIKE %2"; | whereClause = "WHERE %1 LIKE %2"; | ||
esFilterString.append("'''"); | esFilterString.append("'''"); | ||
} else if (filterFlags & | } else if (filterFlags & Qt::MatchEndsWith) { | ||
whereClause = "WHERE %1 LIKE %2"; | whereClause = "WHERE %1 LIKE %2"; | ||
esFilterString.prepend("*"); | esFilterString.prepend("*"); | ||
} else if (filterFlags & | } else if (filterFlags & Qt::MatchRegExp) { | ||
whereClause = "WHERE %1 REGEXP %2"; | whereClause = "WHERE %1 REGEXP %2"; | ||
} else if (filterFlags & | } else if (filterFlags & Qt::MatchWildcard) { | ||
whereClause = "WHERE %1 LIKE %2"; | whereClause = "WHERE %1 LIKE %2"; | ||
} else if (filterFlags & | } else if (filterFlags & Qt::MatchContains) { | ||
whereClause = "WHERE %1 LIKE 2"; | whereClause = "WHERE %1 LIKE 2"; | ||
esFilterString.append("'''"); | esFilterString.append("'''"); | ||
Line 153: | Line 153: | ||
QSqlField field; field.setType(QVariant::String); field.setValue(esFilterString); | QSqlField field; field.setType(QVariant::String); field.setValue(esFilterString); | ||
esFilterString = driver->formatValue(field); | esFilterString = driver->formatValue(field); | ||
if (filterFlags & | if (filterFlags & Qt::MatchStartsWith || Qt::MatchEndsWith || Qt::MatchWildcard) { | ||
esFilterString = esFilterString.replace("", ""); | esFilterString = esFilterString.replace("", ""); | ||
//if (filterFlags & | //if (filterFlags & Qt::MatchWildcard) | ||
esFilterString = esFilterString.replace("'''", ""); | esFilterString = esFilterString.replace("'''", ""); | ||
} | } | ||
Line 188: | Line 188: | ||
} | } | ||
void QSortFilterSqlQueryModel::setFilterColumn(const QString & | void QSortFilterSqlQueryModel::setFilterColumn(const QString &column) | ||
{ | { | ||
filterColumn = column; | filterColumn = column; | ||
} | } | ||
void QSortFilterSqlQueryModel::setFilter(const QString & | void QSortFilterSqlQueryModel::setFilter(const QString &filter) { | ||
filterString = filter; | filterString = filter; | ||
} | } | ||
Line 202: | Line 202: | ||
} | } | ||
void QSortFilterSqlQueryModel::filter(const QString & | void QSortFilterSqlQueryModel::filter(const QString &filter) | ||
{ | { | ||
if (filterString != filter) { | if (filterString != filter) { |
Revision as of 17:50, 12 March 2015
This article may require cleanup to meet the Qt Wiki's quality standards. Reason: Auto-imported from ExpressionEngine. Please improve this article if you can. Remove the {{cleanup}} tag and add this page to Updated pages list after it's clean. |
[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.
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) {
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();
}
}