Talk:Sort and Filter a QSqlQueryModel: Difference between revisions
Jump to navigation
Jump to search
(Сортировка моделей, сформированных запросами типа SELECT field1, field2 FROM table1 WHERE field1=?) |
(Added change for cpp implementation but could not save it ...) |
||
(One intermediate revision by one other user not shown) | |||
Line 1: | Line 1: | ||
Реализация класса QSortFilterSqlQueryModel сортируемой модели не позволяет сортировать таблицы полученные с помощью запросов типа SELECT field1, field2 FROM table1 WHERE field1=? | Got always permission denied or aborted by hook ... | ||
Change cpp implementation to code block: | |||
=== QSortFilterSqlQueryModel.cpp === | |||
<syntaxhighlight lang="c++"> | |||
#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(); | |||
} | |||
} | |||
</syntaxhighlight> | |||
[[User:NetZwerg|NetZwerg]] ([[User talk:NetZwerg|talk]]) | |||
===== Приведенная ниже реализация позволяет: редактировать/устанавливать verticalHeader, сортировать модели от любых запросов. ===== | |||
Реализация класса '''QSortFilterSqlQueryModel''' сортируемой модели '''не позволяет сортировать таблицы полученные с помощью запросов типа SELECT field1, field2 FROM table1 WHERE field1=?''' | |||
<code> | |||
#ifndef QSqlQueryModelEx_h | #ifndef QSqlQueryModelEx_h | ||
#define QSqlQueryModelEx_h | #define QSqlQueryModelEx_h | ||
Line 117: | Line 246: | ||
#endif // QSqlQueryModelEx_h | #endif // QSqlQueryModelEx_h | ||
</code> | |||
Синельникова Т.И. |
Latest revision as of 14:42, 6 October 2017
Got always permission denied or aborted by hook ...
Change cpp implementation to code block:
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();
}
}
Приведенная ниже реализация позволяет: редактировать/устанавливать verticalHeader, сортировать модели от любых запросов.
Реализация класса QSortFilterSqlQueryModel сортируемой модели не позволяет сортировать таблицы полученные с помощью запросов типа SELECT field1, field2 FROM table1 WHERE field1=?
#ifndef QSqlQueryModelEx_h
#define QSqlQueryModelEx_h
#include <QSqlQueryModel>
#include <QSqlQuery>
#include <QMap>
class QSqlQueryModelEx : public QSqlQueryModel
{
Q_OBJECT
public:
QMap<int, QString> vertical_header;
QSqlQueryModelEx(QObject *parent = 0):
QSqlQueryModel(parent) { }
QVariant QSqlQueryModelEx::headerData(int section, Qt::Orientation orientation, int role) const {
if(orientation == Qt::Vertical && role == Qt::DisplayRole) {
return QVariant(vertical_header[section]);
} else {
return QSqlQueryModel::headerData(section, orientation, role);
}
}
bool setHeaderData(int section, Qt::Orientation orientation, const QVariant &value, int role = Qt::EditRole) {
if(QSqlQueryModel::setHeaderData(section, orientation, value, role)) {
emit QSqlQueryModel::headerDataChanged(orientation, section, section);
return true;
} else {
if (orientation == Qt::Vertical) {
vertical_header[section] = value.toString();
emit QSqlQueryModel::headerDataChanged(orientation, section, section);
}
return false;
}
}
void setQuery(const QSqlQuery & query) {
__query__ = query;
str_filter.clear();
flag_filter = Qt::MatchStartsWith;
sort_column = -1;
sort_order = Qt::AscendingOrder;
return QSqlQueryModel::setQuery(query);
}
public slots:
void select() {
if (__query__.lastQuery().isEmpty()) return;
QString str_query = __query__.lastQuery();
if (!str_filter.isEmpty() && !column_filter.isEmpty()) {
//...filtration
//...filtration
//...filtration
}
if (sort_column >= 0) {
QString str_order;
if (str_query.contains("order by ")) {
str_query = str_query.left(str_query.indexOf("order by"));
} else {
str_order += " ";
}
str_order += "ORDER BY " + QString::number(sort_column+1) + " " + ((sort_order == Qt::AscendingOrder) ? "ASC" : "DESC");
str_query.append(str_order);
}
QSqlQuery temp = QSqlQuery(str_query);
if (__query__.boundValues().size()) {
foreach (const QVariant& value, __query__.boundValues()) {
temp.addBindValue(value);
}
temp.exec();
}
QSqlQueryModel::setQuery(temp);
}
void setSort(int column, Qt::SortOrder order) {
sort_column = column;
sort_order = order;
}
void sort(int column, Qt::SortOrder order) {
if (sort_column != column || sort_order != order) {
setSort(column, order);
select();
}
}
void setFilterColumn(const QString& string_column) {
column_filter = string_column;
}
void setFilter(const QString& string_filter) {
str_filter = string_filter;
}
void setFilterFlags(const Qt::MatchFlag flags) {
flag_filter = flags;
}
void filter(const QString& string_filter) {
if (str_filter != string_filter) {
setFilter(string_filter);
select();
}
}
private:
QSqlQuery __query__;
Qt::MatchFlag flag_filter;
QString str_filter, column_filter;
int sort_column;
Qt::SortOrder sort_order;
};
#endif // QSqlQueryModelEx_h
Синельникова Т.И.