QSqlTableModel, QTableView and complex queries

I started to work with QSqlTableModel and became aware of some quirks.

First, this doesn't work:
QSqlQuery *query = new QSqlQuery("SELECT xxx FROM art WHERE art_nr LIKE '13%'");
tablemodel->SetQuery( *query );

You have to use the filter instead:
tablemodel->setFilter("art_nr LIKE '13%'");

It completely ignores the WHERE part of the query. In the Qt help it states "You should normally not call it on a QSqlTableModel. Instead, use setTable(), setSort(), setFilter(), etc., to set up the query."
Nice :rolleyes:

I have rather complex queries involving up to 10 tables and would have preferred to write queries in a single string.


Now the important part:
I googled a bit and found some statements that tell you to not even use QSqlTableModel/QTableView and the like because they only work for very simple work and will fail to work correctly if you use multiple tables.

What are your experiences with fairly complex statemtents and updates? Should I forget QSqlTableView and implement my own (not preferred, much work)?

I have now read the documentation about QSqlRelationalTableModel but what if I simply have a SELECT that joins 3 tables and all I want is to edit data from the first table? (=no need to display anything from table 2 or 3)

To be honest, I'm a newby and a bit puzzled...

No one using databases? :(

I did not understand your problem very well, but have you thought about using views (in mysql) ?

If you have a system with dozens or even hundreds of database tables its not feasable to create a view for every combination of tables - you will get hundreds or thousands of views and performance and maintenance of such a system will be a nightmare.


After spending much time debugging through QSqlTableModel and QSqlRelationalTableModel and after reading the Bugtracker at Qt I have come to the conclusion that both classes are far too limited :(
Please correct me if I have a flaw in my thinking.


Some of the problems I see in QSqlRelationalTableModel:

First database table: customer
Second database table: cities with postal code as primary key

• setTable() itself is a major limit, I need to suppot full selects with joins of several tables
• when I do a setRelation( 1, QSqlRelation("cities", "zipcode", "cityname")) I am only able to show the user the name of the city, not the zipcode. Even when I try to read the zipcode a second time in my select, QSqlRelationalTableModel::selectStatement strips this and isn't able to read it in.
• no ability to show more then one column from the second table, so I can only show the user the city name and not also other information from this table
• no outer join
• What about tables with complex primary keys (not only an id as a primary key but e.g. country plus zip code)???
• all columns of the main are read from the database = performance problem

For me it looks like these classes are not high on the priority list of Qt.

As nobody has a solution for this problem I will make my own classes (subclassing QSqlQuery)...

Some infos as requested:

I've finalized my own QSqlQuery subclass but in a way that is very specific to my needs. What I have:

3 Classes for a database repository that knows what the tables, fields and primary keys should be. Why? First, the database may be in a state that is corrupt. Second, the repository knows much more than the few infos that the database is able to give me.

My own query class derived from QSqlQuery. The class is able to generate SQL statements fro select, insert, update, delete. The select part is able to generate correct joins. Currently I work on support for outer joins on all supported databases.

My own delegates can access the repository and in a table view draw the columns in an application specific way.

Walter,
You couldn't be more right about this...
The implementation of QSql***model classes are sufficient only for basic use of databases from my expirience.

I wonder if people from Nokia plan to do something about it in future releases. It is not that hard to figure out what is needed if you have ANY experience (apart from very basic examples provided) in database programming. I'm also new to Qt (expirienced developer but not in Qt) and I was very disapointed by the implementaton.

Otherwise, we will have to follow your approach - make our own classes...

Have you tried QSqlQueryModel instead of QSqlTabelModel?