Talk:Sort and Filter a QSqlQueryModel

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

NetZwerg (talk)

Приведенная ниже реализация позволяет: редактировать/устанавливать 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

Синельникова Т.И.