c++ QSqlRelationalTable如何在带外键的列中显示来自其他表的值？

brtdzjyr  于 2023-02-01  发布在  其他
关注(0)|答案(4)|浏览(176)

我有一个SQLite数据库，其中包含三个表：

graph(ID int primary key, name varchar(64));
vertex(ID int primary key, graphID int references graph(ID), name varchar(64), x int default 0, y int default 0);
edge(ID int primary key, graphID int references graph(ID), sourceID int references vertex(ID), targetID int references vertex(ID), weight real default 1);

在我的桌面应用程序中，我使用自定义类作为模型/视图

MyTableView : public QTableView
VertexTableModel : public QSqlTableModel
EdgeTableModel : public QSqlRelationalTableModel

我是这样设计的：

GraphyEditor::GraphyEditor(QWidget *parent) :
        QMainWindow(parent),
        ui(new Ui::GraphyEditor),
        vertexModel(new VertexTableModel(parent)),
        edgeModel(new EdgeTableModel(parent)) {
    ui->setupUi(this);

    vertexModel->setTable("vertex");
    ui->vertices->setModel(vertexModel); //  ui->vertices is *MyTableView

    edgeModel->setTable("edge");
    //TODO find fix to the issue
//    edgeModel->setRelation(2, QSqlRelation("vertex", "ID", "name"));
//    edgeModel->setRelation(3, QSqlRelation("vertex", "ID", "name"));

    ui->edges->setModel(edgeModel); //  ui->egdes is *MyTableView
}

这段代码可以正常工作并正确显示数据，但是我想将edgeModel中的第2列和第3列（sourceID和targetID）从vertex.ID替换为vertex.name
我做了一些搜索，找到了setRelation方法（与我在代码中注解掉的方法相同），但是当我使用它时，edgeModel表没有显示边。
是因为我的表模式还是我的代码有问题？
我如何实现这一目标？
编辑：
下面是我正在使用的类的实现：
MyTableModel.h/cpp

#include <QtSql/QSqlTableModel>

class MyTableModel : public QSqlTableModel {
Q_OBJECT

public:
    explicit MyTableModel(QObject *parent = nullptr);

    void refresh();

    [[nodiscard]] Qt::ItemFlags flags(const QModelIndex &index) const override = 0;

    bool setData(const QModelIndex &index, const QVariant &value, int role) override = 0;

signals:

    void databaseUpdated();

};

#endif

#include "MyTableModel.h"
#include <QDebug>
#include <utility>
#include <database/DBManager.h>

MyTableModel::MyTableModel(QObject *parent) :
        QSqlTableModel(parent) {}

void MyTableModel::refresh() { select(); }

VertexTableModel.h/cpp

#include <model/MyTableModel.h>

class VertexTableModel : public MyTableModel {
Q_OBJECT

public:
    explicit VertexTableModel(QObject *parent = nullptr);

    [[nodiscard]] Qt::ItemFlags flags(const QModelIndex &index) const override;

    bool setData(const QModelIndex &index, const QVariant &value, int role) override;
};

#endif

bool VertexTableModel::setData(const QModelIndex &index, const QVariant &value, int role) {
    // checks if value is valid and updates database
}

Qt::ItemFlags VertexTableModel::flags(const QModelIndex &index) const {
    auto flags = Qt::ItemIsSelectable | Qt::ItemIsEnabled;
    if (index.column() == 2) flags |= Qt::ItemIsEditable;
    return flags;
}

VertexTableModel::VertexTableModel(QObject *parent) : MyTableModel(parent) {}

EdgeTableModel.h/cpp

class EdgeTableModel : public QSqlRelationalTableModel {
Q_OBJECT

public:
    explicit EdgeTableModel(QObject *parent = nullptr);

    void refresh();

    [[nodiscard]] Qt::ItemFlags flags(const QModelIndex &index) const override;

    bool setData(const QModelIndex &index, const QVariant &value, int role) override;

signals:

