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 can I modify the default SELECT statement used by QSqlRelationalTableModel
Forum Updated to NodeBB v4.3 + New Features

How can I modify the default SELECT statement used by QSqlRelationalTableModel

Scheduled Pinned Locked Moved General and Desktop
9 Posts 4 Posters 6.3k 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.
  • J Offline
    J Offline
    Jonathan
    wrote on last edited by
    #1

    SQLite doesn't have a date type, but has lots of convenience methods for converting strings and integers to dates, times, etc.

    I can get the SELECT statement used by QSqlRelationalTableModel, (using QSqlRelationalTableModel::selectStatement()) but I can't see a way to set it.

    For instance I want to change something like:
    SELECT employee."id", employee."startDate" FROM employee ORDER BY employee."id" ASC

    to
    SELECT employee."id", date(employee."startDate") FROM employee ORDER BY employee."id" ASC

    I could create my own SQL model, but then it would be a lot of work to provide the necessary editing and relational support.

    kkmspbK 1 Reply Last reply
    0
    • G Offline
      G Offline
      goetz
      wrote on last edited by
      #2

      "QSqlQueryModel":http://doc.qt.nokia.com/stable/qsqlquerymodel.html provides "setQuery() methods":http://doc.qt.nokia.com/stable/qsqlquerymodel.html#setQuery

      QSqlTableModel and QSqlRelationalTableModel both inherit this class, so the methods are available on this level too.

      http://www.catb.org/~esr/faqs/smart-questions.html

      1 Reply Last reply
      1
      • J Offline
        J Offline
        Jonathan
        wrote on last edited by
        #3

        I've thought about setQuery(), however:

        (1) The documentation states "You should normally not call it on a QSqlTableModel. Instead, use setTable(), setSort(), setFilter(), etc., to set up the query"

        (2) There are two setQuery() methods. I had assumed that both execute immediately. Does

        @void QSqlQueryModel::setQuery ( const QSqlQuery & query )@

        defer execution until select() is called? How would I create the QSqlQuery argument?

        1 Reply Last reply
        0
        • F Offline
          F Offline
          fcrochik
          wrote on last edited by
          #4

          Just create an instance of QSqlQuery and pass the "SQL" statement in the constructor.

          The idea behind QSqlRelationalTableModel and QSqlTableModel is to not have to mess with the SQL side of things. If you only want a readonly model you probably should use "QSqlQueryModel":http://doc.qt.nokia.com/latest/qsqlquerymodel.html

          Depending on how you change the select statement, I assume, you may get errors when trying to add/update records.

          Certified Specialist & Qt Ambassador <a href="http://www.crochik.com">Maemo, Meego, Symbian, Playbook, RaspberryPi, Desktop... Qt everywhere!</a>

          1 Reply Last reply
          0
          • J Offline
            J Offline
            Jonathan
            wrote on last edited by
            #5

            Hello fcrochik, I'm not sure what you are proposing. If I create an instance of QSqlQuery and pass the “SQL” statement in the constructor, the statement is executed immediately (see the documentation).

            I'm using QSqlRelationalTableModel because I want an updateable view (and foreign key support), not just a read-only view.

            Rewording my original problem, I'm trying to "intercept" the SELECT statement used by QSqlRelationalTableModel so that I can modify it slightly.

            1 Reply Last reply
            0
            • J Offline
              J Offline
              Jonathan
              wrote on last edited by
              #6

              I have the answer. Inevitably I was being a bit slow.

              I'm already subclassing QSqlRelationalTableModel. All I had to do was override selectStatement() and return a modified version of the select statement returned by the parent class.

              Here's my test code:

              @QString SqlCustomModel::selectStatement() const
              {
              QString query = QSqlRelationalTableModel::selectStatement();
              query = query.replace(QString("""), QString()); // makes the next step easier
              query = query.replace(QString("employee.startDate"), QString("date(employee.startDate)"));
              return query;
              }@

              1 Reply Last reply
              0
              • F Offline
                F Offline
                fcrochik
                wrote on last edited by
                #7

                [quote author="Jonathan" date="1293565515"]Hello fcrochik, I'm not sure what you are proposing. If I create an instance of QSqlQuery and pass the “SQL” statement in the constructor, the statement is executed immediately (see the documentation).

                I'm using QSqlRelationalTableModel because I want an updateable view (and foreign key support), not just a read-only view.

                Rewording my original problem, I'm trying to "intercept" the SELECT statement used by QSqlRelationalTableModel so that I can modify it slightly.[/quote]

                It is not a problem that the query is executed as long as it is not "forward only". See "documentation":http://doc.qt.nokia.com/latest/qsqlquerymodel.html#setQuery

                Certified Specialist & Qt Ambassador <a href="http://www.crochik.com">Maemo, Meego, Symbian, Playbook, RaspberryPi, Desktop... Qt everywhere!</a>

                1 Reply Last reply
                0
                • F Offline
                  F Offline
                  fcrochik
                  wrote on last edited by
                  #8

                  Jonathan:
                  A better question is why don't you use the "other setQuery overload":http://doc.qt.nokia.com/latest/qsqlquerymodel.html#setQuery-2 that will take a SQL argument?

                  Certified Specialist & Qt Ambassador <a href="http://www.crochik.com">Maemo, Meego, Symbian, Playbook, RaspberryPi, Desktop... Qt everywhere!</a>

                  1 Reply Last reply
                  0
                  • J Jonathan

                    SQLite doesn't have a date type, but has lots of convenience methods for converting strings and integers to dates, times, etc.

                    I can get the SELECT statement used by QSqlRelationalTableModel, (using QSqlRelationalTableModel::selectStatement()) but I can't see a way to set it.

                    For instance I want to change something like:
                    SELECT employee."id", employee."startDate" FROM employee ORDER BY employee."id" ASC

                    to
                    SELECT employee."id", date(employee."startDate") FROM employee ORDER BY employee."id" ASC

                    I could create my own SQL model, but then it would be a lot of work to provide the necessary editing and relational support.

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

                    @Jonathan Hello! You can reimplement selectStatement() (this is a virtual function).

                    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