QSql*Model + QTreeView : make a tree

[punkypogo]

Hello,

I would like to have the data contained in an SQL table shown in a treeview (I already have that), but I would like items to be group (kind of filter) by types. See the exemple.

Here is my table "MultimediaFiles" structure:
- id,
- name,
- path,
- type (can be, "picture", "video", "sound").

So, I would like my records to be presented in a treeview like this :




|-- Name --|-- Path --|
------------------------------------
- picture

|- foo.png C:\...
- video

|- bar.avi C:\...
|- toto.mpg C:\...
- sound

|- huhu.wav ...
|- haha.mp3 C:\...



What I already did :


SQL::SQL()
{
QSqlDatabase mDb = QSqlDatabase::addDatabase( "QMYSQL" );
mDb.setHostName( "localhost" );
mDb.setDatabaseName( "mydb" );
mDb.open();
setupModel();
}

void SQL::setupModel()
{
pModel = new QSqlRelationalTableModel();
pModel->setTable( "MultimediaFiles" );
if( !pModel->select() )
{
qDebug() << "pb select";
qDebug() << pModel->lastError().text();
}

treeView->setModel( pModel );
/*...*/
treeView->show();
}

But it doesn't act as I want : there is no node created.
So, do I miss something, or do I need to make my own model, or add treeItems by hands ?



Thanks in advance.

[wysota]

The model you used is a flat model and no matter what view you use it won't become structured just by itself. You need to provide a proxy model that will represent your table as a tree. See QAbstractProxyModel for details.

[punkypogo]

Great, I take a look.

Thank you.

[punkypogo]

I'm back. I'm totally lost :/

I understood I have to subclass QAbstractProxyModel, but I really don't know what to write in the methods.
Could you please help me a little bit ?

[wysota]

The proxy is to transform a flat model into a tree. You need to return proper model indexes and parent indexes. If you don't feel comfortable enough with implementing your own proxy model (which is not very simple) I suggest you use QStandardItemModel and manually fill it with data fetched from the sql table.

[punkypogo]

Hello,

Well, I want to learn, so I will try to make a proxy. ;)

Tell me if I'm wrong :
- I create a TreeModel class (based on the Simple Tree model example from doc.trolltech.com)
- Every time my Qsql*Model will change, I will populate my TreeModel through the Proxy.
- The proxy will make the correspondance between the Qsl*Model and my TreeModel.

Does it sound good ?

[wysota]

No. You need to subclass QAbstractProxyModel.

[vladeck]

