Showing a table with a foreign key

Let's say we have a database table with a foreign key, like the department table I've described in this post.

Hardly what we really want is showing to the user the foreign key value. That makes little o no sense at all to him. In the current case, instead of seeing the numeric value representing the id of the location where the department insist, it would be better to see the name of the that location.

Luckly it is easy to do that with Qt: instead of using a QSqlTableModel we have to refer to the QSqlRelationalTableModel. Besides, we can do even something more interesting: giving the user the chance to see all the available choices for that field in a drop down list. Especially useful when we want to give the user to change the foreign key value.

Let's see the code for showing the department table to the user:

QSqlRelationalTableModel* departmentModel = new QSqlRelationalTableModel(this);
departmentModel->setTable("department");
departmentModel->setRelation(Department_LocationId, QSqlRelation("location", "id", "name")); // 1.
departmentModel->setSort(Department_Name, Qt::AscendingOrder);
departmentModel->setHeaderData(Department_Name, Qt::Horizontal, tr("Dept."));
departmentModel->setHeaderData(Department_LocationId, Qt::Horizontal, tr("Location"));
departmentModel->select();

ui->viewDept->setModel(departmentModel); // 2.
ui->viewDept->setItemDelegate(new QSqlRelationalDelegate(this)); // 3.
ui->viewDept->setSelectionMode(QAbstractItemView::SingleSelection);
ui->viewDept->setSelectionBehavior(QAbstractItemView::SelectRows);
ui->viewDept->setColumnHidden(Department_Id, true);
ui->viewDept->resizeColumnsToContents();
ui->viewDept->horizontalHeader()->setStretchLastSection(true);

1. calling setRelation() we specify how to manage the foreign key. We are telling the model that instead of the location id we should go to the location table, using its id column as index and name as the actual field we want displaying.
2. viewDept is the QTableView where we are about to display the department table. Here we specify that we should use the departmentModel just created as source for the data.
3. specifying the usage of a new QSqlRelationalDelegate we are saying to the view that we want show to the user a drop down list with available choices for that field.

I wrote this post while reading "C++ GUI Programming with Qt 4, Second Edition" by Jasmin Blanchette and Mark Summerfield.