Nominate our 2022 Qt Champions!

Populate QTreeView from QSqlTableModel?

  • I have a table with data formatted like this
    "10";"00";"00";"00";"Live Plant and Animal Material and Accessories and Supplies"
    "10";"10";"00";"00";"Live animals"
    Which I would like to display in a treeview like this
    Segment - Title
    ----Family - Title
    ----Class - Title
    ----Commodity - Title

    How would I go about doing this?
    Am I looking at the problem the wrong way?

  • You will have to build your own model I am afraid. AFAIK, there is no (proxy) model available that will do a transform from a table into a tree automatically.

  • I had a feeling that was what it would ultimately be after 3 hours of scouring the internet for someone else having a similar problem and coming up empty. Thanks for pointing me in the right direction.

  • if you need a tree, you need to tell us wich one inside your database table make the reference to the parent (or the child). Because for sure, you need to refere at a moment to a tree organisation, wich include a hirerchical reference.

    If your table not content this column hierachy, you need to create one OR go by an "interface code" able to point this hierarchy.

    So... after that you can use it better.

    Also, for my own little experiencies around this, i gone by QSqlQuery and construct a QHash for make the tree and mount the view.

    But now, i try to implement a QAbstractItemModel (and call the database from it) with the QTreeView.... i try...

    but if you need some codes idea for construct a tree from a table... i have it with QSqlQuery (and can provide by SqlModel too..).

    tell me first wich is the hierarchy reference to read from table (or the logic to respect for obtain a herarchy tree).

  • The hierarchy looks like this:
    Segment is a child of the root
    Family is a child of Segment
    Class is a child of Family
    Commodity is the leaves(I think that's the word used) of the Class level,

    Would it be easier to split it into multiple tables with FK constraints to the level above them?


    CREATE TABLE unspsc_segment(
    segment_code VARCHAR(2) PRIMARY KEY,
    segment_title VARCHAR(100)

    CREATE TABLE unspsc_family(
    parent_code VARCHAR(2) NOT NULL,
    class_code VARCHAR(2) PRIMARY KEY,
    class_title VARCHAR(100)
    So on and so forth with a foreign key constraint to the parent level?
    Or am I going on a wild tangent and should just loop through the table and build the nodes by hand?
    Am I making too much work for myself and the item version would actually be the correct way to go instead of the view?

  • In terms of making the model: I don't think it matters much if you have the data in a single table or in multiple ones.

    How big is your data set? Would it be problematic to load the whole table into memory?

    In principle, you would not have to load everything in memory. You'd just have to use different queries for every level, and only load on expanding nodes using the query for that level.

    For the base level, you would use a query like this:
    SELECT DISTINCT segment FROM unspsc ORDER BY segment;

    and create a node for every result in that set.

    However, creating tree models is not trivial at all. When starting with creating your own models, I'd not recommend you start with the most complex shape of them*. Instead, start with implementing a list or a table, and when you get that working nicely, try your hand at creating a tree.

    *) A tree is actually not the most complex shape, but it is the most complex one Qt provides a standard view for. In principle Qt item models model a nested 2D table, where every cell can be the parent of a new 2D table.

  • in the fact that all line has not to be referenced from an other one, this would be more simple.
    so, if i well understand, you will use only 2 column inside your tree... one for the "segment/familly/class/commodity" and one for title.

    Like tell you Andre, the best way could be to only call SQL request for first parents level (and first level childs if you want to see the arrow...), and from your view, you can catch the event signal when you unroll a tree node for call the child field values of next childs level 2.

    But tipically, your datas would not take advantage of tree organisation. So make the choice of a tree is only for apparence, but you could use the table easier with this datas.

    OR... i don't know, but i try to understand the idea in the table organisation of datas... and looks like you would like to have a first column "title", and a second one where you can choose wich kind of data linked to this title you want to see...
    for exemple, column 0 for title, and inside the header of column 1, a combobox for choose wich data to view inside the column 1.
    Also, if the type int of datas contains inside your "segment/familly/class/comodity" are reference to something can be readable, you can link them with QSqlRelationnalTableModel.
    You can also use a simple list and populate the tooltip of each title unique column with datas contains in the other colomns in real time...

    At the end, if your data table is big, the idea could also be to load datas depended by the viewport area...
    But by this way, you no need tree and just can use table and a combobox inside the header column 1... (i'm not sure it is a good idea... for your need).

    If you are like me (beginner), i would think about: big reflexion first on the construction style to choose. But for that, sure, you need to know what Qt can do and how it do...

    good luck

  • To update anyone who was interested, I gave up on modelling it, the dataset is readonly to begin with, so I went with a QSqlQuery to pull each level and build out the tree manually.

    Thanks for the input.

Log in to reply