QSQLRelationalTableModel display relation

BushyAxis793

Hello. I have a problem with my QSqlRelationalTableModel. I have table in my database "clampingtypes" which is in relationship with "clampings" table. I tried to show relational with this code:



    tableModel->setTable("clampings");


    tableModel->setRelation(3,QSqlRelation("clampingtypes","ClampingTypeID","ClampingType"));


   // tableModel->setRelation(3,QSqlRelation("instruments","InstrumentID","InstrumentDimension1"));
    //tableModel->setRelation(4,QSqlRelation("instruments","InstrumentID","InstrumentDimension2"));
    //tableModel->setRelation(5,QSqlRelation("instruments","InstrumentID","InstrumentDimension3"));
/*
    tableModel->setHeaderData(0,Qt::Horizontal,tr("Numer ID"));
    tableModel->setHeaderData(1,Qt::Horizontal,tr("Rodzaj"));
    tableModel->setHeaderData(2,Qt::Horizontal,tr("Długość [mm]"));
    tableModel->setHeaderData(3,Qt::Horizontal,tr("Szerokość [mm]"));
    tableModel->setHeaderData(4,Qt::Horizontal,tr("Wysokość [mm]"));
    tableModel->setHeaderData(5,Qt::Horizontal,tr("Materiał"));
    tableModel->setHeaderData(6,Qt::Horizontal,tr("Masa [kg]"));
    tableModel->setHeaderData(7,Qt::Horizontal,tr("Opis przyrządu"));

*/

    clampingTableView->setEditTriggers(QAbstractItemView::NoEditTriggers);
    clampingTableView->setFocusPolicy(Qt::NoFocus);
    clampingTableView->setSelectionBehavior(QAbstractItemView::SelectRows);


    clampingTableView->setModel(tableModel);
    tableModel->select();

    /*
    clampingTableView->hideColumn(tableModel->fieldIndex("ClampingID"));
    clampingTableView->hideColumn(tableModel->fieldIndex("ClampingName"));
    clampingTableView->hideColumn(tableModel->fieldIndex("ClampingSeller"));
    clampingTableView->hideColumn(tableModel->fieldIndex("ClampingLink"));
    clampingTableView->hideColumn(tableModel->fieldIndex("ClampingImage"));
    clampingTableView->hideColumn(tableModel->fieldIndex("ClampingMaterialID"));
    clampingTableView->hideColumn(tableModel->fieldIndex("ClampingMass"));
    clampingTableView->hideColumn(tableModel->fieldIndex("ClampingDescription"));
    clampingTableView->hideColumn(tableModel->fieldIndex("ClampingPriceTypeID"));
    clampingTableView->hideColumn(tableModel->fieldIndex("ClampingAddedDateTime"));
    clampingTableView->hideColumn(tableModel->fieldIndex("ClampingPrice"));

*/

    clampingTableView->horizontalHeader()->setSectionResizeMode(0,QHeaderView::ResizeToContents);
    clampingTableView->horizontalHeader()->setSectionResizeMode(1,QHeaderView::ResizeToContents);
    clampingTableView->horizontalHeader()->setSectionResizeMode(2,QHeaderView::ResizeToContents);
    clampingTableView->horizontalHeader()->setSectionResizeMode(3,QHeaderView::ResizeToContents);
    clampingTableView->horizontalHeader()->setSectionResizeMode(4,QHeaderView::ResizeToContents);
    clampingTableView->horizontalHeader()->setSectionResizeMode(5,QHeaderView::ResizeToContents);
    clampingTableView->horizontalHeader()->setSectionResizeMode(6,QHeaderView::ResizeToContents);
    clampingTableView->horizontalHeader()->setSectionResizeMode(7,QHeaderView::ResizeToContents);
    clampingTableView->horizontalHeader()->setSectionResizeMode(8,QHeaderView::ResizeToContents);
    clampingTableView->horizontalHeader()->setSectionResizeMode(9,QHeaderView::ResizeToContents);
    clampingTableView->horizontalHeader()->setSectionResizeMode(10,QHeaderView::ResizeToContents);



    int rowTotalWidth = 0;

    int count = clampingTableView->horizontalHeader()->count();

    for(int i = 0; i<count;++i)
    {
        if(!clampingTableView->horizontalHeader()->isSectionHidden(i))
        {
            rowTotalWidth += clampingTableView->horizontalHeader()->sectionSize(i);
        }
    }

    clampingTableView->setMaximumWidth(780);