    void databaseUpdated();
};

#endif

EdgeTableModel::EdgeTableModel(QObject *parent) : QSqlRelationalTableModel(parent) {
    refresh();
}

void EdgeTableModel::refresh() { select(); }

bool EdgeTableModel::setData(const QModelIndex &index, const QVariant &value, int role) {
    // checks if value is valid and updates the database
}

Qt::ItemFlags EdgeTableModel::flags(const QModelIndex &index) const {
    auto flags = Qt::ItemIsSelectable | Qt::ItemIsEnabled;
    if (index.column() == 4) flags |= Qt::ItemIsEditable;
    return flags;
}

GraphyEditor.h/cpp

#ifndef GRAPHY_EDITOR_H
#define GRAPHY_EDITOR_H

#include <QMainWindow>
#include <model/vertex/VertexTableModel.h>
#include <model/edge/EdgeTableModel.h>
#include <QtGui/QRegExpValidator>
#include <QtSql/QSqlRelationalDelegate>

QT_BEGIN_NAMESPACE
namespace Ui { class GraphyEditor; }
QT_END_NAMESPACE

class GraphyEditor : public QMainWindow {
Q_OBJECT
    Ui::GraphyEditor *ui;
    QSqlTableModel *vertexModel;
    QSqlRelationalTableModel *edgeModel;
    QSqlRelationalDelegate *delegate;

    QString graphID = "";

public:
    explicit GraphyEditor(QWidget *parent = nullptr);

    void setGraphID(const QString &newGraphID);

    ~GraphyEditor() override;
};

#endif

#include <QtWidgets/QWidget>
#include "GraphyEditor.h"
#include <model/vertex/VertexTableModel.h>
#include <model/edge/EdgeTableModel.h>
#include <QtSql/QSqlRelationalDelegate>

GraphyEditor::GraphyEditor(QWidget *parent) :
        QMainWindow(parent),
        ui(new Ui::GraphyEditor),
//        vertexModel(new VertexTableModel(parent)),
        vertexModel(new QSqlTableModel(parent)),
//        edgeModel(new EdgeTableModel(parent)) {
        edgeModel(new QSqlRelationalTableModel(parent)) {
    ui->setupUi(this);

    vertexModel->setTable("vertex");
    vertexModel->setHeaderData(1, Qt::Horizontal, "Vertex ID");
    vertexModel->setHeaderData(2, Qt::Horizontal, "Vertex Name");

    ui->vertices->setModel(vertexModel);
//    ui->vertices->hideColumn(0);
    ui->vertices->hideColumn(1);
    ui->vertices->hideColumn(3);
    ui->vertices->hideColumn(4);

    edgeModel->setTable("edge");
    //TODO find fix to the issue
//    edgeModel->setRelation(2, QSqlRelation("vertex", "ID", "name"));
//    edgeModel->setRelation(3, QSqlRelation("vertex", "ID", "name as targetName"));

    edgeModel->setHeaderData(2, Qt::Horizontal, "Source Vertex", Qt::DisplayRole);
    edgeModel->setHeaderData(3, Qt::Horizontal, "Target Vertex", Qt::DisplayRole);
    edgeModel->setHeaderData(4, Qt::Horizontal, "Weight");

    delegate = new QSqlRelationalDelegate(this);
    ui->edges->setModel(edgeModel);
    ui->edges->setItemDelegate(delegate);
//    ui->edges->setItemDelegateForColumn(2, delegate);
//    ui->edges->setItemDelegateForColumn(3, delegate);
    ui->edges->hideColumn(0);
    ui->edges->hideColumn(1);

    ui->canvas->setVertices(vertexModel);
    ui->canvas->setEdges(edgeModel);
}

GraphyEditor::~GraphyEditor() {
    delete ui;
    delete vertexModel;
    delete edgeModel;
    delete delegate;
}

