Results 1 to 8 of 8

Thread: How do I display information from three database tables in a QTableView?

  1. #1
    Join Date
    Sep 2010
    Posts
    4
    Thanks
    2

    Default How do I display information from three database tables in a QTableView?

    Here is the story: The application I am working on needs information stored in three tables of a normalized relational database. The information has to be displayed in one QTableView since that would be the "natural view" onto the data. In sql speak its a simple equi join as in
    Qt Code:
    1. select * from A, B, C where A.b=B.id AND A.c=C.id
    To copy to clipboard, switch view to plain text mode 
    The app needs to be able to create, update and delete rows in/from the three tables and display the results in the QTableView. Coming from web apps, I thought this would be a pretty easy one, but I can't figure out how to do it in qt.

    The qt examples use a QSqlRelationalTableModel in combination with a QTableView. Foreign keys in main table A are resolved using e.g.
    Qt Code:
    1. model->setRelation(2, QSqlRelation("city", "id", "name"));
    To copy to clipboard, switch view to plain text mode 
    Is it possible to resolve foreign keys to more than one field/column in the model? I need more information from table B and C than just replacing the id columns in table A by one value from table B or C.

    I have searched the web and read threads aiming at basically the same topic. Those threads were either not answered or answered by proposing to implement a custom model - is there no other way to solve it?

    The examples I have found so far on how to implement a custom model are - let's say "basic" - do you know a good web resource to start from? I have read and implemented half of a Custom Model but the result is awkward. I have read and implemented a MasterDetail Form but was not quite happy with the result since it splits the display into multiple QTableViews. I have read the documentation on QSqlRelationalTableModel and experimented with it.

    Bare in mind I am new to qt and probably miss one or more obvious things. So, all suggestions are welcome! Thank you in advance for your help and time!
    Nele

  2. #2
    Join Date
    Jan 2006
    Location
    Napoli, Italy
    Posts
    621
    Thanks
    5
    Thanked 86 Times in 81 Posts
    Qt products
    Qt3 Qt4
    Platforms
    Unix/X11 Windows

    Default Re: How do I display information from three database tables in a QTableView?

    HI,

    have you tried to set the second relation calling setRelation again?
    Qt Code:
    1. model->setRelation(3, QSqlRelation(C_tableName, "id", fieldname);
    To copy to clipboard, switch view to plain text mode 

    see QSqlRelationalTableModel detailed description
    A camel can go 14 days without drink,
    I can't!!!

  3. The following user says thank you to mcosta for this useful post:

    Nele (24th September 2010)

  4. #3
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,618
    Thanks
    13
    Thanked 1,591 Times in 1,520 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: How do I display information from three database tables in a QTableView?

    You can get the display you want by setting the QTableView's model to a QSqlQueryModel with your three-table SQL in it. There is no way for the view to update through this model as-is though. By deriving from QSqlQueryModel and implementing flags(), setData(), insertRows(), and removeRows() you should be able to make it updateable . You will need to decide how to handle trying to set data in one column from table C when the corresponding rows in tables A and B do not exist, or if you try to update one of the foreign key fields etc. (this is why it doesn't Just Work (TM): there is no single correct answer). The Model Subclassing Reference, Creating New Models, and the Query Model Example covers what you would need to do.

  5. The following user says thank you to ChrisW67 for this useful post:

    Nele (24th September 2010)

  6. #4
    Join Date
    Sep 2010
    Posts
    4
    Thanks
    2

    Default Re: How do I display information from three database tables in a QTableView?

    Quote Originally Posted by mcosta View Post
    have you tried to set the second relation calling setRelation again?
    Qt Code:
    1. model->setRelation(3, QSqlRelation(C_tableName, "id", fieldname);
    To copy to clipboard, switch view to plain text mode 
    Yes, I have. Each foreign key column in table A can be replaced by one value from the referenced table (B or C) - but that is not what I want. I need the whole record from tables B and C, e.g. something like
    Qt Code:
    1. table A = (A.id, A.b_id, A.c_id, A.name, A.age) and
    2. table B = (B.id, B.profession, B.company, ...) and
    3. table C = (C.id, C.street, C.zipcode, C.city, ...) resulting in the view
    4. (A.id, B.profession, B.company, C.street, C.zipcode, C.city, A.name, A.age)
    To copy to clipboard, switch view to plain text mode 

  7. #5
    Join Date
    Sep 2010
    Posts
    4
    Thanks
    2

    Default Re: How do I display information from three database tables in a QTableView?

    Quote Originally Posted by ChrisW67 View Post
    You can get the display you want by setting the QTableView's model to a QSqlQueryModel with your three-table SQL in it. There is no way for the view to update through this model as-is though. By deriving from QSqlQueryModel and implementing flags(), setData(), insertRows(), and removeRows() you should be able to make it updateable . You will need to decide how to handle trying to set data in one column from table C when the corresponding rows in tables A and B do not exist, or if you try to update one of the foreign key fields etc. (this is why it doesn't Just Work (TM): there is no single correct answer). The Model Subclassing Reference, Creating New Models, and the Query Model Example covers what you would need to do.
    Ok. I will try that and post back. Thank you for your suggestion!

  8. #6
    Join Date
    Sep 2010
    Posts
    4
    Thanks
    2

    Default Re: How do I display information from three database tables in a QTableView?

    @ChrisW67: It took me ages to implement it, but finally it works using the approach you suggested. Thank you for your explanaition!

  9. #7
    Join Date
    Jun 2013
    Posts
    3
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: How do I display information from three database tables in a QTableView?

    Nele, Can you post few code OR some idea? How did you do that?

  10. #8
    Join Date
    Oct 2014
    Posts
    22
    Qt products
    Qt3 Qt4 Qt5 PyQt3 PyQt4

    Default Re: How do I display information from three database tables in a QTableView?

    This is an old post, but can you post a solution to this problem?

Similar Threads

  1. list tables column name (database)
    By baray98 in forum Qt Programming
    Replies: 1
    Last Post: 26th September 2012, 11:18
  2. Replies: 1
    Last Post: 20th September 2010, 07:46
  3. Replies: 4
    Last Post: 13th July 2010, 05:17
  4. Joining tables into QTableView
    By SykeS in forum Qt Programming
    Replies: 10
    Last Post: 8th June 2010, 10:39
  5. Way to load all of data from 2 tables into one QTableView?
    By Kevin Hoang in forum Qt Programming
    Replies: 8
    Last Post: 3rd April 2010, 09:42

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.