Qt wiki will be updated on October 12th 2023 starting at 11:30 AM (EEST) and the maintenance will last around 2-3 hours. During the maintenance the site will be unavailable.
Sort and Filter a QSqlQueryModel: Difference between revisions
Jump to navigation
Jump to search
(Fixed code display for cpp implementation) |
|||
(2 intermediate revisions by 2 users not shown) | |||
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++"> | |||
< | |||
#include "QSortFilterSqlQueryModel.h" | #include "QSortFilterSqlQueryModel.h" | ||
#include <QtSql> | #include <QtSql> | ||
#include <QString> | #include <QString> | ||
QSortFilterSqlQueryModel::QSortFilterSqlQueryModel(QObject *parent) : | QSortFilterSqlQueryModel::QSortFilterSqlQueryModel(QObject* parent) | ||
: QSqlQueryModel(parent) {} | |||
{ | |||
} | |||
void QSortFilterSqlQueryModel::setQuery(const QString &query, const QSqlDatabase &db) | 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() | 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 ( | 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); | |||
if (sortKeyColumn >= 0) { | } | ||
QSqlQueryModel::setQuery(query, queryDB); | |||
} | } | ||
void QSortFilterSqlQueryModel::setSort(int column, Qt::SortOrder order) | void QSortFilterSqlQueryModel::setSort(int column, Qt::SortOrder order) { | ||
{ | sortKeyColumn = column; | ||
sortOrder = order; | |||
} | } | ||
void QSortFilterSqlQueryModel::sort(int column, Qt::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) | void QSortFilterSqlQueryModel::setFilterColumn(const QString& column) { | ||
{ | filterColumn = column; | ||
} | } | ||
void QSortFilterSqlQueryModel::setFilter(const QString &filter) { | void QSortFilterSqlQueryModel::setFilter(const QString& filter) { | ||
filterString = filter; | |||
} | } | ||
void QSortFilterSqlQueryModel::setFilterFlags(const Qt::MatchFlag flags) | void QSortFilterSqlQueryModel::setFilterFlags(const Qt::MatchFlag flags) { | ||
{ | filterFlags = flags; | ||
} | } | ||
void QSortFilterSqlQueryModel::filter(const QString &filter) | void QSortFilterSqlQueryModel::filter(const QString& filter) { | ||
{ | if (filterString != filter) { | ||
setFilter(filter); | |||
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
#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();
}
}