QSqlRelationalTableModel - two references to the same table, same foreign keys

Question

I have 3 tables:

I would like to show the Orders table (order's start and end date, user's last name, service name and service price) on GUI using QTableView and QSqlRelationalTableModel. Here's where I set up the table and the view:

this->ordersTable = new QTableView(this);
this->model = new QSqlRelationalTableModel(this, db->getDB());
this->model->setTable("ORDERS");
this->model->setRelation(3, QSqlRelation("USERS", "id", "lastname"));
this->model->setRelation(4, QSqlRelation("SERVICE", "id", "name"));
this->model->setRelation(4, QSqlRelation("SERVICE", "id", "price"));
this->model->select();
this->ordersTable->setModel(this->model);
this->ordersTable->hideColumn(0);

But when I do the third setRelation call, it seems, it overwrites the second call: I can only see the price on GUI, not both the name AND the price. And I need to put both fields - name and price from Services table to my view.

Answer 1

It seems setRelation is used to resolve only one foreign key, but you want to add two columns. In this case you can use QSqlQueryModel to apply your own customized query.

QSqlQueryModel model;
QString q = "your sql query";
model.setQuery(q, db->getDB());
tableView->setModel(model);

For the query, you could achieve your goal with a simple inner join query.

SELECT O.ID, O.START_TIME, O.END_TIME, U.LASTNAME, S.NAME, S.PRICE
    FROM ORDERS O
        INNER JOIN USERS U
            ON O.USER_ID = U.ID
        INNER JOIN SERVICE S
            ON O.SERVICE_ID = S.ID

Answer 2

Try this:

this->model->setRelation(4, QSqlRelation("SERVICE", "id", "name, price"));