Joining tables into QTableView

[SykeS]

Hi!

I have the following tables structure:

classes (
id integer primary key,
name integer
);
 
subjects (
id integer primary key,
name varchar,
short_name varchar
);
 
subjects_relations (
classID integer foreign key,
subjectID integer foreign key
);
 
students (
id integer primary key,
name varchar,
surname varchar,
class integer foreign key
);
 
marks (
id integer primary key,
value integer, //in Poland we have digit marks ;)
studentID integer foreign key,
subjectID integer foreign key
);

classes (
id integer primary key,
name integer
);

subjects (
id integer primary key,
name varchar,
short_name varchar
);

subjects_relations (
classID integer foreign key,
subjectID integer foreign key
);

students (
id integer primary key,
name varchar,
surname varchar,
class integer foreign key
);

marks (
id integer primary key,
value integer, //in Poland we have digit marks ;)
studentID integer foreign key,
subjectID integer foreign key
);

To copy to clipboard, switch view to plain text mode 

Ps.: Only one mark can be connected with specified subject and student.

Now: I want to make a table (QTableView) like this:

***************| math | eng
-----------------------------
David Smiths   |  3   |  5
John Doe       |  5   |  4

***************| math | eng
-----------------------------
David Smiths   |  3   |  5
John Doe       |  5   |  4

To copy to clipboard, switch view to plain text mode 

So: columns are subjects connected to a specified class. Rows are students from this class. Cells are marks. Columns labels are subject short_name (this is no problem) and rows labels are name and surname (this is a problem).

How to make that table with one of the Sql Models? I want to mention that I want to edit marks in table and save them with submitAll();.

Cheers,
David

[SykeS]

Anyone? Please...

[tbscope]

I would start with a QSqlRelationalTableModel.

Set it to use the marks table and then use setRelation to fill in the foreign keys.

See for an example here: http://doc.qt.nokia.com/4.6/sql-rela...model-cpp.html

[SykeS]

What about setting vertical labels to Sql Model?

[tbscope]

By default, only the labels for the horizontal header are set.
To set custom labels for your vertical header, subclass the model and reimplement headerData(...)

[waynew]

There is also another way - that is to create a view of the joined tables then set the model table to the view.

[SykeS]

Thanks tbscope.

There is also another way - that is to create a view of the joined tables then set the model table to the view.

Unfortunately views in SQLite are read-only.

[SykeS]

I don't belive it's so hard...

Ok, forget about vertical labels. I have 3 questions.

1. Is this even possible?

2. Let's say I have query that selects data in way I want. Is there possibility to edit QSqlQueryModel like QSqlTableModel? I know that I have to subclass QSqlQueryModel, but I can't.

3. If above ways are not possible, how should I manage data (maybe files, or XML?) to make my program.

[numbat]

Do you have a SQL query that displays the data as you want? It looks like you want vector data in both the horizontal and vertical directions. Short of a lot of joins, I'm interested in how this could be achieved. If you have an SQL statement, it should be possible to create a manual model.

[ChrisW67]

The table is a classic pivot table (or cross-tab). How this is constructed in SQL varies from vendor to vendor. Difficulty varies and sometimes can only be done with a temporary table.

Assuming you have a query that will generate the pivoted table: Updating the underlying data through a model displaying the pivot table through QSqlQueryModel is not possible. You will have to subclass QSqlQueryModel to provide the setData() method and the flags() method. There's an example in the reference documentation: Creating New Models

Another approach could be creating a derivative of QAbstractTableModel (or QSqlTableModel) and overriding the data(), setData() and flags() virtual methods. Internally the data() method could take the row and column, map them to a student and subject and perform a query to find the right value to return. On setData() the query generated is an UPDATE against the correct table(s).

[numbat]

If you have SQL statements to read and delete, you can probably use the manual SQL query model I posted here.