How to work with MS Access (QODBC) multi-select fields?

BrokenVoodooDoll

I need to make an interface for MS Access database (QODBC driver) and there is a multi-select field:

Are there any implementations and ways for working with such fields (maybe using delegates)?

mrjj

@BrokenVoodooDoll
Hi and welcome to the forums.

Yes a delegate would allow to edit and handle such field.

How is the field store in db ?
just a string with numbers
12;14;15
or is it something special in Access ?

BrokenVoodooDoll

@mrjj
Yes, but I use QSqlRelationalTableModel, so I planned that these numbers could be replaced by names (or something else) as foreign keys.
Without executing setRelation this data is shown as a string of numbers divided by a semicolon
Снимок экрана 2020-08-12 в 17.31.06.png
I tried to find some code which could help me with this and got something like this (sorry for the blur, I can't show the data):

But I just changed QComboBox in the QSqlRelationalDelegate code to CheckBoxList class which I found on the StackOverflow.
And I think this isn't the right way and I don't know how to make it work in the way I need.

mrjj

@BrokenVoodooDoll
Hi
That listbox seems ok code-wise but in what way is it not what you need?

BrokenVoodooDoll

Well, it took a lot of time for me to solve this problem. I solved it not in the way I wanted, but it works fine.
Firstly, work with MS Access multi-valued fields is quite strange but quite simple: you should clear a field like DELETE Field.Value FROM MyTable WHERE Table.ID = MyID;. Then you can leave it clear or populate with new values performing several INSERT queries: INSERT INTO MyTable (Field.Value) VALUES (MyValue) WHERE MyTable.ID = MyID;.
And it turned out that it's not that easy to make such code work in OnManualSubmit strategy table model, so I've made a widget which allows you to modify such fields. It is called MultiValueEditor.
MultiValueEditor.h

#ifndef MULTIVALUEEDITOR_H
#define MULTIVALUEEDITOR_H

#include <QDialog>
#include <QListWidget>
#include <QHash>
#include <QSqlRelation>

class MultiValueEditor : public QDialog
{
    Q_OBJECT

    QModelIndex m_projectIndex;
    QListWidget* m_list;
    QHash<int, QListWidgetItem*> m_idToItem;

public:
    explicit MultiValueEditor(QModelIndex t_index,
                             const QSqlRelation& t_relation,
                             QWidget* parent = nullptr);
    ~MultiValueEditor();

public slots:
    void accept() override;

signals:
    void signalFinished();
};

#endif // MULTIVALUEEDITOR_H

MultiValueEditor.cpp

#include "multivalueeditor.h"

#include <QSqlQuery>
#include <QVBoxLayout>
#include <QDialogButtonBox>
#include <QPushButton>
#include <QMessageBox>
#include <QSqlError>
#include <QDebug>
#include <QSortFilterProxyModel>
#include <QSqlTableModel>

MultiValueEditor::MultiValueEditor(QModelIndex t_index,
                                 const QSqlRelation& t_relation,
                                 QWidget *parent)
    : QDialog(parent)
    , m_projectIndex(t_index)
{
    const QString queryStr =
          QString("SELECT [%1], [%2] FROM [%3] ORDER BY [%2];")
                                 .arg(t_relation.indexColumn(),
                                      t_relation.displayColumn(),
                                      t_relation.tableName());
    QSqlQuery query(queryStr, QSqlDatabase::database());
    m_list = new QListWidget;
    while (query.next()) {
        const int id = query.value(0).toInt();
        const QString value = query.value(1).toString();
        QListWidgetItem* item = new QListWidgetItem(value);
        m_list->addItem(item);
        item->setFlags(item->flags() | Qt::ItemIsUserCheckable);
        item->setCheckState(Qt::Unchecked);
        m_idToItem[id] = item;
    }
    QString value = m_projectIndex.data(Qt::EditRole).toString();
    if (!value.isEmpty()) {
        for (const QString& id : value.split(';')) {
            m_idToItem[id.toInt()]->setCheckState(Qt::Checked);
        }
    }

    QDialogButtonBox* buttonBox = new QDialogButtonBox(QDialogButtonBox::Ok | QDialogButtonBox::Cancel);
    connect(buttonBox, &QDialogButtonBox::accepted, this, &MultiValueEditor::accept);
    connect(buttonBox, &QDialogButtonBox::rejected, this, &MultiValueEditor::reject);

    QVBoxLayout* mainLayout = new QVBoxLayout;
    mainLayout->addWidget(m_list);
    mainLayout->addWidget(buttonBox);

    setLayout(mainLayout);
    setWindowTitle(QString("Edit field '%1'").arg(t_relation.tableName()));
    setAttribute(Qt::WA_DeleteOnClose);
}

MultiValueEditor::~MultiValueEditor()
{}

void MultiValueEditor::accept()
{
    // I use QSFPM, but there can be either QSqlTableModel or QSqlRelationalTableModel
    const QSortFilterProxyModel* proxyModel = qobject_cast<const QSortFilterProxyModel*>(m_projectIndex.model());
    QSqlTableModel* sqlModel = qobject_cast<QSqlTableModel*>(proxyModel->sourceModel());

    const QString primaryKeyName =
        sqlModel->headerData(0, Qt::Horizontal).toString();
    const QString tableName = sqlModel->tableName();
    const QString fieldName =
        sqlModel->headerData(m_projectIndex.column(), Qt::Horizontal).toString();

    QString queryStr =
        QString("DELETE [%1].Value FROM [%2] WHERE [%3]=:tableId;")
                           .arg(fieldName, tableName, primaryKeyName);
    QSqlQuery query;
    query.prepare(queryStr);
    const int tableId = m_projectIndex.siblingAtColumn(0).data().toInt();
    query.bindValue(":tableId", tableId);
    if (!query.exec()) {
        QMessageBox msgBox(QMessageBox::Critical,
                           ERROR_TITLE,
                           QString("Error executing SQL-query\n%1")
                               .arg(query.lastError().text()),
                           QMessageBox::Ok, this);
        msgBox.exec();
    }
    for (auto it = m_idToItem.constBegin(); it != m_idToItem.constEnd(); ++it) {
        if (it.value()->checkState() == Qt::Checked) {
            queryStr = QString("INSERT INTO [%1] ([%2].Value) "
                               "VALUES (:fieldId) WHERE [%3]=:tableId;")
                           .arg(tableName, fieldName, primaryKeyName);
            query.prepare(queryStr);
            query.bindValue(":fieldId", it.key());
            query.bindValue(":tableId", tableId);
            query.exec();
        }
    }

    emit signalFinished();
    close();
}

And in a place where you manage your table view (in my case in the MainWindow) you should connect TableView's double click with the execution of the MultiValueEditor. In my case it looks like this:
MainWindow.cpp

void MainWindow::setupDatabaseModels() {
    //...
    connect(m_projectTableView, &CustomTableView::doubleClicked,
        this, &MainWindow::slotEditProject);
    //...
}

void MainWindow::slotEditProject(const QModelIndex &index)
{
    const int col = index.column();
    if (col == 5) {
        QSqlRelation relation = QSqlRelation("Employees", "ID", "Name");
        MultiValueEditor* multivalueEditor =
            new MultiValueEditor(index, relation, this);
        connect(multivalueEditor, &MultiValueEditor::signalFinished, [=](){
            m_projectTableModel->select();
        });
        multivalueEditor->open();
    }
    //...
}

Hope this will help somebody.