Some filters in QSqlTableModel and SQLite
-
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? -
@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. -
Hi,
@Mikeeeeee said in Some filters in QSqlTableModel and SQLite:
How can I make few request?
What do you mean by that ?
-
@Mikeeeeee "I understand this." - then you should know how to do it.
Like explained here https://www.w3schools.com/sql/sql_and_or.aspSearchTableModel->setFilter(" Age > 30 AND Age < 40");
-
@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?
-
@Mikeeeeee
If you want several filters, you offer several filters to your user. If you want them them to beAND
ed together, you build your SQL filter usingAND
between each one, like @jsulm said. It's your job to build up the necessaryWHERE
clause from whatever filtering you want. Does that answer your question? -
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"); -
@Mikeeeeee You can combine them into one
-
@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++?
-
@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.