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. How to use QSortFilterProxyModel + QSqlTableModel to limit rows?
Forum Updated to NodeBB v4.3 + New Features

How to use QSortFilterProxyModel + QSqlTableModel to limit rows?

Scheduled Pinned Locked Moved General and Desktop
7 Posts 6 Posters 5.9k 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.
  • S Offline
    S Offline
    soroush
    wrote on last edited by
    #1

    Hi

    I'm playing with QtSQL to learn more about. Just created a MySQL database with 100000 rows (foo, bar, baz). Simply trying to load whole table into a QSqlTableModel, the gui refuses to response (normally). I wonder it's possible to use a QSortFilterProxyModel to apply SQL limit on resulting set of query or not?

    Well, that won't help much because data is actually loaded into original model. I should append LIMIT statement to select phrase of original model. Though I wonder if it's possible to get something like "paging" with a proxy or not? You know, first n rows in page 1, second n rows on page 2, ... and then may control pages over a nice GUI navigator.

    1 Reply Last reply
    0
    • L Offline
      L Offline
      lgeyer
      wrote on last edited by
      #2

      QSqlTableModel (more specifically QSqlQueryModel) does automatically limit the number of fetched rows (to currently 255).

      Can you share some code which reproduces the behavior?

      1 Reply Last reply
      0
      • S Offline
        S Offline
        soroush
        wrote on last edited by
        #3

        [quote author="Lukas Geyer" date="1358791758"]QSqlTableModel (more specifically QSqlQueryModel) does automatically limit the number of fetched rows (to currently 255).

        Can you share some code which reproduces the behavior?[/quote]

        I didn't noticed about the 255 limit. I thought GUI can't show all results. Though that was too slow. Didn't profile exact time, but I can say it takes more than 2 seconds.

        Currently I don't have access to that code.

        1 Reply Last reply
        0
        • E Offline
          E Offline
          eJinn
          wrote on last edited by
          #4

          Hi, all!

          I want to continue this theme.

          [quote author="Lukas Geyer" date="1358791758"]QSqlTableModel (more specifically QSqlQueryModel) does automatically limit the number of fetched rows (to currently 255).[/quote]
          Unfortunately this is not quite true.
          It's true, if I'm using Firebird/iBase database and, accordingly, qsqlibase driver.
          But if I want use MySQL database, and qsqlmysql driver, QSqlQueryModel reads the entire database, in model setting:
          @ QTableView* view = new QTableView;
          QSqlQueryModel *model = new QSqlQueryModel;
          model->setQuery("SELECT * employee");
          view->setModel(model);
          view->show();
          @
          Model reads all my 300 000 rows in database and it takes about 10 seconds. My database is expected to have a size of 10 million records. It takes about 300 seconds, which is unacceptable.
          How to make sure that it reads not all at once, and in parts.

          Use MySQL - a prerequisite.

          1 Reply Last reply
          0
          • H Offline
            H Offline
            htettey
            wrote on last edited by
            #5

            Hi all,

            I have just run into the problem explain by ejin.
            Was there a resolution to this? I am using MySQL and my model reads all my records (about 100,000) . This is using about 1Gig of RAM.

            Please advise.

            Thank you

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

              The way I solved this is run the query manually and then inserting the values into a QStandardItemModel. The downside is that your DB might remain busy untill you read the entire query

              bool fillModel(QAbstractItemModel* model, QSqlQuery& query, unsigned int maxRows)
              {
                  // insert up to maxRows row in the model with data from query
                  for (unsigned int i = 0U; i < maxRows; ++i) {
                      if (!query.next())
                          return false;
                      const QSqlRecord currRec = query.record();
                      if (i == 0U) {
                          // Clear the model
                          model->removeRows(0, model->rowCount());
                          model->removeColumns(0, model->columnCount());
                          model->insertColumns(0, currRec.count());
                          for (int j = 0; j < currRec.count(); ++j)
                              model->setHeaderData(j, Qt::Horizontal, currRec.fieldName(j));
                      }
                      model->insertRow(i);
                      for (int j = 0; j < currRec.count(); ++j) {
                          model->setData(model->index(i, j), currRec.value(j));
                      }
                  }
                  return true;
              }
              

              to move to the next page call fillModel again. if the function returns false there are no more pages

              "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
              • M Offline
                M Offline
                Mark Ch
                wrote on last edited by Mark Ch
                #7

                I have been able to achieve this quite easily in Qt 5.7 by subclassing QSqlRelationalTableModel and reimplementing selectStatement() as

                QString NewModel::selectStatement() const
                {
                    return QSqlRelationalTableModel::selectStatement() + QString(" LIMIT 100");
                }
                

                The same should work for QSqlTableModel.

                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