But the result is:

Below is my clampingtypes table:

Any idea how to selve this issue? I will be very appreciate. Have a good day.

JonB

@BushyAxis793 said in QSQLRelationalTableModel display relation:

tableModel->setRelation(6,QSqlRelation("instruments","InstrumentID","InstrumentDimension1"));
tableModel->setRelation(7,QSqlRelation("instruments","InstrumentID","InstrumentDimension2"));
tableModel->setRelation(8,QSqlRelation("instruments","InstrumentID","InstrumentDimension3"));

I am having trouble understanding what is going on here. Maybe it doesn't help for me that your column display names are in Polish. And I don't understand what you are saying when you write

EDIT: I suppouse I have to add additional columns for each dimension.

These 3 lines mean: columns #6, #7 & #8 in tableModel each contain an "InstrumentID". The code should use the value in that column in the original table to locate (via InstrumentID) the corresponding row in table instruments. Then it should display from that row InstrumentDimension1/2/3, which must already be columns in instruments.

It is a little "unusual" to display more than one column from the foreign key table. I do not know that Qt does not support that for some reason, but maybe for a given QSqlRelation("instruments","InstrumentID", ...), i.e. foreign key table+lookup-column, it only allows one relation with one display column. Hence you only see one of the InstrumentDimension1/2/3 mappings?

Although it's not well documented, QSqlRelation actually allows multiple columns to be specified for the "display" column. It might be interesting to see what

tableModel->setRelation(6,QSqlRelation("instruments","InstrumentID","InstrumentDimension1, InstrumentDimension2, InstrumentDimension3"));

