Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. Adding foreign column to a "virtual table" and performing several joins from one table over several
QtWS25 Last Chance

Adding foreign column to a "virtual table" and performing several joins from one table over several

Scheduled Pinned Locked Moved Solved General and Desktop
sql
14 Posts 3 Posters 3.9k Views
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • G Offline
    G Offline
    G4bandit
    wrote on 5 Dec 2017, 15:20 last edited by G4bandit 1 Dec 2018, 13:50
    #1

    Hi,

    does someone knows how to transfer SQL this all SQL commands to c++ using QtSql libs. QtRelationalTableModel and so on.

    SELECT a.[Name]
    ,a.[Surname]
    ,a.[Name_St]
    ,s1.Prog
    ,s2.Kind
    FROM [dbo].[AdaList_Main] a
    JOIN LinkAdaList_AdaSet s1
    ON a.Surname = s1.Surname
    JOIN AdaSet s2
    ON s1.Prog = s2.Prog
    WHERE a.Surname = 'Dummy';

    Inside of Microsoft SQL Server managment Studio it works. In future it is tought that the Attribute Surname (now 'Dummy') is feeded by the Content of "LineEdit".

    For me the most tricky is to

    • perform several joins at once
    • add columns from foreign tables

    ATTACHMENT 2017.12.06

    My Code look so far like this

    RTblModel = new QSqlRelationalTableModel(this);
    RTblModel->setTable("AdaList_Main");
    
    RTblModel->setRelation(1,QSqlRelation("AdaSet","Surname","Prog"));     
    RTblModel->select();
    ui->TV_DBOut->setModel(RTblModel);
    

    It Works so far it is this part
    SELECT a.[Name]
    ,s1.Prog
    ,a.[Name_St]
    FROM [dbo].[AdaList_Main] a
    JOIN LinkAdaList_AdaSet s1
    ON a.Surname = s1.Surname

    Whereas in QT "Surname" is replaced with "Prog"
    Now when I want to do the remaining part with

    //place directly after the first setRelation
    RTblModel->setRelation(1,QSqlRelation("AdaSet","Prog","Kind");
    

    Only what happens is that the urgent column "Surname" is replaced with the column "Kind" (of the 3.thrd table) but no data is showed.
    I can not explain to my self why.

    J 1 Reply Last reply 5 Dec 2017, 22:37
    0
    • S Offline
      S Offline
      SGaist
      Lifetime Qt Champion
      wrote on 5 Dec 2017, 21:05 last edited by
      #2

      Hi,

      What about QSqlQueryModel ? You can set your current query on it.

      Interested in AI ? www.idiap.ch
      Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

      1 Reply Last reply
      1
      • G G4bandit
        5 Dec 2017, 15:20

        Hi,

        does someone knows how to transfer SQL this all SQL commands to c++ using QtSql libs. QtRelationalTableModel and so on.

        SELECT a.[Name]
        ,a.[Surname]
        ,a.[Name_St]
        ,s1.Prog
        ,s2.Kind
        FROM [dbo].[AdaList_Main] a
        JOIN LinkAdaList_AdaSet s1
        ON a.Surname = s1.Surname
        JOIN AdaSet s2
        ON s1.Prog = s2.Prog
        WHERE a.Surname = 'Dummy';

        Inside of Microsoft SQL Server managment Studio it works. In future it is tought that the Attribute Surname (now 'Dummy') is feeded by the Content of "LineEdit".

        For me the most tricky is to

        • perform several joins at once
        • add columns from foreign tables

        ATTACHMENT 2017.12.06

        My Code look so far like this

        RTblModel = new QSqlRelationalTableModel(this);
        RTblModel->setTable("AdaList_Main");
        
        RTblModel->setRelation(1,QSqlRelation("AdaSet","Surname","Prog"));     
        RTblModel->select();
        ui->TV_DBOut->setModel(RTblModel);
        

        It Works so far it is this part
        SELECT a.[Name]
        ,s1.Prog
        ,a.[Name_St]
        FROM [dbo].[AdaList_Main] a
        JOIN LinkAdaList_AdaSet s1
        ON a.Surname = s1.Surname

        Whereas in QT "Surname" is replaced with "Prog"
        Now when I want to do the remaining part with

        //place directly after the first setRelation
        RTblModel->setRelation(1,QSqlRelation("AdaSet","Prog","Kind");
        

        Only what happens is that the urgent column "Surname" is replaced with the column "Kind" (of the 3.thrd table) but no data is showed.
        I can not explain to my self why.

        J Offline
        J Offline
        JonB
        wrote on 5 Dec 2017, 22:37 last edited by
        #3

        @G4bandit

        QSqlQueryModel is the most flexible, and can handle the SELECT you show.

        QSqlTableModel is really intended to map one-to-one to a SQL TABLE. You could write your SELECT as a SQL VIEW , I don't know whether QSqlTableModel would be prepared to connect to that instead of a table.

        You can pass the value of a line edit into QSqlQueryModel by changing the query, or into QSqlTableModel via the filter.

        1 Reply Last reply
        0
        • G Offline
          G Offline
          G4bandit
          wrote on 6 Dec 2017, 09:03 last edited by G4bandit 12 Jun 2017, 09:07
          #4

          @SGaist And @JNBarchan
          Thks so far for your proposals

          I found on this homepage from QT
          SQLProgramming QtDoc
          the following statement:

          0_1512550503607_QSQLOverview.png

          And for me it was clear I have a lot of table with foreign key and I also do want to be able to edit data so I decided for QSqlRelationalTableModel(Maybe I am wrong and do not understand the overview table complet :) )

          But in fact I stucked on the two explained points.

          • perform several joins at once
          • add columns from foreign tables

          @JNBarchan
          With using QSqlRelationalTableModel and a VIEW generated in SQL (using Microsoft SQL Server Management Studio) I came further, MUCH further it works like I want it to.

          So now I can decide either to work continuously with VIEWs from SSMS or try to understand more QSql Classes to be able to make the same stuff like with the VIEWs from SSMS.
          Motivation for more understanding is that I will intensivly work with QSql anyway for at least the next 2 months.

          J 1 Reply Last reply 6 Dec 2017, 09:38
          0
          • G G4bandit
            6 Dec 2017, 09:03

            @SGaist And @JNBarchan
            Thks so far for your proposals

            I found on this homepage from QT
            SQLProgramming QtDoc
            the following statement:

            0_1512550503607_QSQLOverview.png

            And for me it was clear I have a lot of table with foreign key and I also do want to be able to edit data so I decided for QSqlRelationalTableModel(Maybe I am wrong and do not understand the overview table complet :) )

            But in fact I stucked on the two explained points.

            • perform several joins at once
            • add columns from foreign tables

            @JNBarchan
            With using QSqlRelationalTableModel and a VIEW generated in SQL (using Microsoft SQL Server Management Studio) I came further, MUCH further it works like I want it to.

            So now I can decide either to work continuously with VIEWs from SSMS or try to understand more QSql Classes to be able to make the same stuff like with the VIEWs from SSMS.
            Motivation for more understanding is that I will intensivly work with QSql anyway for at least the next 2 months.

            J Offline
            J Offline
            JonB
            wrote on 6 Dec 2017, 09:38 last edited by JonB 12 Jun 2017, 09:44
            #5

            @G4bandit

            1. QSqlQueryModel Read-only: Well, what do you expect from your SELECT query? You certainly don't have a single, actual table you can update from it (you have a JOIN on multiple tables). However, you can use QSqlQuery (not Model) to execute INSERT/UPDATE/DELETE statements which you generate ourself against, say, AdaList_Main table.

            2. QSqlRelationalTableModel with foreign key support: Lets you fetch the value from another table's column corresponding to a value in a column in your table.

            RTblModel->setRelation(1,QSqlRelation("AdaSet","Prog","Kind");
            Only what happens is that the urgent column "Surname" is replaced with the column "Kind" (of the 3.thrd table) but no data is showed.

            What "3.thrd table"?? I see 2 tables in your query? Plus I see no reference to anything named Kind in the SELECT?

            Meanwhile, it is interesting to hear that you say QSqlRelationalTableModel does work with a SQL VIEW as source "table". Of course, that will be for SELECT queries only, you won't be able to update anything through it (well, unless you want to look into MS SQL "Updatable VIEWs", but that's a different story).

            1 Reply Last reply
            1
            • G Offline
              G Offline
              G4bandit
              wrote on 8 Dec 2017, 11:36 last edited by
              #6

              @JNBarchan

              DATE:17.12.08
              Regarding Table_Views from SSMS
              Interesting concerns from your side. In fact when I open the view inside of SSMS the table generated is grew highlited (like RO) and I am not able to edit data. So actually this should work the same.

              But in fact not :) . When I open up and show in the qt tableview I am able to edit and update the fields. The only restriction is I have to push enter. When I just edit and go to other field then the edit will not be saved.

              J 1 Reply Last reply 8 Dec 2017, 11:47
              0
              • G G4bandit
                8 Dec 2017, 11:36

                @JNBarchan

                DATE:17.12.08
                Regarding Table_Views from SSMS
                Interesting concerns from your side. In fact when I open the view inside of SSMS the table generated is grew highlited (like RO) and I am not able to edit data. So actually this should work the same.

                But in fact not :) . When I open up and show in the qt tableview I am able to edit and update the fields. The only restriction is I have to push enter. When I just edit and go to other field then the edit will not be saved.

                J Offline
                J Offline
                JonB
                wrote on 8 Dec 2017, 11:47 last edited by
                #7

                @G4bandit

                1. I wrote:

                What "3.thrd table"?? I see 2 tables in your query? Plus I see no reference to anything named Kind in the SELECT?

                No response from you.

                1. The Qt table view will be editing the table associated with the QSqlRelationalTableModel, not something to do with SELECT statement, which you no longer have. This is quite different from any QSqlQueryModel you might have. Since you have never said what it is you might wish to edit, it's impossible to know whether this is what you want or not.

                If you need further help on this question, I suggest you clarify what code you are actually using, and precisely what you might want to achieve.

                1 Reply Last reply
                0
                • G Offline
                  G Offline
                  G4bandit
                  wrote on 8 Dec 2017, 12:26 last edited by
                  #8
                  
                      You wrote:
                  
                      What "3.thrd table"?? I see 2 tables in your query? Plus I see no reference to anything named Kind in the SELECT?
                  
                  No response from you.
                  

                  Please a bit more patience. Following is working for me and also given by examples from Qt itself
                  Tbl1->Tbl2 (Table refer to...) AND
                  Tbl1-> Tbl3
                  with setRelation "I am able" to get some specific column values of the refered tables but in this constellation
                  Tbl1->Tbl2->Tbl3
                  "I am not able to" get a specific attribute(column) of Tbl3. This is my challenge.
                  Tbl1 refer to Tbl3 indirectly trough Tbl2

                  You wrote
                      The Qt table view will be editing the table associated with the QSqlRelationalTableModel, not something to do with SELECT statement, which you no longer have. This is quite different from any QSqlQueryModel you might have. Since you have never said what it is you might wish to edit, it's impossible to know whether this is what you want or not.
                  
                  If you need further help on this question, I suggest you clarify what code you are actually using, and precisely what you might want to achieve.
                  

                  Sorry for that I misunderstood you. So I did not meant "your concerns".

                  What do I precisly want to achieve?
                  I want to create a gui or widget where the connection between tbl1->tbl2->Tbl3 is resolved to only ONE TableView and the User of the widget has the overview about the mandatory attributes. So does not to fight with background SQL error messages.

                  My current state regarding

                  • perform several joins at once
                    Workaround: to create view in SSMS and refer to the views. Reading, Writing to the table view does not make any problems.
                    Niceto: due to CVS (tortoise svn) or at all better understanding of Qt Data Model to perform this step inside of Qt instead creating view in SSMS.

                  • add columns from foreign tables
                    I do it currently in this way

                  model->setTable("Tbl1");
                  model->setRelation(ColumnIdx, QSqlRelation("Tbl2","RelatedColumnToTbl1FromTbl2","Column1OfTbl2, Column2OfTbl2......."))
                  It is working, - performance is not that nice but it is working.
                  
                  J 1 Reply Last reply 8 Dec 2017, 12:37
                  0
                  • G G4bandit
                    8 Dec 2017, 12:26
                    
                        You wrote:
                    
                        What "3.thrd table"?? I see 2 tables in your query? Plus I see no reference to anything named Kind in the SELECT?
                    
                    No response from you.
                    

                    Please a bit more patience. Following is working for me and also given by examples from Qt itself
                    Tbl1->Tbl2 (Table refer to...) AND
                    Tbl1-> Tbl3
                    with setRelation "I am able" to get some specific column values of the refered tables but in this constellation
                    Tbl1->Tbl2->Tbl3
                    "I am not able to" get a specific attribute(column) of Tbl3. This is my challenge.
                    Tbl1 refer to Tbl3 indirectly trough Tbl2

                    You wrote
                        The Qt table view will be editing the table associated with the QSqlRelationalTableModel, not something to do with SELECT statement, which you no longer have. This is quite different from any QSqlQueryModel you might have. Since you have never said what it is you might wish to edit, it's impossible to know whether this is what you want or not.
                    
                    If you need further help on this question, I suggest you clarify what code you are actually using, and precisely what you might want to achieve.
                    

                    Sorry for that I misunderstood you. So I did not meant "your concerns".

                    What do I precisly want to achieve?
                    I want to create a gui or widget where the connection between tbl1->tbl2->Tbl3 is resolved to only ONE TableView and the User of the widget has the overview about the mandatory attributes. So does not to fight with background SQL error messages.

                    My current state regarding

                    • perform several joins at once
                      Workaround: to create view in SSMS and refer to the views. Reading, Writing to the table view does not make any problems.
                      Niceto: due to CVS (tortoise svn) or at all better understanding of Qt Data Model to perform this step inside of Qt instead creating view in SSMS.

                    • add columns from foreign tables
                      I do it currently in this way

                    model->setTable("Tbl1");
                    model->setRelation(ColumnIdx, QSqlRelation("Tbl2","RelatedColumnToTbl1FromTbl2","Column1OfTbl2, Column2OfTbl2......."))
                    It is working, - performance is not that nice but it is working.
                    
                    J Offline
                    J Offline
                    JonB
                    wrote on 8 Dec 2017, 12:37 last edited by
                    #9

                    @G4bandit said in Adding foreign column to a "virtual table" and performing several joins from one table over several:

                    Please a bit more patience. Following is working for me and also given by examples from Qt itself
                    Tbl1->Tbl2 (Table refer to...) AND
                    Tbl1-> Tbl3
                    with setRelation "I am able" to get some specific column values of the refered tables but in this constellation
                    Tbl1->Tbl2->Tbl3
                    "I am not able to" get a specific attribute(column) of Tbl3. This is my challenge.
                    Tbl1 refer to Tbl3 indirectly trough Tbl2

                    Yes, this is precisely my understanding of the facilities offered by QSqlRelationalTableModel. It supports one "main" table, with as many foreign key relations as you wish between the main table and other FK tables. It does not support any further level of FKs from the FK tables to yet other FK tables. If you wanted that you'd need one QSqlRelationalTableModel for Tbl1->Tbl2, and another QSqlRelationalTableModel for Tbl2->Tbl3. And then you'd have to handle whatever is necessary for Tbl1->Tbl2->Tbl3 yourself.

                    1 Reply Last reply
                    0
                    • G Offline
                      G Offline
                      G4bandit
                      wrote on 8 Dec 2017, 15:48 last edited by
                      #10

                      @JNBarchan said in Adding foreign column to a "virtual table" and performing several joins from one table over several:

                      It does not support any further level of FKs from the FK tables to yet other FK tables. If you wanted that you'd need one QSqlRelationalTableModel for Tbl1->Tbl2, and another QSqlRelationalTableModel for Tbl2->Tbl3. And then you'd have to handle whatever is necessary for Tbl1->Tbl2->Tbl3 yourself.

                      This is exactly the info I have missed as a beginner. Otherwise without this info I would still asking myself:
                      "Is it because I do not understand exactly the Qt Table Model or because the Qt Table Model does not give me the option like I expact"

                      For me it is new of imagination that I have to handle two different RelationalTables to visualise a connection like Tbl1->Tbl2->Tbl3(-> = refer to...).

                      J 1 Reply Last reply 8 Dec 2017, 17:06
                      0
                      • G G4bandit
                        8 Dec 2017, 15:48

                        @JNBarchan said in Adding foreign column to a "virtual table" and performing several joins from one table over several:

                        It does not support any further level of FKs from the FK tables to yet other FK tables. If you wanted that you'd need one QSqlRelationalTableModel for Tbl1->Tbl2, and another QSqlRelationalTableModel for Tbl2->Tbl3. And then you'd have to handle whatever is necessary for Tbl1->Tbl2->Tbl3 yourself.

                        This is exactly the info I have missed as a beginner. Otherwise without this info I would still asking myself:
                        "Is it because I do not understand exactly the Qt Table Model or because the Qt Table Model does not give me the option like I expact"

                        For me it is new of imagination that I have to handle two different RelationalTables to visualise a connection like Tbl1->Tbl2->Tbl3(-> = refer to...).

                        J Offline
                        J Offline
                        JonB
                        wrote on 8 Dec 2017, 17:06 last edited by JonB 12 Aug 2017, 17:07
                        #11

                        @G4bandit
                        No problem about being a beginner! I am too (mostly).

                        But don't forget, in your original question your code only showed 2 tables and a single JOIN, which one QSqlRelationalTableModel can handle. Since then you've edited it at some point, and added the complication of the JOIN from table #2 -> table #3, which one QSqlRelationalTableModel on its own can't do.

                        For the future of posting here, it's OK to fix "mistakes" in your original code, but it's probably better to show new code in a reply here if you need to change it so as to ask a further question. It makes it easier for us to follow what's changing.

                        Best of luck!

                        1 Reply Last reply
                        1
                        • S Offline
                          S Offline
                          SGaist
                          Lifetime Qt Champion
                          wrote on 8 Dec 2017, 20:17 last edited by
                          #12

                          Just in case, QSqlTableModel and derivatives are QSqlQueryModel with writing added so depending on your needs it might still be an option.

                          Interested in AI ? www.idiap.ch
                          Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

                          J 1 Reply Last reply 8 Dec 2017, 22:31
                          0
                          • S SGaist
                            8 Dec 2017, 20:17

                            Just in case, QSqlTableModel and derivatives are QSqlQueryModel with writing added so depending on your needs it might still be an option.

                            J Offline
                            J Offline
                            JonB
                            wrote on 8 Dec 2017, 22:31 last edited by SGaist 12 Sept 2017, 22:01
                            #13

                            @SGaist
                            Just how much of the QSqlQueryModel-ness can you actually use with QSqlTableModel, in practice?

                            The key here is http://doc.qt.io/qt-5/qsqltablemodel.html#setQuery :

                            This function simply calls QSqlQueryModel::setQuery(query). You should normally not call it on a QSqlTableModel. Instead, use setTable(), setSort(), setFilter(), etc., to set up the query.

                            From his own QSqlQueryModel, the OP can afford to pass in the arbitrary query he shows, if he wants. But once he moves to QSqlTableModel he really needs to be using the setTable() level instead, doesn't he? Certainly if he wants to do modification statements.

                            [edit: Fixed class name SGaist]

                            1 Reply Last reply
                            0
                            • S Offline
                              S Offline
                              SGaist
                              Lifetime Qt Champion
                              wrote on 9 Dec 2017, 22:02 last edited by
                              #14

                              Yes that's right. But he can also add the write layer himself like it has been done for QSqlTableModel and others..

                              Interested in AI ? www.idiap.ch
                              Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

                              1 Reply Last reply
                              0

                              1/14

                              5 Dec 2017, 15:20

                              • Login

                              • Login or register to search.
                              1 out of 14
                              • First post
                                1/14
                                Last post
                              0
                              • Categories
                              • Recent
                              • Tags
                              • Popular
                              • Users
                              • Groups
                              • Search
                              • Get Qt Extensions
                              • Unsolved