Sort/filter model/view from database table!

freecamellia

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

goetz

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.

freecamellia

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...

goetz

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
@

freecamellia

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

andre

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

goetz

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.

freecamellia

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

goetz

[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]

freecamellia

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

freecamellia

[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.

andre

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.