Sort and Filter a QSqlQueryModel: Difference between revisions
No edit summary |
No edit summary |
||
Line 1: | Line 1: | ||
=Sort and Filter a QSqlQueryModel= | [[Category:HowTo]]<br />[[Category:Developing with Qt]]<br />[toc align_right="yes&quot; depth="2&quot;] | ||
= Sort and Filter a QSqlQueryModel = | |||
QSortFilterSqlQueryModel is a QSqlQueryModel subclass that allows server-side sorting and filtering. | QSortFilterSqlQueryModel is a QSqlQueryModel subclass that allows server-side sorting and filtering. | ||
Line 5: | Line 7: | ||
Feel free to download and test it. | Feel free to download and test it. | ||
==Example usage== | == Example usage == | ||
<code><br />QSortFilterSqlQueryModel '''model = new QSortFilterSqlQueryModel(this);<br />model->setQuery("SELECT u.id, u.name, c.name FROM users AS u LEFT JOIN cities AS c ON (u.city_id = c.id)");<br />model->setFilterColumn("u.name&quot;); // will filter by user name<br />model->setFilterFlags(Qt::MatchStartsWith);<br />model->setFilter("Iv&quot;);<br />model->select(); | |||
<br />/''' executes this query:<br />SELECT u.id, u.name, c.name FROM users AS u LEFT JOIN cities AS c ON (u.city_id = c.id)<br />WHERE u.name LIKE 'Iv%'<br />*/<br /></code> | |||
You can connect a QLineEdit's textChanged signal to the filter slot:<br />filter is the same as setFilter, but it also call select();<br /><code><br />// dynamic filtering on user input<br />QLineEdit '''search = new QLineEdit(this);<br />connect(search, SIGNAL (textChanged(QString)), model, SLOT (filter(QString)));<br /></code> | |||
<br />Sorting is done automatically by the view, using the sort implementation. You can also use setSort to set a sort order before select()ing. | |||
<br />h2. Warnings | |||
<br />''' It has only been tested in Windows using MySQL<br />* It won't work using setQuery(QSqlQuery). I didn't needed it so you have to reimplement it yourself.<br />* Works for me. May not work for you. :P | |||
== Code == | |||
=== QSortFilterSqlQueryModel.h === | |||
<code><br />#ifndef QSORTFILTERSQLQUERYMODEL_H<br />#define QSORTFILTERSQLQUERYMODEL_H | |||
#include <QSqlQueryModel&gt;<br />#include <QSqlRecord&gt; | |||
class QSortFilterSqlQueryModel : public QSqlQueryModel<br />{<br /> Q_OBJECT | |||
public: | |||
explicit QSortFilterSqlQueryModel(QObject *parent = 0); | |||
//void setQuery(const QSqlQuery &query);<br /> void setQuery(const QString & query, const QSqlDatabase & db = QSqlDatabase::database() ); | |||
public slots: | |||
void setFilterColumn (const QString & column);<br /> void setFilterFlags (const Qt::MatchFlag flags);<br /> void setFilter( const QString & filter );<br /> void filter( const QString & filter ); | |||
void select(); | |||
virtual void setSort (int column, Qt::SortOrder order);<br /> virtual void sort (int column, Qt::SortOrder order); | |||
private: | |||
QSqlDatabase queryDB;<br /> QString queryClause;<br /> Qt::MatchFlag filterFlags;<br /> QString filterString;<br /> QString filterColumn;<br /> int sortKeyColumn;<br /> Qt::SortOrder sortOrder; | |||
signals: | |||
}; | |||
#endif // QSORTFILTERSQLQUERYMODEL_H<br /></code> | |||
=== QSortFilterSqlQueryModel.cpp === | |||
<code><br />#include "QSortFilterSqlQueryModel.h&quot;<br />#include <QtSql&gt;<br />#include <QString&gt; | |||
QSortFilterSqlQueryModel::QSortFilterSqlQueryModel(QObject '''parent) :<br /> QSqlQueryModel(parent)<br />{ | |||
<br />} | |||
<br />void QSortFilterSqlQueryModel::setQuery(const QString &query, const QSqlDatabase &db)<br />{<br /> queryClause = query;<br /> queryDB = db; | |||
<br /> filterString.clear();<br /> //filterColumn.clear();<br /> filterFlags = Qt::MatchStartsWith;<br /> sortKeyColumn = –1;<br /> sortOrder = Qt::AscendingOrder;<br />} | |||
<br />void QSortFilterSqlQueryModel::select()<br />{ | |||
<br /> if (queryClause.isEmpty() || (!queryDB.isValid()))<br /> return; | |||
<br /> QString query = queryClause; | |||
<br /> if (!filterString.isEmpty() && !filterColumn.isEmpty()) {<br /> QString whereClause;<br /> QString esFilterString = filterString;<br /> QString esFilterColumn = filterColumn; | |||
<br /> if (filterFlags & Qt::MatchExactly) {<br /> whereClause = "WHERE %1 = %2&quot;;<br /> } else if (filterFlags & Qt::MatchStartsWith) {<br /> whereClause = "WHERE %1 LIKE %2&quot;;<br /> esFilterString.append("'''");<br /> } else if (filterFlags & Qt::MatchEndsWith) {<br /> whereClause = "WHERE %1 LIKE %2&quot;;<br /> esFilterString.prepend("*");<br /> } else if (filterFlags & Qt::MatchRegExp) {<br /> whereClause = "WHERE %1 REGEXP %2&quot;;<br /> } else if (filterFlags & Qt::MatchWildcard) {<br /> whereClause = "WHERE %1 LIKE %2&quot;;<br /> } else if (filterFlags & Qt::MatchContains) {<br /> whereClause = "WHERE %1 LIKE 2&quot;;<br /> esFilterString.append("'''");<br /> esFilterString.prepend("'''");<br /> } else { return; } // unhandled filterflag | |||
<br /> QSqlDriver '''driver = queryDB.driver();<br /> esFilterColumn = driver->escapeIdentifier(filterColumn, QSqlDriver::FieldName);<br /> QSqlField field; field.setType(QVariant::String); field.setValue(esFilterString);<br /> esFilterString = driver->formatValue(field);<br /> if (filterFlags & Qt::MatchStartsWith || Qt::MatchEndsWith || Qt::MatchWildcard) {<br /> esFilterString = esFilterString.replace("", "");<br /> //if (filterFlags & Qt::MatchWildcard)<br /> esFilterString = esFilterString.replace("'''", "");<br /> } | |||
//whereClause.arg(esFilterColumn).arg(esFilterString); // don't know why it doesn't work<br /> whereClause = whereClause.replace("%1&quot;, esFilterColumn).replace("%2&quot;, esFilterString);<br /> query.append(" " + whereClause);<br /> } | |||
if (sortKeyColumn >= 0) {<br /> QString orderClause;<br /> orderClause = "ORDER BY " + QString::number(sortKeyColumn+1) + " " + ((sortOrder == Qt::AscendingOrder) ? "ASC&quot; : "DESC&quot;);<br /> query.append(" " + orderClause);<br /> } | |||
QSqlQueryModel::setQuery(query, queryDB); | |||
} | |||
void QSortFilterSqlQueryModel::setSort(int column, Qt::SortOrder order)<br />{<br /> sortKeyColumn = column;<br /> sortOrder = order;<br />} | |||
== | void QSortFilterSqlQueryModel::sort(int column, Qt::SortOrder order)<br />{<br /> if ((sortKeyColumn != column) || (sortOrder != order)) {<br /> setSort(column, order);<br /> select();<br /> }<br />} | ||
void QSortFilterSqlQueryModel::setFilterColumn(const QString &column)<br />{<br /> filterColumn = column;<br />} | |||
void QSortFilterSqlQueryModel::setFilter(const QString &filter) {<br /> filterString = filter;<br />} | |||
void QSortFilterSqlQueryModel::setFilterFlags(const Qt::MatchFlag flags)<br />{<br /> filterFlags = flags;<br />} | |||
void QSortFilterSqlQueryModel::filter(const QString &filter)<br />{<br /> if (filterString != filter) {<br /> setFilter(filter);<br /> select();<br /> }<br />} | |||
Revision as of 09:36, 24 February 2015
[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
<br />QSortFilterSqlQueryModel '''model = new QSortFilterSqlQueryModel(this);<br />model->setQuery("SELECT u.id, u.name, c.name FROM users AS u LEFT JOIN cities AS c ON (u.city_id = c.id)");<br />model->setFilterColumn("u.name&quot;); // will filter by user name<br />model->setFilterFlags(Qt::MatchStartsWith);<br />model->setFilter("Iv&quot;);<br />model->select();
<br />/''' executes this query:<br />SELECT u.id, u.name, c.name FROM users AS u LEFT JOIN cities AS c ON (u.city_id = c.id)<br />WHERE u.name LIKE 'Iv%'<br />*/<br />
You can connect a QLineEdit's textChanged signal to the filter slot:
filter is the same as setFilter, but it also call select();
<br />// dynamic filtering on user input<br />QLineEdit '''search = new QLineEdit(this);<br />connect(search, SIGNAL (textChanged(QString)), model, SLOT (filter(QString)));<br />
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
<br />#ifndef QSORTFILTERSQLQUERYMODEL_H<br />#define QSORTFILTERSQLQUERYMODEL_H
#include <QSqlQueryModel&gt;<br />#include <QSqlRecord&gt;
class QSortFilterSqlQueryModel : public QSqlQueryModel<br />{<br /> Q_OBJECT
public:
explicit QSortFilterSqlQueryModel(QObject *parent = 0);
//void setQuery(const QSqlQuery &query);<br /> void setQuery(const QString & query, const QSqlDatabase & db = QSqlDatabase::database() );
public slots:
void setFilterColumn (const QString & column);<br /> void setFilterFlags (const Qt::MatchFlag flags);<br /> void setFilter( const QString & filter );<br /> void filter( const QString & filter );
void select();
virtual void setSort (int column, Qt::SortOrder order);<br /> virtual void sort (int column, Qt::SortOrder order);
private:
QSqlDatabase queryDB;<br /> QString queryClause;<br /> Qt::MatchFlag filterFlags;<br /> QString filterString;<br /> QString filterColumn;<br /> int sortKeyColumn;<br /> Qt::SortOrder sortOrder;
signals:
};
#endif // QSORTFILTERSQLQUERYMODEL_H<br />
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();
}
}