void GraphyEditor::setGraphID(const QString &newGraphID) {
    GraphyEditor::graphID = newGraphID;

    vertexModel->setFilter("graphID = " + newGraphID);
    edgeModel->setFilter("graphID = " + newGraphID);

    ui->canvas->setGraphID(newGraphID);
    ui->canvas->refresh();
}

x1米11米1x

#ifndef GRAPHY_CANVAS_H
#define GRAPHY_CANVAS_H

#include <QWidget>
#include <model/vertex/VertexTableModel.h>
#include <model/edge/EdgeTableModel.h>

class GraphyCanvas : public QWidget {
Q_OBJECT

    QSqlTableModel *vertexModel = nullptr;
    QSqlRelationalTableModel *edgeModel = nullptr;

    QString graphID = "";

public:

    void setGraphID(const QString &newGraphID);

    explicit GraphyCanvas(QWidget *parent = nullptr);

    void setVertices(QSqlTableModel *vertexTableModel);

    void setEdges(QSqlRelationalTableModel *edgeTableModel);

public slots:

    void refresh();
};

#endif

#include <QPainter>
#include <QPen>
#include <QDebug>
#include <QtWidgets/QtWidgets>
#include <cmath>
#include "GraphyCanvas.h"

GraphyCanvas::GraphyCanvas(QWidget *parent) : QWidget(parent) {
    QPalette newPalette = palette();
    newPalette.setColor(QPalette::Window, Qt::white);
    setPalette(newPalette);
}

void GraphyCanvas::refresh() {
    vertexModel->select();
    edgeModel->select();

    for (auto child : children()) child->deleteLater();

    int edgesCount = edgeModel->rowCount();

    for (int e = 0; e < edgesCount; ++e) {
        //paints edge objects
    }

    int verticesCount = vertexModel->rowCount();

    for (int v = 0; v < verticesCount; ++v) {
        //paints vertex objects
    }
}

void GraphyCanvas::setVertices(QSqlTableModel *vertexTableModel) {
    GraphyCanvas::vertexModel = vertexTableModel;
    connect(vertexModel, SIGNAL(databaseUpdated()), this, SLOT(refresh()));
}

void GraphyCanvas::setEdges(QSqlRelationalTableModel *edgeTableModel) {
    GraphyCanvas::edgeModel = edgeTableModel;
    connect(edgeModel, SIGNAL(databaseUpdated()), this, SLOT(refresh()));
}

void GraphyCanvas::setGraphID(const QString &newGraphID) { GraphyCanvas::graphID = newGraphID; }

main.cpp

#include "setup/GraphySetup.h"

#include <QApplication>
#include <database/DBManager.h>

int main(int argc, char *argv[]) {
    QApplication application(argc, argv);

    DBManager::initialize();

    GraphyEditor editor;
    editor.setGraphID("1");
    editor.showMaximized();

    return QApplication::exec();
}

DBManager是一个helper类，负责初始化和访问数据库

c++

4条答案

按热度按时间
a7qyws3x

a7qyws3x1#

在 * 图形编辑器. cpp * 的函数GraphyEditor::setGraphID中，此行

edgeModel->setFilter("graphID = " + newGraphID);

应该是

edgeModel->setFilter("edge.graphID = " + newGraphID);

基础查询是一个连接，其中字段名 * graphID * 属于多个表，因此表名必须与字段名一起指定。