produces for you (you may need to comment out the relations for columns #7 & #8). ~~That column type would be string.~~

SGaist

Hi,

Do you have null entries in your table ?
Does it show correctly if you don't set the relation ?

BushyAxis793

@SGaist Thanks for fast reply! I have some NULL entries but without relation it works correctly.

SGaist

Then that's why you have missing entries.

See the QSqlRelationnalTableModel details

JonB

@SGaist
What area in https://doc.qt.io/qt-6/qsqlrelationaltablemodel.html#details are you referring @BushyAxis793 to? I can only think of the point which says:

If a relational table contains keys that refer to non-existent rows in the referenced table, the rows containing the invalid keys will not be exposed through the model. The user or the database is responsible for keeping referential integrity.

I don't see it saying anything about SQL NULLs?

I would guess to refer the OP to void QSqlRelationalTableModel::setJoinMode(QSqlRelationalTableModel::JoinMode joinMode)

Sets the SQL joinMode to show or hide rows with NULL foreign keys. In InnerJoin mode (the default) these rows will not be shown: use the LeftJoin mode if you want to show them.

@BushyAxis793 Is this relevant to your situation, does LeftJoin resolve your issue?

BushyAxis793

@JonB Thanks for reply. It works! However I have one more question. Is it possible to show a few relation from one table? I have a table:

I would like to add:

Dimension1
Dimension2
Dimension3

I tried with:


 tableModel->setTable("clampings");
    
        tableModel->setJoinMode(QSqlRelationalTableModel::LeftJoin);
    
    
    
    
    
        tableModel->setRelation(2,QSqlRelation("clampingtypes","ClampingTypeID","ClampingType"));
    
        tableModel->setRelation(4,QSqlRelation("instruments","InstrumentID","InstrumentDimension1"));
    
        tableModel->setRelation(5,QSqlRelation("instruments","InstrumentID","InstrumentDimension2"));
    
        tableModel->setRelation(6,QSqlRelation("instruments","InstrumentID","InstrumentDimension3"));
    
        tableModel->setRelation(9,QSqlRelation("materialgenres","GenreID","GenreType"));
    
    
    
        tableModel->setHeaderData(1,Qt::Horizontal,tr("Numer ID"));
    
        tableModel->setHeaderData(2,Qt::Horizontal,tr("Rodzaj"));
    
        tableModel->setHeaderData(3,Qt::Horizontal,tr("Nazwa przyrządu"));
    
        tableModel->setHeaderData(4,Qt::Horizontal,tr("Długość [mm]"));
    
        tableModel->setHeaderData(5,Qt::Horizontal,tr("Szerokość [mm]"));
    
        tableModel->setHeaderData(6,Qt::Horizontal,tr("Wysokość [mm]"));
    
        tableModel->setHeaderData(9,Qt::Horizontal,tr("Materiał"));
    
        tableModel->setHeaderData(10,Qt::Horizontal,tr("Opis przyrządu"));
    
        tableModel->setHeaderData(11,Qt::Horizontal,tr("Masa [kg]"));
    
    
    
    
    
    
    
        clampingTableView->setEditTriggers(QAbstractItemView::NoEditTriggers);
    
        clampingTableView->setFocusPolicy(Qt::NoFocus);
    
        clampingTableView->setSelectionBehavior(QAbstractItemView::SelectRows);
    
    
    
    
    
    
    
        clampingTableView->setModel(tableModel);
    
        tableModel->select();
    
    
    
    
    
    
    
    
    
        //Commented means is is visible
    
        clampingTableView->hideColumn(tableModel->fieldIndex("ClampingID"));
    
        //clampingTableView->hideColumn(tableModel->fieldIndex("ClampingNumber"));
    
        //clampingTableView->hideColumn(tableModel->fieldIndex("ClampingTypeID"));
    
        //clampingTableView->hideColumn(tableModel->fieldIndex("ClampingName"));
    
        clampingTableView->hideColumn(tableModel->fieldIndex("ClampingSeller"));
    
        clampingTableView->hideColumn(tableModel->fieldIndex("ClampingLink"));
    
        clampingTableView->hideColumn(tableModel->fieldIndex("InstrumentID"));
    
        clampingTableView->hideColumn(tableModel->fieldIndex("ViseID"));
    
        clampingTableView->hideColumn(tableModel->fieldIndex("ClampingImage"));
    
        //clampingTableView->hideColumn(tableModel->fieldIndex("ClampingMaterialID"));
    
        //clampingTableView->hideColumn(tableModel->fieldIndex("ClampingDescription"));
    
        //clampingTableView->hideColumn(tableModel->fieldIndex("ClampingMass"));
    
        clampingTableView->hideColumn(tableModel->fieldIndex("ClampingPriceTypeID"));
    
        clampingTableView->hideColumn(tableModel->fieldIndex("ClampingPrice"));
    
        clampingTableView->hideColumn(tableModel->fieldIndex("ClampingAddedDateTime"));
    
    
    
    
    
    
    
        clampingTableView->horizontalHeader()->setSectionResizeMode(0,QHeaderView::ResizeToContents);
    
        clampingTableView->horizontalHeader()->setSectionResizeMode(1,QHeaderView::ResizeToContents);
    
        clampingTableView->horizontalHeader()->setSectionResizeMode(2,QHeaderView::ResizeToContents);
    
        clampingTableView->horizontalHeader()->setSectionResizeMode(3,QHeaderView::ResizeToContents);
    
        clampingTableView->horizontalHeader()->setSectionResizeMode(4,QHeaderView::ResizeToContents);
    
        clampingTableView->horizontalHeader()->setSectionResizeMode(5,QHeaderView::ResizeToContents);
    
        clampingTableView->horizontalHeader()->setSectionResizeMode(6,QHeaderView::ResizeToContents);
    
        clampingTableView->horizontalHeader()->setSectionResizeMode(7,QHeaderView::ResizeToContents);
    
        clampingTableView->horizontalHeader()->setSectionResizeMode(8,QHeaderView::ResizeToContents);
    
        clampingTableView->horizontalHeader()->setSectionResizeMode(9,QHeaderView::ResizeToContents);
    
        clampingTableView->horizontalHeader()->setSectionResizeMode(10,QHeaderView::ResizeToContents);
    
    
    
    
    
    
    
        int rowTotalWidth = 0;
    
    
    
        int count = clampingTableView->horizontalHeader()->count();
    
    
    
        for(int i = 0; i<count;++i)
    
        {
    
            if(!clampingTableView->horizontalHeader()->isSectionHidden(i))
    
            {
    
                rowTotalWidth += clampingTableView->horizontalHeader()->sectionSize(i);
    
            }
    
        }
    
    
    
        clampingTableView->setMaximumWidth(780);

The result:

As you can see Dimension3 is visible. How can I add Dimension1 and Dimension2? Is it required to add additional columns? Thanks and have a good day.

JonB

@BushyAxis793
I don't know why your D1/D2 are showing empty while D3 is filled, assuming you are sure they should have values.

Try commenting out

tableModel->setRelation(6,QSqlRelation("instruments","InstrumentID","InstrumentDimension3"));

Does that make D2 work now??

If you comment out

 tableModel->setRelation(5,QSqlRelation("instruments","InstrumentID","InstrumentDimension2"));

does that make D2 show any value without the relation imposed?

BushyAxis793

@JonB I noticed that when I set the relation to column 6. it displays correctly, but only one dimension. Example:

    tableModel->setRelation(2,QSqlRelation("clampingtypes","ClampingTypeID","ClampingType"));
    tableModel->setRelation(6,QSqlRelation("instruments","InstrumentID","InstrumentDimension1"));
    tableModel->setRelation(7,QSqlRelation("instruments","InstrumentID","InstrumentDimension2"));
    tableModel->setRelation(8,QSqlRelation("instruments","InstrumentID","InstrumentDimension3"));
    tableModel->setRelation(9,QSqlRelation("materialgenres","GenreID","GenreType"));

The result

When I set relation for Dimension2 for column 6 it shows data from dimension2 but never all dimensions. I have no idea why.

EDIT: I suppouse I have to add additional columns for each dimension.

SGaist

Do all these rows contain all data required ?

BushyAxis793

@SGaist Yes. Below is my main table "clampings":

JonB

@BushyAxis793 said in QSQLRelationalTableModel display relation:

tableModel->setRelation(6,QSqlRelation("instruments","InstrumentID","InstrumentDimension1"));
tableModel->setRelation(7,QSqlRelation("instruments","InstrumentID","InstrumentDimension2"));
tableModel->setRelation(8,QSqlRelation("instruments","InstrumentID","InstrumentDimension3"));

I am having trouble understanding what is going on here. Maybe it doesn't help for me that your column display names are in Polish. And I don't understand what you are saying when you write

EDIT: I suppouse I have to add additional columns for each dimension.

These 3 lines mean: columns #6, #7 & #8 in tableModel each contain an "InstrumentID". The code should use the value in that column in the original table to locate (via InstrumentID) the corresponding row in table instruments. Then it should display from that row InstrumentDimension1/2/3, which must already be columns in instruments.

It is a little "unusual" to display more than one column from the foreign key table. I do not know that Qt does not support that for some reason, but maybe for a given QSqlRelation("instruments","InstrumentID", ...), i.e. foreign key table+lookup-column, it only allows one relation with one display column. Hence you only see one of the InstrumentDimension1/2/3 mappings?

Although it's not well documented, QSqlRelation actually allows multiple columns to be specified for the "display" column. It might be interesting to see what

tableModel->setRelation(6,QSqlRelation("instruments","InstrumentID","InstrumentDimension1, InstrumentDimension2, InstrumentDimension3"));

produces for you (you may need to comment out the relations for columns #7 & #8). ~~That column type would be string.~~

BushyAxis793

@JonB We got it!

This part of code was a solution:

tableModel->setRelation(6,QSqlRelation("instruments","InstrumentID","InstrumentDimension1, InstrumentDimension2, InstrumentDimension3"));

Thank you very much!
Have a great day :)