QSqlRelationalTableModel with multiple Joins?

[JonB]

@devhobby
I'm going back to your very original post and thinking about what you're actually trying to achieve anyway. The code we're talking about is all well & good, if you want to proceed, but I'm wondering about your expectations of the interface.

In your pic of Employee table only, you have 1 row per employee. When you say:

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 are you expecting the table interface to be for the multiple positions & resources you say employees have?

It's all very well to say:

What I need, hence, is a full outer join
The query above gives me everything I want to know and see on the Table View

[I'm trusting that your code implements FULL OUTER JOIN] but left to its own devices this will mean you have many rows per employee, for each position/resource variant. Is that what you intend??

One thing to understand: when you're using QSqlRelationalTableModel so that you can "look up" position/resource names, the combobox you'll get will only ever allow single selection, if you're imagining that it might provide multiple selection it won't.

[devhobby]

@JonB said in QSqlRelationalTableModel with multiple Joins?:

@devhobby
Look at the last bit I just added to my post above.

To make it [QSqlQueryModel] read-write, you must subclass it and reimplement setData() and flags().

(And BTW when you've done that there won't be any "iterating over items and setting the editable flag", your items will be editable through your reimplementation of QSqlQueryModel::flags().)

You understand how to "subclass", and how to "reimplement [override] virtual methods", don't you?

I have my CustomModel class now deriving from QSqlQueryModel

#ifndef CUSTOMMODEL_H
#define CUSTOMMODEL_H
#include <QSqlQueryModel>

class CustomModel : public QSqlQueryModel
{
    Q_OBJECT
    
public:
    bool setData(const QModelIndex &index, const QVariant &value, int role) override;
    Qt::ItemFlags flags(const QModelIndex &index) const override;
};

#endif

The problem is...

The linker somehow can't match the signatures... weird.

I checked this link for example [ https://code.woboq.org/qt5/qtbase/src/corelib/itemmodels/qabstractitemmodel.h.html ]

and both setData() and flags() are the same way I overloaded them...

[JonB]

@devhobby
Now you're talking C++ esotericism, and I'm a Python Qt guy anyway. This one is your problem! :) Sometimes signatures don't match if you don't get your consts just right? What about the Q_INVOKABLE?

https://stackoverflow.com/questions/33781346/how-to-write-setdata-in-qsqlquerymodelqabstractitemmodel is an example of what claims to work? (Oohh, it uses Q_DECL_OVERRIDE for your override?)

[devhobby]

@JonB said in QSqlRelationalTableModel with multiple Joins?:

@devhobby
Oohh, it uses Q_DECL_OVERRIDE for your override?

Yes, it is ALSO that.

Something strange is going on here...

I'll try tagging someone @SGaist @VRonin

--- HEADER FILE ---

#ifndef MYMODEL_H
#define MYMODEL_H

#include <QObject>
#include <QSqlQueryModel>

class MyModel : public QSqlQueryModel
{
    Q_OBJECT

public:
    MyModel(QObject *parent = 0);

    Qt::ItemFlags flags(const QModelIndex &index) const override;
    bool setData(const QModelIndex &index, const QVariant &value, int role = Qt::EditRole) override;
};

#endif // MYMODEL_H

--- CPP FILE ---

#include "mymodel.h"

MyModel::MyModel(QObject *parent) : QSqlQueryModel(parent)
{
}

Qt::ItemFlags MyModel::flags(const QModelIndex &index) const
{

}

bool MyModel::setData(const QModelIndex &index, const QVariant &value, int role)
{

}

[SGaist]

Your CPP file contains an error, the default value should be in the declaration of the function.

If you are using Qt >= 5.7, you can simply use the override keyword as C++11 support is mandatory since that version. Q_DECL_OVERRIDE was used to allow compatibly with non-C++11 enabled compilers.

[JonB]

@SGaist You're a hero ;-) It takes you like 1 minute to spot it & reply!

[devhobby]

@SGaist said in QSqlRelationalTableModel with multiple Joins?:

Your CPP file contains an error, the default value should be in the declaration of the function.

If you are using Qt >= 5.7, you can simply use the override keyword as C++11 support is mandatory since that version. Q_DECL_OVERRIDE was used to allow compatibly with non-C++11 enabled compilers.

I hoped it was just that...

I moved the default value in the header file, same issue

[ I will update the code posted above so that I don't repost it everytime ]

[SGaist]

Did you try re-running qmake before building ?

[devhobby]

@SGaist

Did you try re-running qmake before building ?

Thank you, sir! That solved it :)

@JonB

I'm going back to your very original post and thinking about what you're actually trying to achieve anyway. The code we're talking about is all well & good, if you want to proceed, but I'm wondering about your expectations of the interface.

Back to the original post... at the moment I can successfully double-click items and edit them.

How they are edited and how the edit is going to affect the table and the database is something I believe I can do by myself now.

I guess I've caught the mechanism behind it!

BUT

One thing that has remained unresolved is the fact that anytime I change a field (setData() is called) the edited cell must change its background color.

I still don't get this one.

P.S: @JonB I wanted to thank you too. It's amazing the way you're helping me. I just can't thank you enough :)

[SGaist]

Do you mean you want to mark the cell as "edited" ?

[devhobby]

@SGaist said in QSqlRelationalTableModel with multiple Joins?:

Do you mean you want to mark the cell as "edited" ?

Yes but... my way!

I want to change it's background color to, say, Cyan.

This way the user, before committing the changes, can take a look and see what is going to really change in the database.

[SGaist]

One way could be to keep a vector of edited cells that you update when setData is called with the EditRole and that you will use when data is called for the BackgroundRole and you return the colour you want.

[devhobby]

@SGaist said in QSqlRelationalTableModel with multiple Joins?:

One way could be to keep a vector of edited cells that you update when setData is called with the EditRole and that you will use when data is called for the BackgroundRole and you return the colour you want.

Thank you. The problem is I can't find the method which allows me to change te color to a cell. I guess something like setBackgroundColor(row,column)

[SGaist]

Because there's none. Do you have a custom setData method ? If so you should emit the dataChanged signal properly and it should trigger an update of the view which should request all the roles including the one for background colour.

[devhobby]

@SGaist said in QSqlRelationalTableModel with multiple Joins?:

which should request all the roles including the one for background colour.

That! I don't know how to make such a request for the table view.

[SGaist]

That's what dataChanged is for.

[devhobby]

@SGaist said in QSqlRelationalTableModel with multiple Joins?:

That's what dataChanged is for.

In fact I emit the signal including the role

emit dataChanged(index, index, roleArray);

roleArray includes Qt::BackgroundColor

Now what? Where do I select the color the cell has to become?

[SGaist]

Re-implement the data method and handle the BackgroundRole special case there.

[devhobby]

@SGaist said in QSqlRelationalTableModel with multiple Joins?:

Re-implement the data method and handle the BackgroundRole special case there.

I now return the red color when the role is Qt::BackgroundColor

if (!item.isValid())
   return QVariant();

if(role == Qt::BackgroundRole)
    return QColor(255, 0, 0);

return QVariant();

My Table View now is all red without changing anything!

Also, is it good to return a default-constructed QVariant when no particular criteria are met?

[JonB]

@devhobby

My Table View now is all red without changing anything!

Have you understood you now need to look up the cell coordinates (item) in the vector of changed cells, as per:

@SGaist said in QSqlRelationalTableModel with multiple Joins?:

One way could be to keep a vector of edited cells that you update when setData is called with the EditRole and that you will use when data is called for the BackgroundRole and you return the colour you want.