QSqlRelationalTableModel with multiple Joins?

devhobby · 7 Feb 2018, 16:19

Hi.

I have a database which has 2 many to many relations. This means 2 intermediate tables.

Employee
Position (like programmer, 3d artist...)
Resource (like printer, plotter...)
EmployeePosition (many to many: N employees assume N positions)
EmployeeResource (many to many: N employees have access to N resources)

In my Table View I only see Employee's fields:

_relationalModel = new QSqlRelationalTableModel(ui->tableView, *_db);
_relationalModel->setTable("Employee");
_relationalModel->setEditStrategy(QSqlTableModel::OnManualSubmit);
_relationalModel->setRelation(3, QSqlRelation("Country", "id", "Name"));    // Employee.BirthplaceID
_relationalModel->setRelation(5, QSqlRelation("Country", "id", "Name"));    // Employee.ResidenceID
_relationalModel->select();

ui->tableView->setModel(_relationalModel);
ui->tableView->setItemDelegate(new QSqlRelationalDelegate(ui->tableView));

The result is just the Employee table with its fields:

However, I need something more.

I want to join the Employee table with EmployeePosition and EmployeeResource so that I can also see, for each employee, their positions and resources (if they have any!)

What I need, hence, is a full outer join

SELECT * 
  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
  
  UNION ALL
  
 SELECT * 
  FROM Employee
  RIGHT JOIN EmployeePosition ON Employee.id = EmployeePosition.idEmployee
  RIGHT JOIN Position ON EmployeePosition.idPosition = Position.id

  RIGHT JOIN EmployeeResource ON Employee.id = EmployeeResource.idEmployee
  RIGHT JOIN Resource ON Resource.id = EmployeeResource.idResource
  WHERE Employee.id IS NULL

In MySQL there's no Full Outer Join, so I needed to write it another way

The query above gives me everything I want to know and see on the Table View

But I also want to be able to edit the fields (like I do now, but this time with this larger table).

Have you got any suggestions about how to achieve this result?

Thanks in advance!

JonB · wrote on 7 Feb 2018, 16:19

@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....

devhobby · 7 Feb 2018, 19:23

@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!

VRonin · 7 Feb 2018, 19:25

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

JonB · wrote on 7 Feb 2018, 19:23

@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... :(

JonB · 2 Jul 2018, 19:29

@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.]

VRonin · 2 Jul 2018, 20:21

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

JonB · 2 Jul 2018, 20:15

@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 :)

devhobby · 7 Feb 2018, 21:36

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?

JonB · 7 Feb 2018, 21:47

@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.

devhobby · 2 Jul 2018, 22:08

@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.

JonB · 2 Jul 2018, 23:32

@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.

devhobby · 2 Aug 2018, 14:50

@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?

VRonin · 8 Feb 2018, 15:50

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

devhobby · 8 Feb 2018, 16:31

@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

JonB · 8 Feb 2018, 16:37

@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?

devhobby · 8 Feb 2018, 16:44

@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();

JonB · 2 Aug 2018, 16:45

@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.

devhobby · 2 Aug 2018, 16:51

@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.

JonB · 2 Aug 2018, 16:55

@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?