Some filters in QSqlTableModel and SQLite

Mikeeeeee

Hi!
There is this code:

QSqlDatabase SearchDB = QSqlDatabase::addDatabase("QSQLITE");
    SearchDB.setDatabaseName("C:\\CVReader\\ResumeArraySQLite.db");

    QSqlTableModel *SearchTableModel = new QSqlTableModel(this);
    SearchTableModel ->setTable("SQLliteResumeArray");

I need to make some filters.
If I use such code only last filter performs:

SearchTableModel->setFilter(" Age > 30 ");
SearchTableModel->setFilter(" Age < 40 ");

What code should I use to make two or more filters perform at the same time?
May be I should to use QSortFilterProxyModel?

jsulm

@Mikeeeeee As mentioned here http://doc.qt.io/qt-5/qsqltablemodel.html#setFilter
"The filter is a SQL WHERE clause without the keyword WHERE", so you can do it same way you do it in a SQL query.

Mikeeeeee

I understand this.
How can I make few request?

SGaist

Hi,

@Mikeeeeee said in Some filters in QSqlTableModel and SQLite:

How can I make few request?

What do you mean by that ?

jsulm

@Mikeeeeee "I understand this." - then you should know how to do it.
Like explained here https://www.w3schools.com/sql/sql_and_or.asp

SearchTableModel->setFilter(" Age > 30 AND Age < 40");

Mikeeeeee

I understand this.
But I have many composite filters.
I need to make many separated filters.

Mikeeeeee

May be I ned to use temp table in SQLite?

jsulm

@Mikeeeeee said in Some filters in QSqlTableModel and SQLite:

composite filters

Can you give an example?
Why can't you do it like in my example?

"May be I ned to use temp table in SQLite?" - maybe you could use SQL views?

JonB

@Mikeeeeee
If you want several filters, you offer several filters to your user. If you want them them to be ANDed together, you build your SQL filter using AND between each one, like @jsulm said. It's your job to build up the necessary WHERE clause from whatever filtering you want. Does that answer your question?

Mikeeeeee

It's not answer.
Thea example of my filters:
TableModel->setFilter("A1 = 30 AND A2 = 3 AND A3 = 7");
TableModel->setFilter("A4 = 2 OR A5 = 2 OR A6 = 2");
TableModel->setFilter("A7 = 3 OR A8 = 3 OR A8 = 3");
TableModel->setFilter("A9 = 5 OR A10 = 5 OR A11 = 5");

jsulm

@Mikeeeeee You can combine them into one

Mikeeeeee

This variant will be slowly.

Christian Ehrlicher

@Mikeeeeee said in Some filters in QSqlTableModel and SQLite:

This variant will be slowly.

Why should filtering by the database be slower than doing it in c++?

jsulm

@Mikeeeeee said in Some filters in QSqlTableModel and SQLite:

This variant will be slowly.

You measured it? Or how do you know it will be slow?
If you use SQL you should do the filtering in SQL, this is what SQL is for. And SQL server (and SQLite) can optimise SQL queries quite good. If you do the filtering in several steps in C++ this will happen: first you filter using one filter - a big amount of data will be loaded in your app (everything matching the first WHERE criteria), then you will apply second filter and so on. Doing complete filtering in SQL will be different: you will only load data in your app which matches all filters - way less memory consumption and faster operation.

Mikeeeeee

thank you