Displaying custom field from child table using QSqlRelationalTableModel and insert data in parent table.

Ajesh Kumar T.T.

Hi,
I have two tables, one is Student and another is Exam.
Here, student have details like ID, name, etc and Exam table have Student ID which is foreign key of Student, mark, last exam date.
I have a class for displaying the data of Student and it is derived from QSqlRelationalTableModel.
But, the problem is I have to display the data from Student table by taking the some customized data like maximum mark and last examination date from Exam table.

For this, I have used set relation property as follows:

setTable("Student");
setJoinMode(QSqlRelationalTableModel::LeftJoin);
setRelation(0, QSqlRelation("(SELECT StudentID, MAX(Mark) AS MaximumMark,"
                            "MAX(LastExamDate)AS LastExamDate FROM Exam GROUP BY StudentID)",
                            "StudentID", "MaximumMark,LastExamDate,StudentID"));

I have added the role names of these additional fields as follows:

const int EXAM_COL_COUNT = 2;
int columnIndex = 0;
roleNameMap.insert(Qt::UserRole + columnIndex++,          
                   QString("MaximumMark").toLocal8Bit());  
roleNameMap.insert(Qt::UserRole + columnIndex++,          
                   QString("LastExamDate").toLocal8Bit());
for(; columnIndex < record.count() + EXAM_COL_COUNT; ++columnIndex)
        {
            this->roleNameMap.insert(Qt::UserRole + columnIndex,
                                     record.fieldName(columnIndex - STUDY_COL_COUNT).toLocal8Bit());
        }

Now I got the result with following column order.

MaximumMark      LastExamDate   StudentID    Name     Address   ...

But, while trying to insert a new student details using this model(insertrecord is used for inserting data), it shows the following sql error.

QSqlError("1", "Unable to execute statement", "table Student has no column named LastExamDate")

How I can avoid the additional columns like MaximumMark and LastExamDate from insertRecord? Of course, we can use QSqlQuery and direct insert query can solve this issue. But I have to insert the data using QSqlRelationalTableModel. It is ok for me to override any methods of QSqlRelationalTableModel or parent classes to meet the requirement.

Thanks in advance.

SGaist

Hi and welcome to devnet,

On thing you can do is to add a proxy model between your view and your actual model. That proxy can the add additional columns that won't change the handling of the underlying model.

Hope it helps

Ajesh Kumar T.T.

@SGaist Hi SGaist,

Thank you for the support.
I am new in Qt world and lack of knowledge in using proxy model along with QSqlRelationalTableModel.
Anyway I shall try with proxy model.

Thank again for the quick reply.

Ajesh Kumar T.T.