赞(0）分享  回复(0）举报  2023-02-01
c90pui9n

c90pui9n2#

您的问题可能隐藏在项目中的某个其他位置，而不是使用QSqlRelationalTableModel的代码中。
在您的（注解）代码中只有一个最小的问题：两个被替换的列将具有相同的名称：“name”，但您可以在定义QSqlRelation时重命名这两列。
这里有一个m.r.e.来说明如何处理您的两个表和一个QSqlRelationalTableModel，以防万一有人问stackoverflow类似的问题。
SQLite数据库转储：

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE `vertex` (
        `ID`    INTEGER,
        `name`  TEXT,
        PRIMARY KEY(`ID`)
);
INSERT INTO vertex VALUES(1,'one');
INSERT INTO vertex VALUES(2,'two');
INSERT INTO vertex VALUES(3,'three');
INSERT INTO vertex VALUES(4,'four');
INSERT INTO vertex VALUES(5,'five');
INSERT INTO vertex VALUES(6,'six');
INSERT INTO vertex VALUES(7,'seven');
INSERT INTO vertex VALUES(8,'eight');
INSERT INTO vertex VALUES(9,'nine');
CREATE TABLE IF NOT EXISTS "edge" (
        "ID"    INTEGER,
        "sourceID"      INTEGER,
        "targetID"      INTEGER,
        FOREIGN KEY("targetID") REFERENCES "vertex"("ID"),
        PRIMARY KEY("ID"),
        FOREIGN KEY("sourceID") REFERENCES "vertex"("ID")
);
INSERT INTO edge VALUES(1,1,4);
INSERT INTO edge VALUES(2,2,5);
INSERT INTO edge VALUES(3,3,6);
INSERT INTO edge VALUES(4,4,7);
INSERT INTO edge VALUES(5,5,8);
INSERT INTO edge VALUES(6,6,9);
COMMIT;

test.pro

QT = core sql
CONFIG += c++11 console
SOURCES += main.cpp

main.cpp

#include <QCoreApplication>
#include <QTextStream>
#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlRelationalTableModel>
#include <QSqlRecord>
#include <QSqlField>

int main(int argc, char *argv[])
{
    QCoreApplication a(argc, argv);
    QTextStream cout(stdout, QIODevice::WriteOnly);
    QTextStream cerr(stderr, QIODevice::WriteOnly);
    auto db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("test.db");
    if (!db.open()) {
        cerr << db.lastError().text() << endl;
        return 1;
    }

    QSqlRelationalTableModel model;
    model.setTable("edge");
    model.setRelation(1, QSqlRelation("vertex", "ID", "name as sourceName"));
    model.setRelation(2, QSqlRelation("vertex", "ID", "name as targetName"));
    model.select();
    auto rec = model.record();
    // headers output
    cout << qSetFieldWidth(15);
    for(int i=0; i<rec.count(); ++i) {
         cout << rec.field(i).name();
    }
    cout << endl;
    // rows output
    for(int i=0; i<model.rowCount(); ++i) {
        rec = model.record(i);
        cout << rec.value("ID").toInt() << rec.value("sourceName").toString() << rec.value("targetName").toString() << endl;
    }
}

这是程序的输出结果

ID     sourceName     targetName              
          1            one           four              
          2            two           five              
          3          three            six              
          4           four          seven              
          5           five          eight              
          6            six           nine
赞(0）分享  回复(0）举报  2023-02-01
wfsdck30

wfsdck303#

可以修改selectStatement：

QString QSqlRelationalTableModel::selectStatement() const
......
            //!!! my
            fList.append(QLatin1String(", "));
            fList.append(relTableAlias);
            fList.append(QLatin1String("."));
            fList.append(relation.indexColumn());
            fList.append(QLatin1String(" as "));
            fList.append(relation.tableName());
            fList.append(QLatin1String("_"));
            fList.append(relation.indexColumn());
赞(0）分享  回复(0）举报  2023-02-01
shyt4zoc

shyt4zoc4#

也许你可以看看我们的开放Qt项目：[编辑][1]
该项目包含了QSqlTableModel + QTableView之上的一个 Package 器，实现了具有基本表功能的PblTableDlg类，并使用了QSqlRelationalTableModel = PblSqlRelationalTableModel的一个新变体。
PblTableView（由QTableView继承）和PblTableDlg包含一个带有所有控件的数据库表视图。[1]：https://github.com/PavelDorofeev/Fork-Sql-Qt-4.8.1--SQLite-3--relations--calc-fields

赞(0）分享  回复(0）举报  2023-02-01
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com