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 with multiple Joins?
Forum Updated to NodeBB v4.3 + New Features

QSqlRelationalTableModel with multiple Joins?

Scheduled Pinned Locked Moved Unsolved General and Desktop
67 Posts 4 Posters 22.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.
  • devhobbyD Offline
    devhobbyD Offline
    devhobby
    wrote on last edited by
    #3

    @JonB said in QSqlRelationalTableModel with multiple Joins?:

    @devhobby
    So far as I know, you can only do the simple stuff through QSqlRelationalTableModel, i.e. just look up values by foreign key. It's pretty limited, and that's it.

    I shall watch this thread to see if you receive any better ideas....

    Yes! It's what I've been noticing...

    You can view it the way you want -> not editable
    You can edit the records -> queries are limited!

    I'm in front of this scary riddle, and can't find a compromise!

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

      You'll need to do it manually:

      • create a QSqlQuery that fetches what you want
      • iterate over the results and fill a QStandardItemModel with them
      • connect the QAbstractItemModel::dataChanged signal to a slot that runs an UPDATE/INSERT query on the db

      "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
      3
      • devhobbyD devhobby

        @JonB said in QSqlRelationalTableModel with multiple Joins?:

        @devhobby
        So far as I know, you can only do the simple stuff through QSqlRelationalTableModel, i.e. just look up values by foreign key. It's pretty limited, and that's it.

        I shall watch this thread to see if you receive any better ideas....

        Yes! It's what I've been noticing...

        You can view it the way you want -> not editable
        You can edit the records -> queries are limited!

        I'm in front of this scary riddle, and can't find a compromise!

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

        @devhobby

        QSqlRelationalTableModel is only QSqlTableModel with the addition of foreign key lookup of given columns into other tables, so that for example you can display comboboxes/labels for the looked-up values. So as I see it your issue is really at the QSqlTableModel level?

        QSqlTableModel really wants to map to an actual table (not just a SELECT statement) so that it can INSERT?DELETE/UPDATE. That's why editing can't be as flexible as SELECTing.

        I've just seen @VRonin has posted. To the effect that you do indeed need to do it manually --- you can only go the generic QSqlQuery level, not the QSqlTableModel one, and hence not QSqlRelationalTableModel either. Which is about what I found... :(

        1 Reply Last reply
        3
        • VRoninV VRonin

          You'll need to do it manually:

          • create a QSqlQuery that fetches what you want
          • iterate over the results and fill a QStandardItemModel with them
          • connect the QAbstractItemModel::dataChanged signal to a slot that runs an UPDATE/INSERT query on the db
          JonBJ Offline
          JonBJ Offline
          JonB
          wrote on last edited by JonB
          #6

          @VRonin

          You'll need to do it manually
          ...

          You are right, but he loses the QSqlRelationalTableModel foreign key look up features, and then has to write those for himself. As per my post above. Do you agree?

          [EDIT: Actually, the point is once you go down to QSqlQuery you lose all QSqlTableModel level of functionality, and that's what is irritating.]

          VRoninV 1 Reply Last reply
          3
          • JonBJ JonB

            @VRonin

            You'll need to do it manually
            ...

            You are right, but he loses the QSqlRelationalTableModel foreign key look up features, and then has to write those for himself. As per my post above. Do you agree?

            [EDIT: Actually, the point is once you go down to QSqlQuery you lose all QSqlTableModel level of functionality, and that's what is irritating.]

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

            That's correct. Although providing labels/comboboxes is easily implemented via a separate QSqlTableModel/QSqlQueryModel that has all the options and a delegate (in fact that's what QSqlRelationalDelegate does for QSqlRelationalTableModel).

            You'd need something like

            class RelationDelegate : public QStyledItemDelegate{
                Q_OBJECT
                Q_PROPERTY(QAbstractItemModel* relationModel READ relationModel WRITE setRelationModel NOTIFY relationModelChanged)
                Q_PROPERTY(int keyColumn READ keyColumn WRITE setKeyColumn NOTIFY keyColumnChanged)
                Q_PROPERTY(int valueColumn READ valueColumn WRITE setValueColumn NOTIFY valueColumnChanged)
                Q_PROPERTY(const QPersistentModelIndex& relationModelRoot READ relationModelRoot WRITE setRelationModelRoot NOTIFY relationModelRootChanged)
                Q_DISABLE_COPY(RelationDelegate)
            public:
                explicit RelationDelegate(QObject* parent = Q_NULLPTR)
                    :QStyledItemDelegate(parent)
                    , m_relModel(Q_NULLPTR)
                    , m_keyCol(0)
                    , m_valueCol(1)
                {
                    QObject::connect(this, &RelationDelegate::relationModelChanged, this, &RelationDelegate::viewNeedUpdate);
                    QObject::connect(this, &RelationDelegate::keyColumnChanged, this, &RelationDelegate::viewNeedUpdate);
                    QObject::connect(this, &RelationDelegate::valueColumnChanged, this, &RelationDelegate::viewNeedUpdate);
                    QObject::connect(this, &RelationDelegate::relationModelRootChanged, this, &RelationDelegate::viewNeedUpdate);
                }
                Q_SIGNAL void relationModelRootChanged(const QPersistentModelIndex& rootIdx);
                const QPersistentModelIndex& relationModelRoot() const { return m_relModelRoot; }
                void setRelationModelRoot(const QPersistentModelIndex& rootIdx)
                {
                    if (m_relModelRoot == rootIdx)
                        return;
                    Q_ASSERT(!rootIdx.isValid() || rootIdx.model()==m_relModel);
                    m_relModelRoot = rootIdx;
                    relationModelRootChanged(m_relModelRoot);
                }
                Q_SIGNAL void viewNeedUpdate();
                QAbstractItemModel* relationModel() const { return m_relModel; };
                Q_SIGNAL void relationModelChanged(QAbstractItemModel* relModel);
                void setRelationModel(QAbstractItemModel* relModel)
                {
                    if (relModel == m_relModel) 
                        return;
                    m_relModel = relModel;
                    relationModelChanged(m_relModel);
                }
                int keyColumn() const { return m_keyCol; }
                Q_SIGNAL void keyColumnChanged(int keyCol);
                void setKeyColumn(int keyCol)
                {
                    if (keyCol == m_keyCol)
                        return;
                    m_keyCol = keyCol;
                    keyColumnChanged(m_keyCol);
                }
                int valueColumn() const { return m_valueCol; }
                Q_SIGNAL void valueColumnChanged(int valueCol);
                void setValueColumn(int valueCol)
                {
                    if (valueCol == m_valueCol) 
                        return;
                    m_valueCol = valueCol;
                    valueColumnChanged(m_valueCol);
                }
                QString displayText(const QVariant& value, const QLocale& locale) const Q_DECL_OVERRIDE{
                    if (m_relModel) {
                        const int relModelRows = m_relModel->rowCount(m_relModelRoot);
                        for (int i = 0; i < relModelRows; ++i) {
                            if (m_relModel->index(i, m_keyCol, m_relModelRoot).data() == value)
                                return QStyledItemDelegate::displayText(m_relModel->index(i, m_valueCol, m_relModelRoot).data(), locale);
                        }
                    }
                    return QStyledItemDelegate::displayText(value, locale);
                }
                 QWidget* createEditor(QWidget *parent, const QStyleOptionViewItem &option, const QModelIndex &index) const Q_DECL_OVERRIDE{
                    if (!m_relModel) 
                        return QStyledItemDelegate::createEditor(parent, option, index);
                    QComboBox* editor = new QComboBox(parent);
                    editor->setModel(m_relModel);
                    editor->setRootModelIndex(m_relModelRoot);
                    editor->setModelColumn(m_valueCol);
                    return editor;
                }
                void setEditorData(QWidget *editor, const QModelIndex &index) const Q_DECL_OVERRIDE{
                    if (!m_relModel) 
                        return QStyledItemDelegate::setEditorData(editor, index);
                    QComboBox* editCombo = qobject_cast<QComboBox*>(editor);
                    Q_ASSERT(editCombo);
                    const int relModelRows = m_relModel->rowCount(m_relModelRoot);
                    for (int i = 0; i < relModelRows; ++i) {
                        if (m_relModel->index(i, m_keyCol, m_relModelRoot).data() == index.data()) {
                            editCombo->setCurrentIndex(i);
                            return;
                        }
                    }
                }
                void setModelData(QWidget *editor, QAbstractItemModel *model, const QModelIndex &index) const Q_DECL_OVERRIDE{
                    if (!m_relModel)
                        return QStyledItemDelegate::setModelData(editor, model, index);
                    QComboBox* editCombo = qobject_cast<QComboBox*>(editor);
                    Q_ASSERT(editCombo);
                    const int currIdx = editCombo->currentIndex();
                    model->setData(index, m_relModel->index(currIdx, m_keyCol, m_relModelRoot).data());
                }
                void updateEditorGeometry(QWidget *editor, const QStyleOptionViewItem &option, const QModelIndex &index) const Q_DECL_OVERRIDE{
                    if (!m_relModel)
                        return QStyledItemDelegate::updateEditorGeometry(editor, option, index);
                    editor->setGeometry(option.rect);
                }
                
            private:
                QAbstractItemModel* m_relModel;
                int m_keyCol;
                int m_valueCol;
                QPersistentModelIndex m_relModelRoot;
            };
            

            and connect the viewNeedUpdate to the update of the view

            "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
            3
            • VRoninV VRonin

              That's correct. Although providing labels/comboboxes is easily implemented via a separate QSqlTableModel/QSqlQueryModel that has all the options and a delegate (in fact that's what QSqlRelationalDelegate does for QSqlRelationalTableModel).

              You'd need something like

              class RelationDelegate : public QStyledItemDelegate{
                  Q_OBJECT
                  Q_PROPERTY(QAbstractItemModel* relationModel READ relationModel WRITE setRelationModel NOTIFY relationModelChanged)
                  Q_PROPERTY(int keyColumn READ keyColumn WRITE setKeyColumn NOTIFY keyColumnChanged)
                  Q_PROPERTY(int valueColumn READ valueColumn WRITE setValueColumn NOTIFY valueColumnChanged)
                  Q_PROPERTY(const QPersistentModelIndex& relationModelRoot READ relationModelRoot WRITE setRelationModelRoot NOTIFY relationModelRootChanged)
                  Q_DISABLE_COPY(RelationDelegate)
              public:
                  explicit RelationDelegate(QObject* parent = Q_NULLPTR)
                      :QStyledItemDelegate(parent)
                      , m_relModel(Q_NULLPTR)
                      , m_keyCol(0)
                      , m_valueCol(1)
                  {
                      QObject::connect(this, &RelationDelegate::relationModelChanged, this, &RelationDelegate::viewNeedUpdate);
                      QObject::connect(this, &RelationDelegate::keyColumnChanged, this, &RelationDelegate::viewNeedUpdate);
                      QObject::connect(this, &RelationDelegate::valueColumnChanged, this, &RelationDelegate::viewNeedUpdate);
                      QObject::connect(this, &RelationDelegate::relationModelRootChanged, this, &RelationDelegate::viewNeedUpdate);
                  }
                  Q_SIGNAL void relationModelRootChanged(const QPersistentModelIndex& rootIdx);
                  const QPersistentModelIndex& relationModelRoot() const { return m_relModelRoot; }
                  void setRelationModelRoot(const QPersistentModelIndex& rootIdx)
                  {
                      if (m_relModelRoot == rootIdx)
                          return;
                      Q_ASSERT(!rootIdx.isValid() || rootIdx.model()==m_relModel);
                      m_relModelRoot = rootIdx;
                      relationModelRootChanged(m_relModelRoot);
                  }
                  Q_SIGNAL void viewNeedUpdate();
                  QAbstractItemModel* relationModel() const { return m_relModel; };
                  Q_SIGNAL void relationModelChanged(QAbstractItemModel* relModel);
                  void setRelationModel(QAbstractItemModel* relModel)
                  {
                      if (relModel == m_relModel) 
                          return;
                      m_relModel = relModel;
                      relationModelChanged(m_relModel);
                  }
                  int keyColumn() const { return m_keyCol; }
                  Q_SIGNAL void keyColumnChanged(int keyCol);
                  void setKeyColumn(int keyCol)
                  {
                      if (keyCol == m_keyCol)
                          return;
                      m_keyCol = keyCol;
                      keyColumnChanged(m_keyCol);
                  }
                  int valueColumn() const { return m_valueCol; }
                  Q_SIGNAL void valueColumnChanged(int valueCol);
                  void setValueColumn(int valueCol)
                  {
                      if (valueCol == m_valueCol) 
                          return;
                      m_valueCol = valueCol;
                      valueColumnChanged(m_valueCol);
                  }
                  QString displayText(const QVariant& value, const QLocale& locale) const Q_DECL_OVERRIDE{
                      if (m_relModel) {
                          const int relModelRows = m_relModel->rowCount(m_relModelRoot);
                          for (int i = 0; i < relModelRows; ++i) {
                              if (m_relModel->index(i, m_keyCol, m_relModelRoot).data() == value)
                                  return QStyledItemDelegate::displayText(m_relModel->index(i, m_valueCol, m_relModelRoot).data(), locale);
                          }
                      }
                      return QStyledItemDelegate::displayText(value, locale);
                  }
                   QWidget* createEditor(QWidget *parent, const QStyleOptionViewItem &option, const QModelIndex &index) const Q_DECL_OVERRIDE{
                      if (!m_relModel) 
                          return QStyledItemDelegate::createEditor(parent, option, index);
                      QComboBox* editor = new QComboBox(parent);
                      editor->setModel(m_relModel);
                      editor->setRootModelIndex(m_relModelRoot);
                      editor->setModelColumn(m_valueCol);
                      return editor;
                  }
                  void setEditorData(QWidget *editor, const QModelIndex &index) const Q_DECL_OVERRIDE{
                      if (!m_relModel) 
                          return QStyledItemDelegate::setEditorData(editor, index);
                      QComboBox* editCombo = qobject_cast<QComboBox*>(editor);
                      Q_ASSERT(editCombo);
                      const int relModelRows = m_relModel->rowCount(m_relModelRoot);
                      for (int i = 0; i < relModelRows; ++i) {
                          if (m_relModel->index(i, m_keyCol, m_relModelRoot).data() == index.data()) {
                              editCombo->setCurrentIndex(i);
                              return;
                          }
                      }
                  }
                  void setModelData(QWidget *editor, QAbstractItemModel *model, const QModelIndex &index) const Q_DECL_OVERRIDE{
                      if (!m_relModel)
                          return QStyledItemDelegate::setModelData(editor, model, index);
                      QComboBox* editCombo = qobject_cast<QComboBox*>(editor);
                      Q_ASSERT(editCombo);
                      const int currIdx = editCombo->currentIndex();
                      model->setData(index, m_relModel->index(currIdx, m_keyCol, m_relModelRoot).data());
                  }
                  void updateEditorGeometry(QWidget *editor, const QStyleOptionViewItem &option, const QModelIndex &index) const Q_DECL_OVERRIDE{
                      if (!m_relModel)
                          return QStyledItemDelegate::updateEditorGeometry(editor, option, index);
                      editor->setGeometry(option.rect);
                  }
                  
              private:
                  QAbstractItemModel* m_relModel;
                  int m_keyCol;
                  int m_valueCol;
                  QPersistentModelIndex m_relModelRoot;
              };
              

              and connect the viewNeedUpdate to the update of the view

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

              @VRonin
              Yes. I appreciate this. Like I edited my previous, it's actually the QSqlTableModel level I miss, QSqlRelationalTableModel doesn't add that much. I realize each one is written on top of the next one in Qt, so of course you can design them on top of QSqlQuery.

              That's a bit of code you've written there! It'll be even more to model QSqlTableModel too :)

              1 Reply Last reply
              0
              • devhobbyD Offline
                devhobbyD Offline
                devhobby
                wrote on last edited by
                #9

                I wanted to take a different approach:

                Send a custom query to the table view in read-only mode

                Then, a right click (or another method) will change the content of the record through code.

                The problem is: I can't even send a custom query!

                My query (similar to the one I posted above) has some joins in it.

                But why can't I use the table returned by a query as the table to display on the Table View?

                Is all this really so limited?

                JonBJ 1 Reply Last reply
                0
                • devhobbyD devhobby

                  I wanted to take a different approach:

                  Send a custom query to the table view in read-only mode

                  Then, a right click (or another method) will change the content of the record through code.

                  The problem is: I can't even send a custom query!

                  My query (similar to the one I posted above) has some joins in it.

                  But why can't I use the table returned by a query as the table to display on the Table View?

                  Is all this really so limited?

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

                  @devhobby
                  You are using a QTableView, right? You can use that against a QSqlQueryModel, which is a model based against a query rather than an actual table.

                  devhobbyD 1 Reply Last reply
                  0
                  • JonBJ JonB

                    @devhobby
                    You are using a QTableView, right? You can use that against a QSqlQueryModel, which is a model based against a query rather than an actual table.

                    devhobbyD Offline
                    devhobbyD Offline
                    devhobby
                    wrote on last edited by devhobby
                    #11

                    @JonB said in QSqlRelationalTableModel with multiple Joins?:

                    @devhobby
                    You are using a QTableView, right? You can use that against a QSqlQueryModel, which is a model based against a query rather than an actual table.

                    No, I'm using a QSqlQueryModel

                            _queryModel = new QSqlQueryModel(ui->tableView);
                    
                           _queryModel->setQuery("SELECT Employee.id, Employee.Name, Employee.Surname, Birthplace.Name as 'Birthplace', Employee.Birthdate, Residence.Name as 'Residence', Employee.Qualification, Position.Name as 'Position', EmployeePosition.EngagementDate, Resource.Name as 'Resource', EmployeeResource.AcquisitionDate"
                                        "FROM Employee"
                                        "LEFT JOIN EmployeePosition ON Employee.id = EmployeePosition.idEmployee"
                                        "LEFT JOIN Position ON EmployeePosition.idPosition = Position.id"
                    
                                        "LEFT JOIN EmployeeResource ON Employee.id = EmployeeResource.idEmployee"
                                        "LEFT JOIN Resource ON Resource.id = EmployeeResource.idResource"
                    
                                        "LEFT JOIN Country AS Birthplace ON Employee.BirthplaceID = Birthplace.id"
                                        "LEFT JOIN Country AS Residence ON Employee.ResidenceID = Residence.id", *_db);
                    
                            ui->tableView->setModel(_queryModel);
                            ui->tableView->show();
                    

                    And the Table View is blank

                    I also tried with QSqlTableModel

                            _tableModel = new QSqlTableModel(ui->tableView, *_db);
                    
                            QString myQuery = "SELECT Employee.id, Employee.Name, Employee.Surname, Birthplace.Name as 'Birthplace', Employee.Birthdate, Residence.Name as 'Residence', Employee.Qualification, Position.Name as 'Position', EmployeePosition.EngagementDate, Resource.Name as 'Resource', EmployeeResource.AcquisitionDate"
                                            "FROM Employee"
                                            "LEFT JOIN EmployeePosition ON Employee.id = EmployeePosition.idEmployee"
                                            "LEFT JOIN Position ON EmployeePosition.idPosition = Position.id"
                    
                                            "LEFT JOIN EmployeeResource ON Employee.id = EmployeeResource.idEmployee"
                                            "LEFT JOIN Resource ON Resource.id = EmployeeResource.idResource"
                    
                                            "LEFT JOIN Country AS Birthplace ON Employee.BirthplaceID = Birthplace.id"
                                            "LEFT JOIN Country AS Residence ON Employee.ResidenceID = Residence.id";
                    
                            _tableModel->setTable(myQuery );
                            _tableModel->setEditStrategy(QSqlTableModel::OnManualSubmit);
                            _tableModel->select();
                    
                            ui->tableView->setModel(_tableModel);
                            ui->tableView->show();
                    

                    Same issue: Table View is blank.

                    JonBJ 1 Reply Last reply
                    0
                    • devhobbyD devhobby

                      @JonB said in QSqlRelationalTableModel with multiple Joins?:

                      @devhobby
                      You are using a QTableView, right? You can use that against a QSqlQueryModel, which is a model based against a query rather than an actual table.

                      No, I'm using a QSqlQueryModel

                              _queryModel = new QSqlQueryModel(ui->tableView);
                      
                             _queryModel->setQuery("SELECT Employee.id, Employee.Name, Employee.Surname, Birthplace.Name as 'Birthplace', Employee.Birthdate, Residence.Name as 'Residence', Employee.Qualification, Position.Name as 'Position', EmployeePosition.EngagementDate, Resource.Name as 'Resource', EmployeeResource.AcquisitionDate"
                                          "FROM Employee"
                                          "LEFT JOIN EmployeePosition ON Employee.id = EmployeePosition.idEmployee"
                                          "LEFT JOIN Position ON EmployeePosition.idPosition = Position.id"
                      
                                          "LEFT JOIN EmployeeResource ON Employee.id = EmployeeResource.idEmployee"
                                          "LEFT JOIN Resource ON Resource.id = EmployeeResource.idResource"
                      
                                          "LEFT JOIN Country AS Birthplace ON Employee.BirthplaceID = Birthplace.id"
                                          "LEFT JOIN Country AS Residence ON Employee.ResidenceID = Residence.id", *_db);
                      
                              ui->tableView->setModel(_queryModel);
                              ui->tableView->show();
                      

                      And the Table View is blank

                      I also tried with QSqlTableModel

                              _tableModel = new QSqlTableModel(ui->tableView, *_db);
                      
                              QString myQuery = "SELECT Employee.id, Employee.Name, Employee.Surname, Birthplace.Name as 'Birthplace', Employee.Birthdate, Residence.Name as 'Residence', Employee.Qualification, Position.Name as 'Position', EmployeePosition.EngagementDate, Resource.Name as 'Resource', EmployeeResource.AcquisitionDate"
                                              "FROM Employee"
                                              "LEFT JOIN EmployeePosition ON Employee.id = EmployeePosition.idEmployee"
                                              "LEFT JOIN Position ON EmployeePosition.idPosition = Position.id"
                      
                                              "LEFT JOIN EmployeeResource ON Employee.id = EmployeeResource.idEmployee"
                                              "LEFT JOIN Resource ON Resource.id = EmployeeResource.idResource"
                      
                                              "LEFT JOIN Country AS Birthplace ON Employee.BirthplaceID = Birthplace.id"
                                              "LEFT JOIN Country AS Residence ON Employee.ResidenceID = Residence.id";
                      
                              _tableModel->setTable(myQuery );
                              _tableModel->setEditStrategy(QSqlTableModel::OnManualSubmit);
                              _tableModel->select();
                      
                              ui->tableView->setModel(_tableModel);
                              ui->tableView->show();
                      

                      Same issue: Table View is blank.

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

                      @devhobby
                      I was asking you to confirm that in ui->tableView->setModel(_queryModel); the type of your ui->tableView is a QTableView?

                      Anyway, assuming it is, what's this:

                       _queryModel = new QSqlQueryModel(ui->tableView);
                      

                      What's that parent doing there? (Haven't seen this before, just checking.)

                      Does QSqlQueryModel::lastError() tell you anything after setQuery() or later?

                      Hang on! I don't do C++, but the way you've written your query, does C++ join the strings with a space between? Because if not (and I don't think it does) your words are all touching each other, and you have a SQL error!! Make sure your query is right, and check that lastError() thing, e.g.

                      QSqlQueryModel model;
                      model.setQuery("select *"
                                     "from MyTable");
                      if (model.lastError().isValid())
                          qDebug() << model.lastError();
                      

                      Because I think the principle should be working fine otherwise.

                      devhobbyD 1 Reply Last reply
                      3
                      • JonBJ JonB

                        @devhobby
                        I was asking you to confirm that in ui->tableView->setModel(_queryModel); the type of your ui->tableView is a QTableView?

                        Anyway, assuming it is, what's this:

                         _queryModel = new QSqlQueryModel(ui->tableView);
                        

                        What's that parent doing there? (Haven't seen this before, just checking.)

                        Does QSqlQueryModel::lastError() tell you anything after setQuery() or later?

                        Hang on! I don't do C++, but the way you've written your query, does C++ join the strings with a space between? Because if not (and I don't think it does) your words are all touching each other, and you have a SQL error!! Make sure your query is right, and check that lastError() thing, e.g.

                        QSqlQueryModel model;
                        model.setQuery("select *"
                                       "from MyTable");
                        if (model.lastError().isValid())
                            qDebug() << model.lastError();
                        

                        Because I think the principle should be working fine otherwise.

                        devhobbyD Offline
                        devhobbyD Offline
                        devhobby
                        wrote on last edited by devhobby
                        #13

                        @JonB said in QSqlRelationalTableModel with multiple Joins?:
                        does C++ join the strings with a space between? Because if not (and I don't think it does) your words are all touching each other, >and you have a SQL error!! Make sure your query is right, and check that lastError() thing, e.g.
                        Because I think the principle should be working fine otherwise.

                        Oh gosh! I completely forgot to put the spaces before each line! Thanks

                        By the way, once that I get all of this setup... now I need to find a way to customize a cell (item).
                        I successfully retrieve its index... but I'd like to change its background color, for instance

                        And since I'm using a Table View and not a Table Widget, I only see item delegates.

                        How can I achieve what I want?

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

                          See https://forum.qt.io/topic/85973/how-to-simply-change-the-background-colour-of-a-cell-inside-a-tableview

                          "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

                          devhobbyD 1 Reply Last reply
                          2
                          • VRoninV VRonin

                            See https://forum.qt.io/topic/85973/how-to-simply-change-the-background-colour-of-a-cell-inside-a-tableview

                            devhobbyD Offline
                            devhobbyD Offline
                            devhobby
                            wrote on last edited by
                            #15

                            @VRonin said in QSqlRelationalTableModel with multiple Joins?:

                            See https://forum.qt.io/topic/85973/how-to-simply-change-the-background-colour-of-a-cell-inside-a-tableview

                            Thanks but before doing that, I was wondering if there's a way to change the content of the cell by directly typing into it.

                            I just want to change the data of the cell and, once changed, color that cell in a different color to alert the user of the change of that particular item/cell.

                            Seems a lot of hard-coding work... maybe I should find another convenient way... but for now let's see what comes out

                            JonBJ VRoninV 2 Replies Last reply
                            0
                            • devhobbyD devhobby

                              @VRonin said in QSqlRelationalTableModel with multiple Joins?:

                              See https://forum.qt.io/topic/85973/how-to-simply-change-the-background-colour-of-a-cell-inside-a-tableview

                              Thanks but before doing that, I was wondering if there's a way to change the content of the cell by directly typing into it.

                              I just want to change the data of the cell and, once changed, color that cell in a different color to alert the user of the change of that particular item/cell.

                              Seems a lot of hard-coding work... maybe I should find another convenient way... but for now let's see what comes out

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

                              @devhobby
                              If you want to edit in a QTableView (right?), what have you set your http://doc.qt.io/qt-5/qabstractitemview.html#editTriggers-prop to?

                              devhobbyD 1 Reply Last reply
                              1
                              • JonBJ JonB

                                @devhobby
                                If you want to edit in a QTableView (right?), what have you set your http://doc.qt.io/qt-5/qabstractitemview.html#editTriggers-prop to?

                                devhobbyD Offline
                                devhobbyD Offline
                                devhobby
                                wrote on last edited by
                                #17

                                @JonB said in QSqlRelationalTableModel with multiple Joins?:

                                @devhobby
                                If you want to edit in a QTableView (right?), what have you set your http://doc.qt.io/qt-5/qabstractitemview.html#editTriggers-prop to?

                                QAbstractItemView::DoubleClicked

                                ui->tableView->setModel(_queryModel);
                                ui->tableView->verticalHeader()->hide();
                                ui->tableView->setEditTriggers(QAbstractItemView::DoubleClicked);
                                ui->tableView->show();
                                
                                JonBJ 1 Reply Last reply
                                0
                                • devhobbyD devhobby

                                  @JonB said in QSqlRelationalTableModel with multiple Joins?:

                                  @devhobby
                                  If you want to edit in a QTableView (right?), what have you set your http://doc.qt.io/qt-5/qabstractitemview.html#editTriggers-prop to?

                                  QAbstractItemView::DoubleClicked

                                  ui->tableView->setModel(_queryModel);
                                  ui->tableView->verticalHeader()->hide();
                                  ui->tableView->setEditTriggers(QAbstractItemView::DoubleClicked);
                                  ui->tableView->show();
                                  
                                  JonBJ Offline
                                  JonBJ Offline
                                  JonB
                                  wrote on last edited by JonB
                                  #18

                                  @devhobby
                                  And you are saying that when you double-click...? Nothing at all happens?

                                  If that is the case, I can only imagine your model is read-only, to do with it being a SELECT and not a table? To be clear, you won't be "changing the data of the cell" per se, you'll be changing the data in the model that cell is displaying.

                                  devhobbyD 1 Reply Last reply
                                  0
                                  • JonBJ JonB

                                    @devhobby
                                    And you are saying that when you double-click...? Nothing at all happens?

                                    If that is the case, I can only imagine your model is read-only, to do with it being a SELECT and not a table? To be clear, you won't be "changing the data of the cell" per se, you'll be changing the data in the model that cell is displaying.

                                    devhobbyD Offline
                                    devhobbyD Offline
                                    devhobby
                                    wrote on last edited by devhobby
                                    #19

                                    @JonB said in QSqlRelationalTableModel with multiple Joins?:

                                    @devhobby
                                    And you are saying that when you double-click...? Nothing at all happens?

                                    If that is the case, I can only imagine your model is read-only, to do with it being a SELECT and not a table?

                                    Yes, as stated above I used my custom query to do all the joins.

                                    By the way, I don't expect to change the database directly.

                                    I just want to visually edit the cell without applying any changes to the database.

                                    Once the user changes the text of the cell, the new text is immediately visible and I store it in a QString

                                    When the user presses the button "Commit changes" I will have a series of changes that the user wants to apply to the database -> I now need to make them real sending a custom query to the database.

                                    JonBJ 1 Reply Last reply
                                    0
                                    • devhobbyD devhobby

                                      @JonB said in QSqlRelationalTableModel with multiple Joins?:

                                      @devhobby
                                      And you are saying that when you double-click...? Nothing at all happens?

                                      If that is the case, I can only imagine your model is read-only, to do with it being a SELECT and not a table?

                                      Yes, as stated above I used my custom query to do all the joins.

                                      By the way, I don't expect to change the database directly.

                                      I just want to visually edit the cell without applying any changes to the database.

                                      Once the user changes the text of the cell, the new text is immediately visible and I store it in a QString

                                      When the user presses the button "Commit changes" I will have a series of changes that the user wants to apply to the database -> I now need to make them real sending a custom query to the database.

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

                                      @devhobby
                                      I'm a little lost. You wrote:

                                      Thanks but before doing that, I was wondering if there's a way to change the content of the cell by directly typing into it.

                                      I thought you were saying when double-click it does not let you edit, nothing happens, or whatever. Now I think you're saying it does let you edit? I don't know if you have a question here?

                                      devhobbyD 1 Reply Last reply
                                      0
                                      • JonBJ JonB

                                        @devhobby
                                        I'm a little lost. You wrote:

                                        Thanks but before doing that, I was wondering if there's a way to change the content of the cell by directly typing into it.

                                        I thought you were saying when double-click it does not let you edit, nothing happens, or whatever. Now I think you're saying it does let you edit? I don't know if you have a question here?

                                        devhobbyD Offline
                                        devhobbyD Offline
                                        devhobby
                                        wrote on last edited by devhobby
                                        #21

                                        @JonB said in QSqlRelationalTableModel with multiple Joins?:

                                        @devhobby
                                        I'm a little lost. You wrote:

                                        Thanks but before doing that, I was wondering if there's a way to change the content of the cell by directly typing into it.

                                        I thought you were saying when double-click it does not let you edit, nothing happens, or whatever. Now I think you're saying it does let you edit? I don't know if you have a question here?

                                        Yes I asked if there's a way to change the content of the cell by directly typing into it

                                        Because now, when I double click, nothing happens.

                                        Once the cell is edited, I'd also like to change its background color... but that's another story

                                        JonBJ 1 Reply Last reply
                                        0
                                        • devhobbyD devhobby

                                          @JonB said in QSqlRelationalTableModel with multiple Joins?:

                                          @devhobby
                                          I'm a little lost. You wrote:

                                          Thanks but before doing that, I was wondering if there's a way to change the content of the cell by directly typing into it.

                                          I thought you were saying when double-click it does not let you edit, nothing happens, or whatever. Now I think you're saying it does let you edit? I don't know if you have a question here?

                                          Yes I asked if there's a way to change the content of the cell by directly typing into it

                                          Because now, when I double click, nothing happens.

                                          Once the cell is edited, I'd also like to change its background color... but that's another story

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

                                          @devhobby said in QSqlRelationalTableModel with multiple Joins?:

                                          Once the user changes the text of the cell, the new text is immediately visible and I store it in a QString

                                          Because now, when I double click, nothing happens.

                                          Sorry, but if "nothing happens" when you double-click cell to edit, how come you talk about "Once the user changes the text of the cell"? Maybe I'm being dumb, but I just don't get it!

                                          devhobbyD 1 Reply Last reply
                                          1

                                          • Login

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