Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

QSqlRelationalDelegate display all possible values from foreign key



  • Hello Qt community !

    I have been slowly working my way in qt for a while now and stumbled on a selection behavior between a QSqlRelationalTableModel and a QComboBox.

    So my problem is the following:
    I have one table which stores some items. The SQL table is made as follow (with SQLite):

    Articles(
    Name TEXT,
    price1 REAL,
    price2 REAL,
    price3 REAL,
    price4 REAL,
    function INTEGER REFERENCES Functions(Id));
    

    I have another database where I store the names of the functions to which Articles refers.

    Functions(
    name TEXT,
    Id INTEGER);
    

    The way I want to display the Articles is the following (all fields must be editable except Name):
    A QListView on the left and a form on the right to display and update the prices. QDoubleSpinBox for the prices and a QComboBox for Articles.function as text values. Everything is successfully linked with a QDataWidgetMapper and both tables are also successfully linked with a QSqlRelation

    The problem I face is that the QComboBox only lists the Function names that are in use in the model, not all the existing values.

    Here is what I tried so far:

     //Init all private members
        price1      = new QDoubleSpinBox(this);
        price2      = new QDoubleSpinBox(this);
        price3      = new QDoubleSpinBox(this);
        price4      = new QDoubleSpinBox(this);
        function    = new QComboBox(this);
        mapper      = new QDataWidgetMapper(this);
        sqlModel    = new QSqlRelationalTableModel(this);
        QSqlTableModel *fctModel    = new QSqlTableModel(this);
        QListView *articlesView     = new QListView(this);
    
        //Imediately links the views and the models
        sqlModel->setTable("Articles");
    
        //A few indexes
        int nameIndex           = sqlModel->record().indexOf("Name");
        int priceIndex          = sqlModel->record().indexOf("price1");
        int jobShareIndex       = sqlModel->record().indexOf("price2");
        int bPriceIndex         = sqlModel->record().indexOf("price3");
        int redPriceIndex       = sqlModel->record().indexOf("price4");
        int functionIndex       = sqlModel->record().indexOf("function");
    
        int functionModelIndex  = fctModel->record().indexOf("name");
    
        sqlModel->setRelation(functionIndex, QSqlRelation("Functions", "Id", "name"));
        sqlModel->setEditStrategy(QSqlTableModel::OnFieldChange);
        sqlModel->select();
        fctModel->setTable("Functions");
        fctModel->select();
    
        //Views
        articlesView->setModel(sqlModel);
        articlesView->setModelColumn(nameIndex); //Sets the column to the name
        articlesView->setEditTriggers(QAbstractItemView::NoEditTriggers);
    
        //Mapper
        mapper->setModel(sqlModel);
        mapper->addMapping(price1, priceIndex);
        mapper->addMapping(price2, bPriceIndex);
        mapper->addMapping(price3, jobShareIndex);
        mapper->addMapping(price4, redPriceIndex);
        mapper->addMapping(function, functionIndex);
        mapper->setSubmitPolicy(QDataWidgetMapper::ManualSubmit);
    
        function->setModel(sqlModel);
        function->setModelColumn(functionIndex);
        function->setItemDelegate(new QSqlRelationalDelegate(function));
    

    I also tried using a QCompleter but the result stays the same.

        QCompleter *completer = new QCompleter(this);
        completer->setCompletionMode(QCompleter::UnfilteredPopupCompletion);
        completer->setModel(fctModel);
        completer->setCompletionColumn(functionMode);
        function->setCompleter(completer);
    

    Do somebody knows what to do or already encountered this situation ?

    Thanks in advance for your response !

    I wish you all a happy new year !



  • Hi again !
    It seems that by looking still a bit more I found a suitable answer.
    The key was to set the QSqlRelationalDelegate for the mapper and not the QComboBox but still setting the model of the QComboBox to the table containing all the functions.

    Here is my final code:

        //Init all private members
     //Init all private members
        price1      = new QDoubleSpinBox(this);
        price2      = new QDoubleSpinBox(this);
        price3      = new QDoubleSpinBox(this);
        price4      = new QDoubleSpinBox(this);
        function    = new QComboBox(this);
        mapper      = new QDataWidgetMapper(this);
        sqlModel    = new QSqlRelationalTableModel(this);
        QSqlTableModel *fctModel    = new QSqlTableModel(this);
        QListView *articlesView     = new QListView(this);
    
        //Imediately links the views and the models
        sqlModel->setTable("Articles");
    
        //A few indexes
        int nameIndex           = sqlModel->record().indexOf("Name");
        int priceIndex          = sqlModel->record().indexOf("sellPrice");
        int jobShareIndex       = sqlModel->record().indexOf("jShare");
        int bPriceIndex         = sqlModel->record().indexOf("bPrice");
        int redPriceIndex       = sqlModel->record().indexOf("reducedPrice");
        int functionIndex       = sqlModel->record().indexOf("function");
        int functionNameIndex;
        {
            auto tmp = new QSqlTableModel();
            tmp->setTable("Functions");
            functionNameIndex = tmp->record().indexOf("name");
            delete tmp;
        }
    
        //Configures the sql model
        sqlModel->setRelation(functionIndex, QSqlRelation("Functions", "Id", "name"));
        sqlModel->setEditStrategy(QSqlTableModel::OnFieldChange);
        sqlModel->select();
    
        //Views
        articlesView->setModel(sqlModel);
        articlesView->setModelColumn(nameIndex); //Sets the column to the name
        articlesView->setEditTriggers(QAbstractItemView::NoEditTriggers);
        function->setModel(sqlModel->relationModel(functionIndex));
        function->setModelColumn(functionNameIndex );
    
        //Mapper
        mapper->setModel(sqlModel);
        mapper->addMapping(price1, priceIndex);
        mapper->addMapping(price2, bPriceIndex);
        mapper->addMapping(price3, jobShareIndex);
        mapper->addMapping(price4, redPriceIndex);
        mapper->addMapping(function, functionIndex);
        mapper->setItemDelegate(new QSqlRelationalDelegate(mapper));
        mapper->setSubmitPolicy(QDataWidgetMapper::ManualSubmit);
    

    I hope that it will be able to help anybody who might encounter that same problem !
    Happy new year everybody, take care ! :)


Log in to reply