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. QSqlRelationalTableModel get relation foreign key value
QtWS25 Last Chance

QSqlRelationalTableModel get relation foreign key value

Scheduled Pinned Locked Moved Solved General and Desktop
qsqlqsqlrelationqsqltablemodel
8 Posts 2 Posters 5.7k 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.
  • M Offline
    M Offline
    maurosanjo
    wrote on last edited by maurosanjo
    #1

    I have a QSqlRelationalTableModel that has a relation defined that maps a foreign key to a display column.

    for instance:

    QSqlRelationalTableModel *myModel = new QSqlRelationalTableModel(this);
    myModel->setTable("address");
    myModel->setEditStrategy(QSqlTableModel::OnManualSubmit);
    myModel->setRelation(myModel->fieldIndex("id_city"), QSqlRelation("cities","id_city","name"));
    

    I could not find any reasonable solution to just get the foreign key value when retrieving a record data.

    When I insert records (using insertRecord), berfore submitting the changes to DB, the value returned by display column name "name" is the foreign key value of "id_city".

    QSqlRecord record = myModel->record(row);
    // Returns the FK value before submitting new records
    QString val = record.value("name").toString();
    

    After submitting them, the behaviour is returning the city name, and no access to the foreign key.

    Imagine I do not want to go through relationalModel records looking for the city name, because i do not guarantee unique names for whatever reason.

    This should be pretty straightforward as the whole Qt Framework, but I could not find it...

    kshegunovK 1 Reply Last reply
    1
    • M maurosanjo

      I have a QSqlRelationalTableModel that has a relation defined that maps a foreign key to a display column.

      for instance:

      QSqlRelationalTableModel *myModel = new QSqlRelationalTableModel(this);
      myModel->setTable("address");
      myModel->setEditStrategy(QSqlTableModel::OnManualSubmit);
      myModel->setRelation(myModel->fieldIndex("id_city"), QSqlRelation("cities","id_city","name"));
      

      I could not find any reasonable solution to just get the foreign key value when retrieving a record data.

      When I insert records (using insertRecord), berfore submitting the changes to DB, the value returned by display column name "name" is the foreign key value of "id_city".

      QSqlRecord record = myModel->record(row);
      // Returns the FK value before submitting new records
      QString val = record.value("name").toString();
      

      After submitting them, the behaviour is returning the city name, and no access to the foreign key.

      Imagine I do not want to go through relationalModel records looking for the city name, because i do not guarantee unique names for whatever reason.

      This should be pretty straightforward as the whole Qt Framework, but I could not find it...

      kshegunovK Offline
      kshegunovK Offline
      kshegunov
      Moderators
      wrote on last edited by
      #2

      @maurosanjo
      Hello,
      Is the footnote on the relational model page what you're looking for. If I understand it correctly you can retrieve the fields you're interested in by using properly aliased name.

      Kind regards.

      Read and abide by the Qt Code of Conduct

      M 1 Reply Last reply
      0
      • kshegunovK kshegunov

        @maurosanjo
        Hello,
        Is the footnote on the relational model page what you're looking for. If I understand it correctly you can retrieve the fields you're interested in by using properly aliased name.

        Kind regards.

        M Offline
        M Offline
        maurosanjo
        wrote on last edited by maurosanjo
        #3

        @kshegunov
        Hello,

        Unfortunatelly that is not case. If I get a QSqlRecord from the QSqlRelationalTableModel and iterate through the fields and show their names, it only outputs the table columns changing the foreign key column to the display column defined on the relation (in this example "name" in place of "id_city").

        If I check the column count on the QSqlRelationalTableModel it gives the exact number of columns on the "main" table, and no aliased field from the related table.

        I believe that note is talking about aliasing fields when the relations created have equal column names, which is not the case,

        What I need is pretty simple, I create a QSqlRealtionalTableModel with one relation, and for each record i want to retrieve the foreing key value for the display column.

        Regards

        kshegunovK 1 Reply Last reply
        0
        • M maurosanjo

          @kshegunov
          Hello,

          Unfortunatelly that is not case. If I get a QSqlRecord from the QSqlRelationalTableModel and iterate through the fields and show their names, it only outputs the table columns changing the foreign key column to the display column defined on the relation (in this example "name" in place of "id_city").

          If I check the column count on the QSqlRelationalTableModel it gives the exact number of columns on the "main" table, and no aliased field from the related table.

          I believe that note is talking about aliasing fields when the relations created have equal column names, which is not the case,

          What I need is pretty simple, I create a QSqlRealtionalTableModel with one relation, and for each record i want to retrieve the foreing key value for the display column.

          Regards

          kshegunovK Offline
          kshegunovK Offline
          kshegunov
          Moderators
          wrote on last edited by
          #4

          @maurosanjo

          If I get a QSqlRecord from the QSqlRelationalTableModel and iterate through the fields and show their names, it only outputs the table columns changing the foreign key column to the display column defined on the relation (in this example "name" in place of "id_city").

          I see, but what about retrieving the data directly from the model, not passing through QSqlRecord at all?

          I believe that note is talking about aliasing fields when the relations created have equal column names, which is not the case,

          Yes, but also it mentions (joined) table names are aliased. That's what I was referring to when I suggested the note.

          What I need is pretty simple, I create a QSqlRealtionalTableModel with one relation, and for each record i want to retrieve the foreing key value for the display column.

          That is the integer (presumably) that's declared as a foreign key, correct?

          Kind regards.

          Read and abide by the Qt Code of Conduct

          M 1 Reply Last reply
          0
          • kshegunovK kshegunov

            @maurosanjo

            If I get a QSqlRecord from the QSqlRelationalTableModel and iterate through the fields and show their names, it only outputs the table columns changing the foreign key column to the display column defined on the relation (in this example "name" in place of "id_city").

            I see, but what about retrieving the data directly from the model, not passing through QSqlRecord at all?

            I believe that note is talking about aliasing fields when the relations created have equal column names, which is not the case,

            Yes, but also it mentions (joined) table names are aliased. That's what I was referring to when I suggested the note.

            What I need is pretty simple, I create a QSqlRealtionalTableModel with one relation, and for each record i want to retrieve the foreing key value for the display column.

            That is the integer (presumably) that's declared as a foreign key, correct?

            Kind regards.

            M Offline
            M Offline
            maurosanjo
            wrote on last edited by
            #5

            @kshegunov
            Hello,

            I also tried that, and looked at the select query from the model, and as i said the id column "id_city" is used only in the WHERE clause, in the select it is replaced by the display column "name".

            Doing some tests I found a way to add the foreign key but this creates problems when adding new records...

            Taking a look at the select query, Qt does not include " on the field name of the display column, allowing do to something like this:

            myModel->setRelation(myModel->fieldIndex("id_city"), QSqlRelation("cities", "id", "name, relTblAl_2.id_city"));
            

            After select the query shows the additional foreing key column, and you're able to manipulate it record by record.

            Still investigating if it is possible to overcome the problems when inserting records. But this is not a proper solution, this info should be easily retrieved,

            kshegunovK 1 Reply Last reply
            0
            • M maurosanjo

              @kshegunov
              Hello,

              I also tried that, and looked at the select query from the model, and as i said the id column "id_city" is used only in the WHERE clause, in the select it is replaced by the display column "name".

              Doing some tests I found a way to add the foreign key but this creates problems when adding new records...

              Taking a look at the select query, Qt does not include " on the field name of the display column, allowing do to something like this:

              myModel->setRelation(myModel->fieldIndex("id_city"), QSqlRelation("cities", "id", "name, relTblAl_2.id_city"));
              

              After select the query shows the additional foreing key column, and you're able to manipulate it record by record.

              Still investigating if it is possible to overcome the problems when inserting records. But this is not a proper solution, this info should be easily retrieved,

              kshegunovK Offline
              kshegunovK Offline
              kshegunov
              Moderators
              wrote on last edited by
              #6

              @maurosanjo
              The only other thing I could think of is to use something like this:

              int relationIndex = myModel->fieldIndex("id_city");
              QVariant idCity = myModel->relationModel(relationIndex)->record(row).value("id_city");
              

              But it might not work, I'm just speculating ...

              Kind regards.

              Read and abide by the Qt Code of Conduct

              1 Reply Last reply
              0
              • M Offline
                M Offline
                maurosanjo
                wrote on last edited by
                #7

                @kshegunov

                This won't work because the relational Model is just a QSqlTableModel pointing to the reference table, so it fetches all the records from that table not linking with the QSqlRelationalModel.

                I did not succeed trying with QSqlRelationalModel. I had to extend QSqlTableModel reimplementing columnCount, data, flags and headerData, to add new columns and personalize the columns data when populated with the model.

                For some reason I could not do that extending QSortFilterProxyModel. the columns where always empty.

                Regards

                kshegunovK 1 Reply Last reply
                0
                • M maurosanjo

                  @kshegunov

                  This won't work because the relational Model is just a QSqlTableModel pointing to the reference table, so it fetches all the records from that table not linking with the QSqlRelationalModel.

                  I did not succeed trying with QSqlRelationalModel. I had to extend QSqlTableModel reimplementing columnCount, data, flags and headerData, to add new columns and personalize the columns data when populated with the model.

                  For some reason I could not do that extending QSortFilterProxyModel. the columns where always empty.

                  Regards

                  kshegunovK Offline
                  kshegunovK Offline
                  kshegunov
                  Moderators
                  wrote on last edited by
                  #8

                  @maurosanjo
                  Well, I'm all out of ideas, sorry.

                  Read and abide by the Qt Code of Conduct

                  1 Reply Last reply
                  0

                  • Login

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