Presenting nested database data in TreeView

8105

Good evening everyone.

I would like to get some suggestions on my little situation.

Here's the portion of the relational database that I'm reading from (and eventually writing to):

floors:
number: varchar(5)
capacity: varchar(5)

rooms:
roomNumber: varchar(5)
#floorNumber: varchar(5)
beds: varchar(3)

My aim is to present this data in a tree like layout. The first level presents the floor number and the number of rooms on that floor. The second level contains the rooms (under every floor tree node we present rooms' properties).

I was able to present the first level using a TreeView, and also able to put rooms in TreeView's grouped by floors but I failed to figure how to insert those individual TreeView's under the correct main TreeView node.

Here's a screenshot of my current incomplete results:
alt text

Here's the piece of code that allowed me to do this:

//"Hotel" is the QSqlDatabase identifier
    QSqlQueryModel* floorsModel = new QSqlQueryModel;
    QSqlQuery floorsQuery(Hotel);
    QString q = "select floors.number, count(rooms.floornumber) from floors, rooms where floors.number=rooms.floornumber group by floors.number, rooms.floornumber;";
    floorsQuery.prepare(q);
    floorsQuery.exec();
    floorsModel->setQuery(floorsQuery);
    ui->floorsAndRooms->setModel(floorsModel);
//"floorsAndRooms" is the designer QTreeView
    floorsModel->setHeaderData(0, Qt::Horizontal, "Floor #");
    floorsModel->setHeaderData(1, Qt::Horizontal, "Rooms");

    int nFloors = floorsModel->rowCount();
    QString floorNumber;
    for(int i = 0; i != nFloors; i++)
    {
         floorNumber = floorsModel->record(i).value("number").toString();
         QSqlQuery roomsQuery(Hotel);
         roomsQuery.prepare("select * from rooms where floorNumber=:fNumber");
         roomsQuery.bindValue(":fNumber",floorNumber);
         roomsQuery.exec();
         QSqlQueryModel* roomsModel = new QSqlQueryModel;
         roomsModel->setQuery(roomsQuery);
         QTreeView* rooms = new QTreeView;
         rooms->setModel(roomsModel);
         rooms->show();
    }

I hope you understand my aim and welcome any radical changes I may need to implement to better present the needed data.

Thank you for your time.

VRonin

would you be ok with something like this (imagine a tree with floor 1 branch open :

Floor#	Rooms	Room Number	Beds
1	5
		0	2
		1	4
		2	1
		3	2
		4	4
2	7
3	5
4	3

or do you need the actual TableView with separate headers to appear when you expand the branch? (spoiler alert the first is very easy, the second relatively more complicated)

8105

Thank you for your suggestion. I think it's what I'll eventually settle for, fairly easy to indeed, using just one SQL query.
Although, I'm starting to think ... wouldn't my original aim be easier to fulfil using TreeWidget and not TreeView?

Unsolved Presenting nested database data in TreeView