ProxyModel is deprecated... You should take a look at simple tree view example. You can make a tree model from flat sql result by choosing what columns will be the tree parent creating initiator (I'm bad with english :) )

For example:


Column_001 | Column_002
-----------+-----------
value_001 | value002
value_001 | value003
value_002 | value004
value_002 | value005
value_003 | value006

You should get (with the use of some code logic):


value_001
+-- value_002
+-- value_003
value_002
+-- value_004
+-- value_005
value_003
+-- value_006

[patrik08]

I build mysql netset on a tree ... to infinit loop whithout proxie or so..
The query is long but running...

#define TREECAT "SELECT n.*, round((n.rgt-n.lft-1)/2,0) AS childs, count(*)+(n.lft>1) AS level, ((min(p.rgt)-n.rgt-(n.lft>1))/2) > 0 AS lower, (( (n.lft-max(p.lft)>1) )) AS upper FROM FAKTURA_SUB n, FAKTURA_SUB p WHERE n.lft BETWEEN p.lft AND p.rgt AND (p.root_id = n.root_id) AND (p.id != n.id OR n.lft = 1) GROUP BY n.root_id,n.id ORDER BY n.root_id,n.lft"

You running only level 1 rows and append to QStandardItemModel(); root
all sub level go to self on composing ....
and you can move level up or dow append ....

a tree online demo http://www.klempert.de/nested_sets/demo/





QList<QStandardItem *> Cat_Tree::Compose( int rowposition )
{
QSqlRecord r = ReadModel->record(rowposition);
int level;
int childs;
QList<QStandardItem *> Horrizzontal_Line;
for (int x = 0; x < DisplayFields.size(); ++x) {

Horrizzontal_Line.append( xxxxx )
}
/* check on qmap unique ! */
if (!OnBox(rowposition)) {
RowUnique.insert(rowposition,level);
}
/* if moore childs append ..... loops */
if (childs > 0 ) {
for (int i = 0; i < childs; ++i) {
const int jumpto = i + 1 + rowposition;
if (!OnBox(jumpto)) {
diritto.first()->appendRow(Compose(jumpto));
}
}
}
return diritto;
}

[wysota]

ProxyModel is deprecated...
Oh, really? Very interesting... since when?

[jacek]

Oh, really? Very interesting... since when?
4.1 or something. Just when QAbstractProxyModel and QSortFilterProxyModel came in to replace it.

[wysota]

4.1 or something. Just when QAbstractProxyModel and QSortFilterProxyModel came in to replace it.

Gee... isn't that QAbstractProxyModel I was writing about? I could have sworn the "abstract" word was there... and it still is, isn't it?

[jacek]

I could have sworn the "abstract" word was there... and it still is, isn't it?
Not in vladeck's post. :p

[wysota]

I know that and you know that. Does he know that?

[punkypogo]

Hello,

I finally gave up with the idea of subclassing a QAbstractProxyModel because I had no idea how to do that ...
So I used a QStandardItemModel and everything works now.

If someone has a solution with a proxy, I would be very glad to see it.



Anyway, thanks to all the people who tried to help me.

[vladeck]

Here is the working code I use for making tree model out of sql query:

TreeItem.hpp:

/**
@file
*/

#ifndef TEST_DATA_TREEITEM_HPP
#define TEST_DATA_TREEITEM_HPP

// Qt headers
#include <QtCore/QList>
#include <QtCore/QVariant>


namespace test{
namespace data {

class TreeItem
{
public:
TreeItem(const QList<QVariant>& data, TreeItem* parent = 0);
~TreeItem();

void AddChild(TreeItem* child);
TreeItem* GetChild(int rowIndex);
int GetNumberOfChildren() const;
int GetNumberOfColumns() const;
QVariant GetData(int columnIndex) const;
int GetRowIndex() const;


TreeItem* GetParent();

private:
QList<TreeItem*> _Children;
QList<QVariant> _ItemData;
TreeItem* _Parent;
};

} // namespace data
} // namespace test

#endif // TEST_DATA_TREEITEM_HP

TreeItem.cpp:

/**
@file
*/

// Project headers
#include "TreeItem.hpp"


namespace test {
namespace data {

TreeItem::TreeItem(const QList<QVariant>& data, TreeItem* parent)
{
_Parent = parent;
_ItemData = data;
}

TreeItem::~TreeItem()
{ qDeleteAll(_Children); }

void TreeItem::AddChild(TreeItem* child)
{ _Children.append(child); }

TreeItem* TreeItem::GetChild(int rowIndex)
{ return _Children.value(rowIndex); }

int TreeItem::GetNumberOfChildren() const
{ return _Children.count(); }

int TreeItem::GetNumberOfColumns() const
{ return _ItemData.count(); }

QVariant TreeItem::GetData(int columnIndex) const
{ return _ItemData.value(columnIndex); }

TreeItem* TreeItem::GetParent()
{ return _Parent; }

int TreeItem::GetRowIndex() const
{

if (_Parent)
return _Parent->_Children.indexOf(const_cast<TreeItem*>(this));

return 0;
}

} // namespace data
} // namespace test

SqlTreeModel.hpp:

/**
@file
*/

#ifndef TEST_DATA_SQLTREEMODEL_HPP
#define TEST_DATA_SQLTREEMODEL_HPP

// Qt headers
#include <QtCore/QAbstractItemModel>
#include <QtSql/QSqlQuery>


namespace test {
namespace data {

// Forward declarations
class TreeItem;

class SqlTreeModel : public QAbstractItemModel
{
Q_OBJECT

public:
SqlTreeModel(QObject* parent = NULL);
~SqlTreeModel();

void SetQuery(const QSqlQuery& query);
bool Select();

void SetGroupByIndexes(const QList<int>& columnIndexes);
void SetGroupTitleFormat(const QString& format);

QVariant GetData(const QModelIndex& index, int role) const;
Qt::ItemFlags GetFlags(const QModelIndex& index) const;
QVariant GetHeaderData(int section, Qt::Orientation orientation, int role = Qt::DisplayRole) const;
QModelIndex GetIndex(int rowIndex, int columnIndex, const QModelIndex& parent = QModelIndex()) const;
QModelIndex GetParent(const QModelIndex& index) const;
int rowCount(const QModelIndex& parent = QModelIndex()) const;
int columnCount(const QModelIndex& parent = QModelIndex()) const;

private:
QVariant data(const QModelIndex& index, int role) const;
QVariant headerData(int section, Qt::Orientation orientation, int role = Qt::DisplayRole) const;
QModelIndex index(int rowIndex, int columnIndex, const QModelIndex& parent = QModelIndex()) const;
QModelIndex parent(const QModelIndex& index) const;

TreeItem* _Root;

QSqlQuery _Query;
QList<int> _GroupColumns;
QString _GroupTitleFormat;

void Create(TreeItem* parent);
};

} // namespace data
} // namespace test

#endif // TEST_DATA_SQLTREEMODEL_HPP

SqlTreeModel.cpp:

/**
@file
*/

// Project headers
#include "TreeItem.hpp"
#include "SqlTreeModel.hpp"

// Qt headers
#include <QtCore/QTextStream>
#include <QtSql/QSqlRecord>


namespace test {
namespace data {

SqlTreeModel::SqlTreeModel(QObject* parent /* = NULL */)
: _Root(NULL),
QAbstractItemModel(parent)
{
_GroupColumns << 0;
_GroupTitleFormat = "{0}";
}

SqlTreeModel::~SqlTreeModel()
{
if (_Root != NULL)
delete _Root;
}

void SqlTreeModel::SetQuery(const QSqlQuery& query)
{ _Query = query; }

bool SqlTreeModel::Select()
{
if (!_Query.isActive())
return false;

QSqlRecord record = _Query.record();
const int record_count = record.count();
QList<QVariant> root_data;

for (int i = 0; i < record_count; ++i)
{
if (_GroupColumns.indexOf(i) == -1)
root_data << record.fieldName(i);
}

_Root = new TreeItem(root_data);
Create(_Root);

return true;
}

void SqlTreeModel::SetGroupByIndexes(const QList<int>& columnIndexes)
{ _GroupColumns = columnIndexes; }

void SqlTreeModel::SetGroupTitleFormat(const QString& format)
{ _GroupTitleFormat = format; }

QVariant SqlTreeModel::GetData( const QModelIndex& index, int role ) const
{
return data(index, role);
}

QVariant SqlTreeModel::data(const QModelIndex& index, int role) const
{
if (!index.isValid())
return QVariant();

if (role != Qt::DisplayRole)
return QVariant();

TreeItem* item = static_cast<TreeItem*>(index.internalPointer());
return item->GetData(index.column());
}

Qt::ItemFlags SqlTreeModel::GetFlags(const QModelIndex& index) const
{
if (!index.isValid())
return 0;

return Qt::ItemIsEnabled | Qt::ItemIsSelectable;
}

QVariant SqlTreeModel::GetHeaderData( int section, Qt::Orientation orientation, int role /*= Qt::DisplayRole*/ ) const
{
return headerData(section, orientation, role);
}
QVariant SqlTreeModel::headerData(int section, Qt::Orientation orientation, int role) const
{
if (orientation == Qt::Horizontal && role == Qt::DisplayRole)
return _Root->GetData(section);

return QVariant();
}

QModelIndex SqlTreeModel::GetIndex( int rowIndex, int columnIndex, const QModelIndex& parent /*= QModelIndex()*/ ) const
{
return index(rowIndex, columnIndex, parent);
}

QModelIndex SqlTreeModel::index(int rowIndex, int columnIndex, const QModelIndex& parent) const
{
if (!hasIndex(rowIndex, columnIndex, parent))
return QModelIndex();

TreeItem *parent_item;

if (!parent.isValid())
parent_item = _Root;
else
parent_item = static_cast<TreeItem*>(parent.internalPointer());

TreeItem *child_item = parent_item->GetChild(rowIndex);

if (child_item)
return createIndex(rowIndex, columnIndex, child_item);
else
return QModelIndex();
}

QModelIndex SqlTreeModel::GetParent( const QModelIndex& index ) const
{
return parent(index);
}

QModelIndex SqlTreeModel::parent(const QModelIndex& index) const
{
if (!index.isValid())
return QModelIndex();

TreeItem* child_item = static_cast<TreeItem*>(index.internalPointer());
TreeItem* parent_item = child_item->GetParent();

if (parent_item == _Root)
return QModelIndex();

return createIndex(parent_item->GetRowIndex(), 0, parent_item);
}

int SqlTreeModel::rowCount(const QModelIndex& parent) const
{
TreeItem* parent_item;
if (parent.column() > 0)
return 0;

if (!parent.isValid())
parent_item = _Root;
else
parent_item = static_cast<TreeItem*>(parent.internalPointer());

return parent_item->GetNumberOfChildren();
}

int SqlTreeModel::columnCount(const QModelIndex& parent) const
{

if (parent.isValid())
return static_cast<TreeItem*>(parent.internalPointer())->GetNumberOfColumns();
else
return _Root->GetNumberOfColumns();
}

void SqlTreeModel::Create(TreeItem* parent)
{
TreeItem* current_parent = parent;

while (_Query.next())
{
QString group_title = _GroupTitleFormat;
const int group_columns_count = _GroupColumns.count();
const int number_of_columns = _Query.record().count();

for (int i = 0; i < group_columns_count; ++i)
{
const int column_index = _GroupColumns.at(i);

QString column_arg;
QTextStream arg_stream(&column_arg);
arg_stream << "{" << column_index << "}";

group_title.replace(column_arg, _Query.value(column_index).toString());
}

if (current_parent->GetData(0).toString() != group_title)
{
QList<QVariant> parent_data;
parent_data << group_title;

for (int i = 1; i < number_of_columns; ++i)
parent_data << "";

TreeItem* parent_item = new TreeItem(parent_data, parent);
parent->AddChild(parent_item);
current_parent = parent_item;

QList<QVariant> child_data;

for (int j = 0; j < number_of_columns; ++j)
{
if (_GroupColumns.indexOf(j) == -1)
child_data << _Query.record().value(j);
}

TreeItem* child_item = new TreeItem(child_data, current_parent);
current_parent->AddChild(child_item);
}
else
{
QList<QVariant> child_data;

for (int i = 0; i < number_of_columns; ++i)
{
if (_GroupColumns.indexOf(i) == -1)
child_data << _Query.record().value(i);
}

TreeItem* child_item = new TreeItem(child_data, current_parent);
current_parent->AddChild(child_item);
}
}
}

} // namespace data
} // namespace test

Use case:


QList<int> group_column_indexes;
group_column_indexes << 0; // group by column 0... can be any combination of column indexes

test::data::SqlTreeModel* model = new test::data::SqlTreeModel();
model->SetQuery(query); // SELECT ... statement, for example
model->SetGroupByIndexes(group_column_indexes);
// Note, that you must format this using same indexes for grouping...
// if we choose to hide columns 0, 3 & 4, then the code/format would be something like:
//
// group_column_indexes << 0 << 3 << 4
// ...
// model->SetGroupTitleFormat("Group {0} is grouped with {3} and {4}");
model->SetGroupTitleFormat("Group {0}");
model->Select();

_TrvPackages->setAllColumnsShowFocus(false);
_TrvPackages->setModel(model);

for (int i = 0; i < _TrvPackages->model()->rowCount(); ++i)
_TrvPackages->setFirstColumnSpanned(i, QModelIndex(), true);

_TrvPackages->expandAll();

[amicitas]

Yesterday I was finally able to get my tree model working from my SQL query results. I have to say took me a while to see where I needed to be going. In the end I dump my results into a QAbstractTableModel, then build up a tree item model that gets it's data from this table. Then I use a QSortFilterProxyModel to display the tree. My code is a little ugly right now (I really need to subclass some of this), but I can use either a table model or a tree model to display the same search data.

The QSortFilterProxyModel is a bit of overkill for this, but the nice thing is that it has already subclassed and connected everything to make sure that the proxy model updates whenever the table model does.

If any one wants to see what I ended up doing let me know and I can post the code.

-- amicitas

[amicitas]

Janus wanted to take a look-see at my code, so I figured I would post it for everyone. It is not particularly polished code, but it does the job.

For this application I am doing a SQL query that returns a table of songs. Each record has a number of columns including artist, album & track. I want to turn this table into a tree. To do this I use a TableModel and a ProxyModel.

Here is a basic description of what happens:

An SQL query is performed.
The results from the query, along with the column names is added to the HystrixSearchModel. This data is added as a table.
A set of tree items is generated for the data.
reset() is called to let both the HystrixSeachModel and the HystrixProxyModel know that the data has changed.
The proxy model displays the data from the tree items.


For most of this I followed along with the "Simple Tree Model Example" in the Qt documentation. I did not document in the code the stuff that is documented there.

Since the file is too big to put in this post here is a link to it on sourceforge.
(Note that this is written in Python using PyQt4, you C++ people will have to translate.)
(as a hint though, python passes all mutable objects as pointers, so only references to the data are stored in the tree items.

hystrix_model.py (http://hystrixaudio.svn.sourceforge.net/viewvc/hystrixaudio/hystrix_model.py?view=markup)


To use this model in a view I use:


#
# Create the View (HystrixTreeView is subclassed from QTreeView
ui_search_view = HystrixTreeView(self)

# Create the data model
search_model = hystrix_model.HystrixSearchModel()

# Create the proxy model
proxy_model = hystrix_model.HystrixProxyModel()

# Connect the model
proxy_model.setSourceModel(search_model)

ui_search_view.setModel(proxy_model)




In the code the tree items are built by: HystrixSearchModel.makeTreeItems

I also have a routine in there called: HystrixSearchModel.makeIndexedTreeMap

makeIndexedTreeMap is not actually used, but it is easier to see how I am building the tree in that routine. If you are having trouble figuring out what I am doing in makeTreeItems then look at makeIndexedTreeMap first.


For my proxy model I subclass QSortFilterProxyModel. I don't actually use any of the filtering or sorting abilities, but that class already has all necessary parts of QAbstractProxyModel reimplemented and the signals, slots and events are all connected up. To use the QAbstractProxyModel directly requires a whole lot of work.

If anyone has questions let me know and I will be happy to answer them.

Good luck.

-- amicitas
(that code snippit is part of Hystrix Audio Hystrix Audio (http://hystrixaudio.sourceforge.net))

[janus]

Hi,

thx to amicitas and other posts in this thread I was able to build a tree from sql. But I am not using a model: On startup i execute a query for the parent items (children of the rootitem). My problem was, that I want to load only the data form the database that are visible at the beginning otherwise the whole thing is too slow. When the user clicks a parent (all of them have children) I execute another query to load the childitems. This is quite fast and works. My problem is, that i am not able to connect to the expanded() signal of the treeView. Right now I am hiding the decoration (setRootIsDecorated(false)) and use clicks on items within the view to expand or collapse. The expanding is done within the model ...


connect(ui.treeView, SIGNAL(clicked(QModelIndex)), model, SLOT(expand(QModelIndex)));

connect(ui.treeView, SIGNAL(expanded(QModelIndex)), model, SLOT(expand(QModelIndex))); //<-- no way. infinite loop

void TreeModel::expand(const QModelIndex &index)
{
QModelIndex idx = proxy->mapToSource(index);

QModelIndex sourceIndex = idx.sibling(idx.row(), 0);
QModelIndex proxyIndex = index.sibling(index.row(), 0);

if (!hasChildren(sourceIndex))
return;

TreeItem *item = getItem(sourceIndex);

if (item->childCount() > 0) {

if (view->isExpanded(proxyIndex)) {
view->collapse(proxyIndex);
return;
}

view->expand(proxyIndex);

}

else {

QSqlQuery q;

.. execute query

setupModelData(q, item);
view->expand(proxyIndex);

}

}


This is not a very nice solution. I'd rather prefer to use the default rootdecoration. But i can not find a way to get the expanded signal and load the data: Nothing is dispayed because the signal is emitted after the view is updated (At that point there is no data). If I call the expand slot again after the query I end up in an infinite loop ... I took a look at the Qt source code, but all parts that are required to cache the expansion (the click on the decoration [+] ) before the model is checked for children are part of QTreeViewPrivate. Sorry for the long explanation, but maybe someone has an idea :-)