How to show distinct rows in QSqlTableModel filter?
-
Dear all,
In filtering QSqlTableModel, we got results.
I would like to display only rows which has different in specific column.E.g. Results return as
@Name | units
cake | 3
pie | 2
ice cream | 4
cake | 1
@What I need is to show only one row of cake because there is already cake in rows.
It can solve in "distinct" keyword in MS SQL, but I don't know how to handle in QSqlTableModel Filter.Thanks
-
I think this is best handled at the SQL level, but it depends a bit on what you want to happen. Do you expect the units to be added or something like that for the different cake entries? In that case, I suggest you take a look at the GROUP BY statement and use the right aggregate function in SQL. Instead of using a QSqlTableModel, you would need to use a QSqlQueryModel. You can not* do this by just setting the WHERE part of an SQL statement, so the filter method of QSqlTableModel is not going to help you.
Doing this using a proxy model or something like that will be tricky.
[*] Well, perhaps you could, but that would result in really hideous SQL, and I would not recommend trying it.
-
The "official" solution from this book: "Advanced Qt Programming"
@
class UniqueProxyModel : public QSortFilterProxyModel
{
Q_OBJECT
public:
explicit UniqueProxyModel(int column, QObject *parent=0) : QSortFilterProxyModel(parent), Column(column) {}
void setSourceModel(QAbstractItemModel *sourceModel);
protected:
bool filterAcceptsRow(int sourceRow,
const QModelIndex &sourceParent) const;
private slots:
void clearCache() { cache.clear(); }
private:
const int Column;
mutable QSet<QString> cache; // important: "set" allows only ONE!
};void UniqueProxyModel::setSourceModel(QAbstractItemModel *sourceModel)
{
connect(sourceModel, SIGNAL(modelReset()),
this, SLOT(clearCache()));
QSortFilterProxyModel::setSourceModel(sourceModel);
}bool UniqueProxyModel::filterAcceptsRow(int sourceRow, const QModelIndex &sourceParent) const
{
QModelIndex index = sourceModel()->index(sourceRow, Column,
sourceParent);
const QString &text = sourceModel()->data(index).toString();
if (cache.contains(text))
return false; // the filtering goes here!
cache << text;
return true;
}
@Edit: please use @ tags around code sections; Andre
-
Please note that the proxy solution is fine if you don't have a lot of duplicate rows. In such case in fact, you will query all the database rows and drop them later, which can be bandwidth and time consuming. The best solution is to drop to SQL, after all if you are going to do such a filtering chances are you will need complex queries later on.