3

I have 3 tables:

enter image description here

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.

yak
  • 3,770
  • 19
  • 60
  • 111

2 Answers2

4

Try this:

this->model->setRelation(4, QSqlRelation("SERVICE", "id", "name, price"));
Gerator
  • 41
  • 1
2

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
frogatto
  • 28,539
  • 11
  • 83
  • 129
  • This could be the solution, however, I forgot to mention, that I need to make changes in DB, and the docs say that `The QSqlQueryModel class provides a read-only data model for SQL result sets`, so, I need something which won't be read-only. Thanks anyway :) – yak Feb 22 '16 at 20:25