How to show the filtered data of QSqlTableModel in another tableView in Qt?

Question

I have loaded a database by using QSqlTableModel, and show it in tableView1. Now, I have setup a filter for this QSqlTableModel, and want to show the filtered data in tableView2. tableView1 and tableView2 should not interact with each other. Then, how to show it, and what is the fastest way?

Note:

The database only contains number without string, and the dimension is not large (about 500*5);
The data in tableView2 do not need to be stored.

The main code is as follows:

QSqlTableModel *model = new QSqlTableModel(NULL, db);
model->setTable(tableName);
model->select();
tableView->setModel(model);
tableView->show();
model->setFilter("colum5 > 10");

I do not want to write the filtred data in another database, and then store it, show it. I want to find some better ways to show it. Or, do I need to extract the filtered data in a matrix, and show this matrix in tableView? Can anyone provides some thoughts? Thank you

Is there any reason you can't have two `QSqlTableModel`s -- one for each `QTableView` -- but which both use the same `QSqlDatabase` instance? — G.M., Oct 24 '16 at 07:19
@G.M. because I only want to have one copy of this database. If I use two `QSqlTableModel`, it is a waste of memory. — stackname, Nov 05 '16 at 01:18

score 3 · Accepted Answer · answered Oct 24 '16 at 12:07

Since you want to display both models, I think that the cleanest way to do what you want would be using a QSortFilterProxyModel subclass (I will refer to it as model2) in tableView2, override filterAcceptsRow() to accept only rows with column5 > 10, and set its source model to model1 (your original QSqlTableModel).

This will make both models synchronized together, an edit to any one of them will be applied automatically to both models.

Here is how your QSortFilterProxyModel subclass might look like:

class MyFilterModel : public QSortFilterProxyModel{
public:
    explicit MyFilterModel(QObject* parent= nullptr):QSortFilterProxyModel(parent){}
    ~MyFilterModel(){}

    void setCol5Min(int val){
        col5Min= val;
        invalidateFilter();
    }

protected:
    bool filterAcceptsRow(int source_row, const QModelIndex &/*source_parent*/) const{
        //get index using source_row, and the column you want to filter
        QModelIndex index4= sourceModel()->index(source_row, 4);
        //accept the row only when col5's value is greater than col5Min
        return (sourceModel()->data(index4).toInt() > col5Min);
    }
private:
    int col5Min;
};

and you can use it like this:

QSqlTableModel *model1 = new QSqlTableModel(this, db);
model1->setTable(tableName);
model1->select();
tableView1->setModel(model1);

MyFilterModel* model2= new MyFilterModel(this);
model2->setCol5Min(10);
model2->setSourceModel(&model1);
tableView2->setModel(model2);

The only downside to the above approach is that filtering is not performed in the database (it is done in your application code), so you can't take advantage of any indexes you might have on col5 for example (but if you have 500 rows, this is not an issue at all).

If you wanted filtering to be performed in the database, You'll have to use two separate QSqlTableModels:

QSqlTableModel *model1 = new QSqlTableModel(this, db);
model1->setTable(tableName);
model1->select();
tableView1->setModel(model1);

QSqlTableModel *model2 = new QSqlTableModel(this, db);
model2->setTable(tableName);
model2->setFilter("colum5 > 10");
model2->select();
tableView2->setModel(model2);

But this will not make your table views synchronized. When the user edits one of the table views, you will have to update the other one.

Thank you very much. `QSortFilterProxyModel` is good way for solving my problem since I need to synchronize to model views. Thank you again. — stackname, Nov 05 '16 at 01:20

How to show the filtered data of QSqlTableModel in another tableView in Qt?

1 Answers1