Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

Sort/filter model/view from database table!



  • See this interface and the following code, and be patient please! I hope in this once its clear.

    !http://im10.gulfup.com/2011-12-03/1322954612301.png(test)!

    @Widget::Widget()
    {
    // Connection
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("database");
    bool opened = db.open();

    // populate a QSqlTableModel with an entire sorted table from the database
    tableModel = new QSqlTableModel(parent ,db);
    tableModel->setTable("table"); // with 2 columns: col1 & col2
    tableModel->setEditStrategy(QSqlTableModel::OnManualSubmit);
    tableModel->sort(0, Qt::AscendingOrder);
    tableModel->select();

    //Set the tableModel in a proxyModel and set one column of the proxyModel in a listView
    proxyModel = new QSortFilterProxyModel;
    proxyModel->setSourceModel(tableModel);

    ui->listView->setModel(proxyModel);
    ui->listView->setModelColumn(0);
    

    //When we just begin typing text in the lineEdit, the listView must display items begining whith the lineEdit value.
    connect(ui->lineEdit, SIGNAL(textChanged(QString)),this , SLOT(filterChanged(QString)));

    //When an listView item (col1 item) is clicked, his corresponding field in the other column (col2) is displayed in a textEdit
    connect(ui->listView, SIGNAL(clicked(QModelIndex)),this , SLOT(itemClicked(QModelIndex)));
    }

    //Slots
    void Widget::filterChanged(const QString &filter)
    {
    proxyModel->setFilterRegExp(QString("^%1").arg(filter));
    }

    void Widget::itemClicked(QModelIndex i)
    {
    int r = i.row();
    QSqlRecord rec = tableModel->record(r);
    QSqlField f = rec.field("col2");
    QVariant v = f.value();
    QString str = v.toString();
    ui->textEdit->setText(str);
    }@

    Questions:
    1/ Is this a convenience use of the model/view architecture?
    2/ The problem with this code is that the filter don't work, i.e. when I type something in the lineEdit, the listView displays nothing.
    3/ Advices?

    thanks in advance



  • Your itemClicked method can be simplified:

    @
    void Widget::itemClicked(QModelIndex i)
    {
    int r = i.row();
    QString str = proxyModel->data(proxyModel->index(r,1), Qt::DisplayRole);
    ui->textEdit->setText(str);
    }
    @

    The filter looks ok to me, one would have to test in a real application to see what's going wrong.



  • Thanks for your replay.

    The data() method return a QVariant value, not QString. And I need the second column col2 instead of the first. So some modifications are required as follow:

    @int r = i.row();
    QVariant v = proxyModel->data(proxyModel->index(r,2), Qt::DisplayRole);
    QString str = v.toString();
    ui->textEdit->setText(str);@

    but I have always the same problem with the filter, I don't find the solution and I am stopped now...



  • This code works for me:

    create-qdndb.sql

    @
    CREATE TABLE qdntest (
    col1 varchar(100) not null,
    col2 varchar(100) not null
    );

    INSERT INTO qdntest VALUES( 'abc' ,'column 2 of abc');
    INSERT INTO qdntest VALUES( 'aboc' ,'column 2 of aboc');
    INSERT INTO qdntest VALUES( 'abzee' ,'column 2 of abzee');
    INSERT INTO qdntest VALUES( 'qdn' ,'column 2 of qdn');
    INSERT INTO qdntest VALUES( 'test' ,'column 2 of test');
    INSERT INTO qdntest VALUES( 'what' ,'column 2 of what');
    @

    mainwindow.h

    @

    #ifndef MAINWINDOW_H
    #define MAINWINDOW_H

    #include <QtGui/QDialog>

    class QListView;
    class QLineEdit;
    class QTextEdit;
    class QSqlTableModel;
    class QSortFilterProxyModel;
    class QModelIndex;

    class MainWindow : public QDialog
    {
    Q_OBJECT

    public:
    MainWindow(QWidget *parent = 0);
    ~MainWindow();

    protected slots:
    void filterChanged(const QString &filter);
    void itemClicked(const QModelIndex &index);

    private:
    QListView *listView;
    QLineEdit *lineEdit;
    QTextEdit *textEdit;
    QSqlTableModel *tableModel;
    QSortFilterProxyModel *proxyModel;
    };

    #endif // MAINWINDOW_H
    @

    mainwindow.cpp

    @

    #include "mainwindow.h"

    #include <QListView>
    #include <QLineEdit>
    #include <QTextEdit>
    #include <QSqlTableModel>
    #include <QSortFilterProxyModel>
    #include <QVBoxLayout>
    #include <QSqlDatabase>

    MainWindow::MainWindow(QWidget *parent)
    : QDialog(parent)
    {
    QVBoxLayout *layout = new QVBoxLayout(this);

    lineEdit = new QLineEdit;
    layout->addWidget(lineEdit);
    
    listView = new QListView;
    layout->addWidget(listView);
    
    textEdit = new QTextEdit;
    layout->addWidget(textEdit);
    
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("/Users/volker/qdn.db");
    db.open();
    
    tableModel = new QSqlTableModel(this, db);
    tableModel->setTable("qdntest");
    tableModel->setEditStrategy(QSqlTableModel::OnManualSubmit);
    tableModel->sort(0, Qt::AscendingOrder);
    tableModel->select();
    
    proxyModel = new QSortFilterProxyModel;
    proxyModel->setSourceModel(tableModel);
    
    listView->setModel(proxyModel);
    listView->setModelColumn(0);
    
    connect(lineEdit, SIGNAL(textChanged(QString)), this, SLOT(filterChanged(QString)));
    connect(listView, SIGNAL(clicked(QModelIndex)), this, SLOT(itemClicked(QModelIndex)));
    

    }

    MainWindow::~MainWindow()
    {
    }

    void MainWindow::filterChanged(const QString &filter)
    {
    proxyModel->setFilterRegExp(QString("^%1").arg(filter));
    }

    void MainWindow::itemClicked(const QModelIndex &index)
    {
    QString text = proxyModel->data(proxyModel->index(index.row(), 1), Qt::DisplayRole).toString();
    textEdit->setPlainText(text);
    }
    @

    main.cpp

    @
    #include <QtGui/QApplication>
    #include "mainwindow.h"

    int main(int argc, char *argv[])
    {
    QApplication a(argc, argv);
    MainWindow w;
    w.show();

    return a.exec&#40;&#41;;
    

    }
    @

    qdntest.pro

    @

    #-------------------------------------------------

    Project created by QtCreator 2011-12-04T22:06:13

    #-------------------------------------------------

    QT += core gui sql

    TARGET = SqlProxyModelTest
    TEMPLATE = app

    SOURCES += main.cpp
    mainwindow.cpp

    HEADERS += mainwindow.h
    @



  • Sorry, I tested it but the filter always don't work! when I type something in the lineEdit, the listView becomes empty.



  • So, that seems reasonable to me if no item in the list matches the filter text. What is the actual problem?



  • So what didd you type in? Of course, there will only be items remaining, if you start your search with one of the letters a, q, t or w.



  • Yes, even if I type a, q, t or w, the listView becomes empty again.
    It works for you?!



  • [quote author="freecamellia" date="1323086553"]Yes, even if I type a, q, t or w, the listView becomes empty again.
    It works for you?![/quote]

    Of course:

    [quote author="Volker" date="1323033824"]This code works for me:[/quote]



  • Thank you for your helps during all this time.
    Regards.



  • [quote author="Andre" date="1323085112"]So, that seems reasonable to me if no item in the list matches the filter text. What is the actual problem?[/quote]

    [quote author="freecamellia" date="1322956485"]when I type something in the lineEdit, the listView displays nothing.
    [/quote]

    This is my problem.



  • That only means that your filter filters out everything: no row matches whatever the filter was that was created based on what was typed in the line edit.

    I have no way of checking how you implemented that, but based on the implementation shown in this topic:
    @
    void MainWindow::filterChanged(const QString &filter)
    {
    proxyModel->setFilterRegExp(QString("^%1").arg(filter));
    }
    @
    the filter should match any item that starts with whatever you typed in the line edit.


Log in to reply