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 and complex queries
Forum Updated to NodeBB v4.3 + New Features

QSqlRelationalTableModel and complex queries

Scheduled Pinned Locked Moved Solved General and Desktop
28 Posts 4 Posters 8.2k Views 1 Watching
  • 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.
  • VRoninV Offline
    VRoninV Offline
    VRonin
    wrote on last edited by
    #13

    I'd suggest a QHash<quint64,QVariant> where the key has the most significant 32 bits as the row index and the least significant 32 as the column

    "La mort n'est rien, mais vivre vaincu et sans gloire, c'est mourir tous les jours"
    ~Napoleon Bonaparte

    On a crusade to banish setIndexWidget() from the holy land of Qt

    1 Reply Last reply
    1
    • P Offline
      P Offline
      Panoss
      wrote on last edited by Panoss
      #14

      I 've made this function: (I call it of course, like this: model->setQuery("SELECT id, name FROM parts"))

       void TableModel::setQuery(const QSqlQuery &qry) {
           query = qry;
       }
      

      I suppose this is not enough for the model to get the data from db and then be available (I mean the data from db) to the model? (a QAbstractTableModel)
      Must I do something more?

      And this is the data function:

       QVariant TableModel::data(const QModelIndex &index, int role) const
       {
           if (!index.isValid())
               return QVariant();
           if (index.row() >= query.size())
               return QVariant();
           if (role == Qt::DisplayRole) {
               query.seek(index.row());
               return query.value(index.column()); 
           } else {
               return QVariant();
           }
      }
      

      Where I get an error:
      ...\tablemodel.cpp:74: error: C2662: 'QSqlQuery::seek' : cannot convert 'this' pointer from 'const QSqlQuery' to 'QSqlQuery &'
      Conversion loses qualifiers

      It's at the line 'query.seek(index.row());'.

      1 Reply Last reply
      0
      • SGaistS Offline
        SGaistS Offline
        SGaist
        Lifetime Qt Champion
        wrote on last edited by
        #15

        How did you declared your query member variable ?

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

        P 1 Reply Last reply
        0
        • SGaistS SGaist

          How did you declared your query member variable ?

          P Offline
          P Offline
          Panoss
          wrote on last edited by Panoss
          #16

          private:

           QSqlQuery query;
          

          I read from this:
          "get rid of the const with const_cast"

          What does this mean?

          1 Reply Last reply
          0
          • VRoninV Offline
            VRoninV Offline
            VRonin
            wrote on last edited by VRonin
            #17

            "get rid of the const with const_cast"

            NOOOOOOOOOOOOOOOOOOO!!!!
            mutable QSqlQuery query; solves your compilation issue but I don't think your logic is going anywhere.

            Just a second here. let's take a step back: what do you want to achieve?

            • does the model need to be editable and changes be reflected in the database?
            • will you have more than 1 column?

            "La mort n'est rien, mais vivre vaincu et sans gloire, c'est mourir tous les jours"
            ~Napoleon Bonaparte

            On a crusade to banish setIndexWidget() from the holy land of Qt

            1 Reply Last reply
            0
            • P Offline
              P Offline
              Panoss
              wrote on last edited by Panoss
              #18

              I want my QTableView to display data from two tables.
              This in SQL means my model should have the ability to:

              1. make LEFT JOINS on the second table
              2. Concatenate two fields.

              For these things, QSqlQueryModel works fine.

              But I also need to add and remove records, which QSqlQueryModel cannot do.

              So, my conclusion is I should subclass QSqlQueryModel (this is what I'm doing right now, but I have problems...) and add custom functionality for adding and removing rows.

              What do you think?

              1 Reply Last reply
              0
              • VRoninV Offline
                VRoninV Offline
                VRonin
                wrote on last edited by
                #19

                Personally I would not subclass anything, I'd use a QStandardItemModel fill it with a QSqlQuery and react to QStandardItemModel::rowsInserted and QStandardItemModel::rowsRemoved signals to trigger the update in the db via another QSqlQuery

                "La mort n'est rien, mais vivre vaincu et sans gloire, c'est mourir tous les jours"
                ~Napoleon Bonaparte

                On a crusade to banish setIndexWidget() from the holy land of Qt

                1 Reply Last reply
                0
                • P Offline
                  P Offline
                  Panoss
                  wrote on last edited by Panoss
                  #20

                  The problem is I don't have the slightest idea how to do all this.
                  Is there some example I could look at?

                  EDIT: QStandardItemModel has no setQuery function.
                  How shall I "fill it with a QSqlQuery"?
                  You mean wit QStandardItemModel::setItem?

                  VRoninV 1 Reply Last reply
                  0
                  • P Panoss

                    The problem is I don't have the slightest idea how to do all this.
                    Is there some example I could look at?

                    EDIT: QStandardItemModel has no setQuery function.
                    How shall I "fill it with a QSqlQuery"?
                    You mean wit QStandardItemModel::setItem?

                    VRoninV Offline
                    VRoninV Offline
                    VRonin
                    wrote on last edited by VRonin
                    #21

                    @Panoss said in QSqlRelationalTableModel and complex queries:

                    How shall I "fill it with a QSqlQuery"

                    manually, outside the model

                        model->removeRows(0, model->rowCount());
                        model->removeColumns(0, model->columnCount());
                        QSqlQuery testQuery;
                        testQuery.prepare("select * from MyTable");
                        if (testQuery.exec()) {
                            for (bool firstRun = true; testQuery.next();) {
                                const QSqlRecord currRecord = testQuery.record();
                                if (firstRun) {
                                    firstRun = false;
                                    model->insertColumns(0, currRecord.count());
                                    for (int i = 0; i < currRecord.count(); ++i)
                                        model->setHeaderData(i, Qt::Horizontal, currRecord.fieldName(i));
                                }
                                const int newRow = model->rowCount();
                                model->insertRow(newRow);
                                for (int i = 0; i < currRecord.count(); ++i)
                                    model->setData(model->index(newRow, i), currRecord.value(i));
                            }
                        }
                    

                    "La mort n'est rien, mais vivre vaincu et sans gloire, c'est mourir tous les jours"
                    ~Napoleon Bonaparte

                    On a crusade to banish setIndexWidget() from the holy land of Qt

                    1 Reply Last reply
                    2
                    • P Offline
                      P Offline
                      Panoss
                      wrote on last edited by Panoss
                      #22

                      Works, the model->removeRow works, it removes a row from the model ,but how do I remove this row from the db too? Something like this?

                      QSqlQuery query;
                          query.prepare("DELETE FROM MyTable WHERE id=1");
                          if (query.exec()) {
                      etc. etc.
                      
                      1 Reply Last reply
                      0
                      • SGaistS Offline
                        SGaistS Offline
                        SGaist
                        Lifetime Qt Champion
                        wrote on last edited by
                        #23

                        You'll likely have to make a more precise delete query but otherwise, yes.

                        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
                        • P Offline
                          P Offline
                          Panoss
                          wrote on last edited by
                          #24

                          What do you mean by "more precise"? I thought "WHERE id=1" makes it absolutely precise (the value "1" was chosen for the shake of the example).

                          1 Reply Last reply
                          0
                          • SGaistS Offline
                            SGaistS Offline
                            SGaist
                            Lifetime Qt Champion
                            wrote on last edited by
                            #25

                            Sorry, that was badly written, I meant that you would have to ensure that you are passing the correct parameter(s) to the delete query to ensure you are deleting the same row in the database.

                            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
                            • P Offline
                              P Offline
                              Panoss
                              wrote on last edited by
                              #26

                              Ah, ok, guys, VRonin, SGaist, thank you very much.

                              1 Reply Last reply
                              0
                              • VRoninV Offline
                                VRoninV Offline
                                VRonin
                                wrote on last edited by VRonin
                                #27

                                be careful here as if you naively connect the database delete to rowsRemoved the first two lines:
                                (model->removeRows(0, model->rowCount()); model->removeColumns(0, model->columnCount());)
                                will delete your entire database and you cannot just use a QSignalBlocker on the model either or the view won't update

                                "La mort n'est rien, mais vivre vaincu et sans gloire, c'est mourir tous les jours"
                                ~Napoleon Bonaparte

                                On a crusade to banish setIndexWidget() from the holy land of Qt

                                1 Reply Last reply
                                1
                                • P Panoss

                                  I was using QSqlRelationalTableModel for making the model of a QTableView.
                                  But I had to had some complex queries (LEFT JOINS, plus concatenating fields) (can I concatenate fields with QSqlRelationalTableModel? I think not).
                                  But, QSqlRelationalTableModel cannot accept queries (the ->setQuery is private), only the ->setTable() works.
                                  So I replaced QSqlRelationalTableModel with QSqlQueryModel.

                                  Is this the best approach or am I missing something?

                                  kkmspbK Offline
                                  kkmspbK Offline
                                  kkmspb
                                  wrote on last edited by kkmspb
                                  #28

                                  @Panoss Hi! I solved this problem. You can extended Qt sources. First you change QSqlRelationalTableModel in QSqlTabelModel style, i.e. you create qsqlrelationaltabelmodel_p.h file. Afterwards you can derive from QSqlRelationalTableModel and you wil get access to private data.

                                  Than you rebuild qt souces.

                                  Only you needs reimplement selectStatement.
                                  (Sorry for my english). I'v done this on qt 4.8.1.

                                  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