Talk:Sort and Filter a QSqlQueryModel

From Qt Wiki
Jump to: navigation, search

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=?

  1. ifndef QSqlQueryModelEx_h
  2. define QSqlQueryModelEx_h
  1. include <QSqlQueryModel>
  2. include <QSqlQuery>
  3. 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;

};

  1. endif // QSqlQueryModelEx_h

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