Filter relation column options - QSqlRelationalDelegate? constrain or restrict option

[talkfig]

qt4.8

I have a relational table model, with editable relations that work.

The table is filtered, and the default relational delegate
collects all possible values from the database as combo options.

I would like to restrict the available options to either:

a) the option set defined by the table's filter - i.e. what's available in the current table
OR
b) manually-generated list (of relations in the current table )

either one will do.

I attempted a specialized createEditor function to
create an editor with a fixed/generated set of options.

QWidget *TeamSelectDelegate::createEditor(QWidget *parent,
    const QStyleOptionViewItem & option,
    const QModelIndex & index) const
{
    Q_UNUSED(option);
const QSqlRelationalTableModel *sqlModel = qobject_cast<const QSqlRelationalTableModel *>(index.model());
    QSqlTableModel *childModel = sqlModel ? sqlModel->relationModel(index.column()) : 0;
    if (!childModel)
        return QItemDelegate::createEditor(parent, option, index);
 
    QComboBox *combo = new QComboBox(parent);
    combo->setDuplicatesEnabled(false);
    combo->setModel(childModel);
    combo->setModelColumn(childModel->fieldIndex(sqlModel->relation(index.column()).displayColumn()));
 
    combo->clear();                     // get rid of stuff from model,
    combo->addItems(*_relevant_teams);   // and put in options from a subset ( generated list )
 
    combo->installEventFilter(const_cast<TeamSelectDelegate *>(this));
    return combo;

QWidget *TeamSelectDelegate::createEditor(QWidget *parent,
    const QStyleOptionViewItem & option,
    const QModelIndex & index) const
{
    Q_UNUSED(option);
const QSqlRelationalTableModel *sqlModel = qobject_cast<const QSqlRelationalTableModel *>(index.model());
    QSqlTableModel *childModel = sqlModel ? sqlModel->relationModel(index.column()) : 0;
    if (!childModel)
        return QItemDelegate::createEditor(parent, option, index);

    QComboBox *combo = new QComboBox(parent);
    combo->setDuplicatesEnabled(false);
    combo->setModel(childModel);
    combo->setModelColumn(childModel->fieldIndex(sqlModel->relation(index.column()).displayColumn()));

    combo->clear();                     // get rid of stuff from model,
    combo->addItems(*_relevant_teams);   // and put in options from a subset ( generated list )

    combo->installEventFilter(const_cast<TeamSelectDelegate *>(this));
    return combo;

To copy to clipboard, switch view to plain text mode 

"_relevant_teams" is a previously generated list of options.
This did not work... it seemed to delete all items in the DB!

Thanks for sharing your ideas.


Cheers!
mike

[talkfig]

Looks like no easy way to accomplish this -
due to a lack of forethought perhaps.

There is a function to get the model of the relations (to setup a filter) but,
the "get" function itself triggers the SQL query to populate the model!

So, my approach is to modify the sql/model/qsqlrelational bits to allow
passing a filter string when setting up the relation like:

Game::m_tmodel->setRelation(3, QSqlRelation("team",   "id", "name", "division_id=3"));

Game::m_tmodel->setRelation(3, QSqlRelation("team",   "id", "name", "division_id=3"));

To copy to clipboard, switch view to plain text mode 

Down the "populate()" function chain, a function actually looks for a WHERE condition but no way to setup conditions before the query happens.

now, compiling the Qt sources...

[talkfig]

Tweaking Qt sql-model code got desired results -

diff -r ../../../QtSDK-orig/QtSources/4.8.1/src/sql/models/qsqlrelationaltablemodel.cpp ./src/sql/models/qsqlrelationaltablemodel.cpp
241a242
>     setFilter(relation->rel.where());   // This seems to work...
diff -r ../../../QtSDK-orig/QtSources/4.8.1/src/sql/models/qsqlrelationaltablemodel.h ./src/sql/models/qsqlrelationaltablemodel.h
58,59c58,59
<                const QString &displayCol)
<         : tName(aTableName), iColumn(indexCol), dColumn(displayCol) {}
---
>                const QString &displayCol, const QString &where = QString())
>         : tName(aTableName), iColumn(indexCol), dColumn(displayCol), _where(where) {}
65a66,67
>     inline QString where() const
>     { return _where; }
69c71
<     QString tName, iColumn, dColumn;
---
>     QString tName, iColumn, dColumn, _where;

diff -r ../../../QtSDK-orig/QtSources/4.8.1/src/sql/models/qsqlrelationaltablemodel.cpp ./src/sql/models/qsqlrelationaltablemodel.cpp
241a242
>     setFilter(relation->rel.where());   // This seems to work...
diff -r ../../../QtSDK-orig/QtSources/4.8.1/src/sql/models/qsqlrelationaltablemodel.h ./src/sql/models/qsqlrelationaltablemodel.h
58,59c58,59
<                const QString &displayCol)
<         : tName(aTableName), iColumn(indexCol), dColumn(displayCol) {}
---
>                const QString &displayCol, const QString &where = QString())
>         : tName(aTableName), iColumn(indexCol), dColumn(displayCol), _where(where) {}
65a66,67
>     inline QString where() const
>     { return _where; }
69c71
<     QString tName, iColumn, dColumn;
---
>     QString tName, iColumn, dColumn, _where;

To copy to clipboard, switch view to plain text mode 

This allows specifying your WHERE clause to limit the association options for model relations - no special delegate is required.

Game::m_tmodel->setRelation(4, QSqlRelation("team", "id", "name", "team.division_id IN (1,10,11,12)" ));

Game::m_tmodel->setRelation(4, QSqlRelation("team", "id", "name", "team.division_id IN (1,10,11,12)" ));

To copy to clipboard, switch view to plain text mode