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. Using QSqlQueryModel instead of QStandardItemModel
Forum Updated to NodeBB v4.3 + New Features

Using QSqlQueryModel instead of QStandardItemModel

Scheduled Pinned Locked Moved Solved General and Desktop
11 Posts 3 Posters 3.8k Views 2 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.
  • JonBJ Offline
    JonBJ Offline
    JonB
    wrote on last edited by JonB
    #1

    I have inherited a mess of an application which uses a variety of QStandardItemModel/QSqlQueryModel/QSqlTableModel/QSqlRelationalTableModel (probably written by different people over different times) for its database queries. Typically the data is presented in a QTreeView (please don't ask why it's not a QTableView).

    I come from a rather different background from Qt's table-models and views, so my expectations may be a bit different from what Qt provides.

    I feel like doing a rewrite so that at least QStandardItemModel is replaced by one of the QSql... types, since they all access SQL data. I feel that would be more consistent/maintainable. At present I am looking at an area like:

            model = QStandardItemModel(0, LetterColumns.ColumnCount, self)
            model.setHorizontalHeaderLabels(["Letter Code", "Type", "Description"])
    
            # dataQuerier.getAllLetters() is a utility function
            # which queries the database with a "SELECT" and returns all columns
            # via some "daos" module, I think
            for letter in self.dataQuerier.getAllLetters():
                model.insertRow(0)
                model.setData(model.index(0, LetterColumns.LetterCode), letter.ltCode)
                model.setData(model.index(0, LetterColumns.LtType), letterTypeMap[letter.type])
                ...
    

    I do not like the principle of populating all rows in QStandardItemModel when QSqlQueryModel would do this. So I'd like to change this over to QSqlQueryModel (at minimum, might even go for QSqlTableModel).

    I'm confused over where Qt would like me to put some stuff between the model and the view. So....

    • A QTreeView attached to a QSqlQueryModel shows all columns from the query. To show just 3, I seem to have to iterate all columns with QTreeView.setColumHidden(col, False) and then QTreeView.setColumHidden(col, True) on just those I want to achieve a generic solution corresponding to the QStandardItemModel code above which imports just the desired columns into the model for viewing. Is that right?

    • The above uses QStandardItemModel.setHorizontalHeaderLabels(["Letter Code", "Type", "Description"]) to simply set 3 visible columns on the model (which has many more). For QSqlQueryModel I seem to have found I need to call model.setHeaderData(0, QtCore.Qt.Horizontal, "Letter Code") for each column to achieve same effect. Is that right? Or, should I be trying to do it by some method of the QTreeView view instead of on the model? Or, if I use QSqlQueryModel I guess I could write my own SELECT query to return only the columns I want (but not if I go for QSqlTableModel as I believe that pulls all columns from the specified table)?

    • You will see that one column's rows are model.setData(model.index(0, LetterColumns.LtType), letterTypeMap[letter.type]). This is a mapping (via in-memory data) from the database table value to the desired visual value. I'm afraid it cannot be changed to get the mapped value at the SQL query side. I can see this presents a bit of a problem using QSqlQueryModel, which is designed for data read from the database, rather than QStandardItemModel, which is effectively more flexible. How best to achieve? I don't think QSqlQueryModel allows me to append a column with my own values after the SELECT has returned, which might have been a "dirty" way? Or, am I supposed to override QSqlQueryModel.data()/setData() to do the mapping, putting the onus on the model? In my world, this would have been achieved via the view QTreeView having an "output-mapping" function which mapped values for desired columns as it rendered; it feels more like View functionality than Model?

    If you have been kind enough to read through the above, do you think it is worth my pursuing this change from QStandardItemModel to a QSql...Model architecture, or am I just making more trouble than it is worth?

    Thank you! :)

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

      Hi,

      First thing to keep in mind: QSqlQueryModel is read-only but there are examples to make it writable.

      If you want to filter things or modify what is shown by the view, a QSortFilterProxyModel might be of interest.

      There are several ways to customise the header views content depending on what view you are using to show your table data.

      All in all, no, you're not crazy, but I recommend starting small and modify the models gradually.

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

      JonBJ 1 Reply Last reply
      1
      • SGaistS SGaist

        Hi,

        First thing to keep in mind: QSqlQueryModel is read-only but there are examples to make it writable.

        If you want to filter things or modify what is shown by the view, a QSortFilterProxyModel might be of interest.

        There are several ways to customise the header views content depending on what view you are using to show your table data.

        All in all, no, you're not crazy, but I recommend starting small and modify the models gradually.

        JonBJ Offline
        JonBJ Offline
        JonB
        wrote on last edited by JonB
        #3

        @SGaist
        OK, thank you.

        Then let's take one specific problem:

        The columns which come back from the database from a SELECT are insufficient for me. I need an extra column (at least for display), whose value is calculated in Qt code based on one of the columns which is returned. In the code above this model.setData(model.index(0, LetterColumns.LtType), letterTypeMap[letter.type]).

        Can I do this by adding my own column to the data/table returned from SQL query? (I don't think so.) Can I do this in QTree/TableView? i.e. have the view have its own extra column beyond what is the model? (Again, perhaps not?) Or else I seem to be stuck....

        VRoninV 1 Reply Last reply
        0
        • JonBJ JonB

          @SGaist
          OK, thank you.

          Then let's take one specific problem:

          The columns which come back from the database from a SELECT are insufficient for me. I need an extra column (at least for display), whose value is calculated in Qt code based on one of the columns which is returned. In the code above this model.setData(model.index(0, LetterColumns.LtType), letterTypeMap[letter.type]).

          Can I do this by adding my own column to the data/table returned from SQL query? (I don't think so.) Can I do this in QTree/TableView? i.e. have the view have its own extra column beyond what is the model? (Again, perhaps not?) Or else I seem to be stuck....

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

          There's a proxy model for that: KExtraColumnsProxyModel

          "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

          JonBJ 1 Reply Last reply
          2
          • VRoninV VRonin

            There's a proxy model for that: KExtraColumnsProxyModel

            JonBJ Offline
            JonBJ Offline
            JonB
            wrote on last edited by JonB
            #5

            @VRonin said in Using QSqlQueryModel instead of QStandardItemModel:

            There's a proxy model for that: KExtraColumnsProxyModel

            Oohh --- thank you! That's a third-party package I'd need to install and distribute/have end-users install, which I'm not dead keen on.... Does that imply that there is indeed no in-built facility for my extra column in Qt alone?

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

              There is not but that particular class can be just copy pasted and just adding a few lines to your license (that class comes from KDE which uses LGPL). No need to compile/distribute external binaries

              "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

              JonBJ 2 Replies Last reply
              1
              • VRoninV VRonin

                There is not but that particular class can be just copy pasted and just adding a few lines to your license (that class comes from KDE which uses LGPL). No need to compile/distribute external binaries

                JonBJ Offline
                JonBJ Offline
                JonB
                wrote on last edited by
                #7

                @VRonin Thank you, that is useful, I will indeed investigate.

                1 Reply Last reply
                0
                • VRoninV VRonin

                  There is not but that particular class can be just copy pasted and just adding a few lines to your license (that class comes from KDE which uses LGPL). No need to compile/distribute external binaries

                  JonBJ Offline
                  JonBJ Offline
                  JonB
                  wrote on last edited by
                  #8

                  @VRonin
                  I have now had a chance to investigate KExtraColumnsProxyModel.

                  This seems like what I was looking for, and I will mark this post as solved.

                  However, unfortunately it has made me realise I cannot use any third-party code for Qt. It all tends to be written in C++ (understandably). I am using PyQt (not my choice!). Which leaves me with:

                  • I don't fancy introducing C++ code which I need to compile for my multi-platform targets.
                  • Even if I did, I don't think you can auto-generate PyQt/Python bindings.
                  • And looking at that code I don't fancy rewriting it natively in Python.

                  Unless there are any PyQt/Python experts here? Which is all rather a shame going forward, as it limits me to native Qt-supplied solutions... :(

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

                    You can use sip to create the bindings you need for that class.

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

                    JonBJ 1 Reply Last reply
                    1
                    • SGaistS SGaist

                      You can use sip to create the bindings you need for that class.

                      JonBJ Offline
                      JonBJ Offline
                      JonB
                      wrote on last edited by JonB
                      #10

                      @SGaist
                      I'd be obliged if you would comment on the following:

                      I did look at sip, and more specifically https://riverbankcomputing.com/pipermail/pyqt/2016-May/037557.html, and https://steveire.wordpress.com/2016/05/18/generating-python-bindings-with-clang/ which discusses something like clang->sip to generate Python bindings for C++. The gist was that the source had to be annotated in a particular way (via XML, "rules files"??) to allow it to work, and even then there were issues and it needed some manual help. So it did not look straightforward?

                      Even if it does not apply here, I would like to understand whether generating PyQt/Python bindings from arbitrary C++ source is a totally simple, automated process or whether in practice it's not so simple? Thanks.

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

                        Looks pretty interesting and I'd say worth a shot.

                        Well, the proposition of Steve makes it automated. Otherwise take a look at the PyMyLabel project and the sip example, the class you need isn't overly complicated so writing the corresponding sip files wouldn't be that complex.

                        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

                        • Login

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