QSqlTableModel, QTableView and complex queries

**Walter** · 30th July 2009, 12:32

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

**Walter** · 1st August 2009, 14:01

No one using databases?

**georgep** · 2nd August 2009, 07:19

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

**Walter** · 2nd August 2009, 07:57

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.

**Walter** · 4th August 2009, 11:03

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

**Walter** · 6th August 2009, 10:38

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.

**toroman** · 9th October 2009, 01:03

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