Results 1 to 9 of 9

Thread: QSqlTableModel, QTableView and complex queries

  1. #1
    Join Date
    Jun 2007
    Location
    Austria (Europe)
    Posts
    31
    Thanks
    5
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default 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

    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)?

  2. #2
    Join Date
    Jun 2007
    Location
    Austria (Europe)
    Posts
    31
    Thanks
    5
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSqlTableModel, QTableView and complex queries

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

  3. #3
    Join Date
    Jun 2007
    Location
    Austria (Europe)
    Posts
    31
    Thanks
    5
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSqlTableModel, QTableView and complex queries

    No one using databases?

  4. #4
    Join Date
    Oct 2008
    Location
    Europe
    Posts
    37
    Thanks
    3
    Thanked 1 Time in 1 Post
    Qt products
    Qt4
    Platforms
    Windows

    Default Re: QSqlTableModel, QTableView and complex queries

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

  5. #5
    Join Date
    Jun 2007
    Location
    Austria (Europe)
    Posts
    31
    Thanks
    5
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSqlTableModel, QTableView and complex queries

    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.

  6. #6
    Join Date
    Jun 2007
    Location
    Austria (Europe)
    Posts
    31
    Thanks
    5
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSqlTableModel, QTableView and complex queries

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

  7. #7
    Join Date
    Jun 2007
    Location
    Austria (Europe)
    Posts
    31
    Thanks
    5
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSqlTableModel, QTableView and complex queries

    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.

  8. #8
    Join Date
    Sep 2009
    Posts
    7
    Qt products
    Qt4 Qt/Embedded
    Platforms
    Unix/X11 Windows

    Default Re: QSqlTableModel, QTableView and complex queries

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

  9. #9
    Join Date
    Dec 2007
    Posts
    5
    Qt products
    Qt4
    Platforms
    MacOS X Unix/X11 Windows

    Default Re: QSqlTableModel, QTableView and complex queries

    Have you tried QSqlQueryModel instead of QSqlTabelModel?

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.