Way to load all of data from 2 tables into one QTableView?

[Kevin Hoang]

I have 2 tables; between them have a one-to-many relationship. They have a one-to-many relationship with a language table.

So, I want load all of data in 2 tables above into one Table View, edit and add new record to them. What way I can do? Help me….

[waynew]

Create a database view and base your model on the view.

[Kevin Hoang]

Thanks waynew!

Does QT support to create a view tables?

[waynew]

I don't know what database you are using, but the general syntax would be:

"CREATE view my_view col_1, col_2 as SELECT col1, col2 from table1, table2, where table1.<id_col> = table2.<id_col>"

Then set your model table to my_view.

where table1 contains columns col1 and id_col and table2 contains columns col2 and id_col.
You must join the tables on columns that will hold the same data.
For Sqlite, see here: http://www.hwaci.com/sw/sqlite/lang.html and here: http://www.hwaci.com/sw/sqlite/lang_createview.html

[Kevin Hoang]

I'm using SQLite.

Follow 2 links you recommended, I get new problem: "Views are read-only in SQLite. I cannot DELETE, INSERT, or UPDATE a view"

what next I should do with this task?

[toutarrive]

About SQLite:
You cannot DELETE, INSERT, or UPDATE a view. Views are read-only in SQLite. However, in many cases you can use an INSTEAD OF trigger on the view to accomplish the same thing
Look here http://www.hwaci.com/sw/sqlite/lang_createtrigger.html#instead_of_trigger
It should be something like that (data, text are two tables, and one view you create )


CREATE TRIGGER view_insert INSTEAD OF INSERT ON view
BEGIN
INSERT INTO data (....) VALUES (....);
INSERT INTO text (....) VALUES (....);
END;


You'll find a link here, it's in french, but it should be useful http://www.developpez.net/forums/d795295/bases-donnees/autres-sgbd/sqlite/trigger-tables-liees/

[Kevin Hoang]

Thanks to toutarrive: I got it! The QT seemed doesn't support to create a view and trigger, I have to make query for this.

I have another question about this: How may I insert, update, delete a table has many-to-many relationship on TableView? Example: I want to add or edit a product. The product table has many-to-many relationship with collection table and language table.

[toutarrive]

You'll need a junction table. See SQLite documentation

[Kevin Hoang]

Yes, I see.
Does QTableView and QSql...Model support to do this?