C++ QSqlRelationalTableModel::setRelation方法代码示例

void UserForm:: createTableModels()
{
    QSqlRelationalTableModel* model = new QSqlRelationalTableModel();
    model->setTable("good");
    model->setRelation(Producer, QSqlRelation("company", "companyid", "name"));
    model->setRelation(Catalog, QSqlRelation("catalog", "id", "name"));

    model->setHeaderData(Producer, Qt::Horizontal, "Producer");
    model->setHeaderData(Name, Qt::Horizontal, "Name");
    model->setHeaderData(Price, Qt::Horizontal, "Price, $");
    model->setHeaderData(Description, Qt::Horizontal, "Description");
    model->setHeaderData(Catalog, Qt::Horizontal, "Catalog");
    model->setFilter("catalog_id IS NOT NULL");

//    model->removeColumn(5);
    model->select();
    productsModel = model;

    model = new QSqlRelationalTableModel();
    model->setTable("`order`");
    model->setRelation(goodId, QSqlRelation("good", "goodid", "name"));
    model->setFilter("user_login='" + TofPApplication::getUserLogin() + "'");
    model->select();   

    model->setEditStrategy(QSqlTableModel::OnRowChange);
    model->removeColumn(3);
    model->removeColumn(2);
    model->setHeaderData(orderId, Qt::Horizontal, "Order #");
    model->setHeaderData(goodId, Qt::Horizontal, "Product");
    model->setHeaderData(date, Qt::Horizontal, "Date");
    model->setHeaderData(address, Qt::Horizontal, "Address to deliver");
    ordersModel = model;
}

void Carga::loadCargas(QDate start, QDate end){
    QSqlRelationalTableModel *model = new QSqlRelationalTableModel(0, QSqlDatabase::database());

    model->setTable("cargas");
    model->setRelation(2, QSqlRelation("cereales", "id", "cereal"));
    model->setRelation(15, QSqlRelation("transportista", "cuit", "nombre"));
    model->setRelation(8, QSqlRelation("cliente", "cuit", "nombre"));
    model->setRelation(5, QSqlRelation("localizacion", "id", "lugar"));
    model->setRelation(7, QSqlRelation("tipo_operacion", "id", "name"));
    model->setRelation(11, QSqlRelation("boolean", "id", "name"));
    model->setRelation(12, QSqlRelation("boolean", "id", "name"));
    model->setRelation(13, QSqlRelation("boolean", "id", "name"));
    model->setRelation(14, QSqlRelation("boolean", "id", "name"));
    model->setRelation(19, QSqlRelation("boolean", "id", "name"));

    QString filter = "fecha BETWEEN '" + start.toString("yyyy-MM-dd") + "' AND '" + end.toString("yyyy-MM-dd") + "'";

    model->setFilter(filter);

    model->setHeaderData(1, Qt::Horizontal, QObject::tr("Fecha"));
    model->setHeaderData(2, Qt::Horizontal, QObject::tr("Grano"));
    model->setHeaderData(3, Qt::Horizontal, QObject::tr("Cosecha"));
    model->setHeaderData(4, Qt::Horizontal, QObject::tr("Kilos"));
    model->setHeaderData(5, Qt::Horizontal, QObject::tr("Origen"));
    model->setHeaderData(6, Qt::Horizontal, QObject::tr("N° C porte"));
    model->setHeaderData(7, Qt::Horizontal, QObject::tr("Tipo"));
    model->setHeaderData(8, Qt::Horizontal, QObject::tr("Cliente"));
    model->setHeaderData(9, Qt::Horizontal, QObject::tr("Tarifa a cobrar"));
    model->setHeaderData(10, Qt::Horizontal, QObject::tr("Tarifa a pagar"));
    model->setHeaderData(11, Qt::Horizontal, QObject::tr("Se cobra"));
    model->setHeaderData(12, Qt::Horizontal, QObject::tr("Se paga"));
    model->setHeaderData(13, Qt::Horizontal, QObject::tr("Pagado"));
    model->setHeaderData(14, Qt::Horizontal, QObject::tr("Cobrado"));
    model->setHeaderData(15, Qt::Horizontal, QObject::tr("Transportista"));
    model->setHeaderData(16, Qt::Horizontal, QObject::tr("Observaciones"));
    model->setHeaderData(17, Qt::Horizontal, QObject::tr("Origen"));
    model->setHeaderData(18, Qt::Horizontal, QObject::tr("Destino"));
    model->setHeaderData(19, Qt::Horizontal, QObject::tr("En depósito"));


    model->select();

    ui->cargas->setModel(model);
    ui->cargas->setItemDelegate(new QSqlRelationalDelegate(ui->cargas));
    ui->cargas->setSortingEnabled(true);

    ui->cargas->setVisible(false);
    ui->cargas->resizeColumnsToContents();
    ui->cargas->setVisible(true);

    // OCULTAR COLUMNAS CARGAS
    ui->cargas->setColumnHidden(0,true);

    ui->pushButton_3->setDisabled(true);

}

QSqlRelationalTableModel* EkonTables::createRelationalTableModel(QWidget *parentWidget, QString tableName, QStringList columnNames, QList<QPair<int, QSqlRelation> > relations)
{
    QSqlRelationalTableModel *tableModel = new QSqlRelationalTableModel(parentWidget, DbService::getInstance()->getCurrentDatabase());
    tableModel->setTable(tableName);

    for (auto relation : relations)
        tableModel->setRelation(relation.first, relation.second);

    configureTableModel(tableModel, columnNames);

    return tableModel;
}

int main(int argc, char *argv[])
{
    QApplication a(argc, argv);
    MainWindow w;

    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    if(!createConnection(db))
        return 1;
/*
    QSqlQueryModel * mode = new QSqlQueryModel;
    mode->setQuery("select prod_id, vend_name, prod_name, prod_price from products, vendors where products.vend_id = vendors.vend_id", db);
    mode->setHeaderData(0, Qt::Horizontal, "prod_id");
    mode->setHeaderData(1, Qt::Horizontal, "vend_name");
    mode->setHeaderData(2, Qt::Horizontal, "prod_name");
    mode->setHeaderData(3, Qt::Horizontal, "prod_price");
*/

    QSqlRelationalTableModel * mode =
            new QSqlRelationalTableModel;
    mode->setTable("products");
    QSqlRelation relation("vendors",
                          "vend_id",
                          "vend_name");

    mode->setRelation(1, relation);
    mode->select();
    mode->setHeaderData(0, Qt::Horizontal, "prod_id");
    mode->setHeaderData(1, Qt::Horizontal, "vend_name");
    mode->setHeaderData(2, Qt::Horizontal, "prod_name");
    mode->setHeaderData(3, Qt::Horizontal, "prod_price");

    QTableView * tableview = new QTableView(&w);
    tableview->verticalHeader()->hide();
    tableview->setModel(mode);

    w.setCentralWidget(tableview);
    w.resize(600, 400);
    w.show();
    
    return a.exec();
}

void CarsEdit::FillTable()
{
    QString str_oper = "SELECT hm_cars.id, hm_cars.name, hm_cars.speed, hm_fuel.name, hm_fuel.id FROM hm_cars INNER JOIN hm_fuel ON hm_cars.fuel_id = hm_fuel.id;";
    QSqlQueryModel * model_Oper = new QSqlQueryModel(0);
    model_Oper->setQuery(str_oper);
    model_Oper->setHeaderData(1, Qt::Horizontal, QObject::trUtf8("Название автомобиля"));
    model_Oper->setHeaderData(2, Qt::Horizontal, QObject::trUtf8("Максимальная скорость"));
    model_Oper->setHeaderData(3, Qt::Horizontal, QObject::trUtf8("Тип топлива"));
    ui->tableView->setModel(model_Oper);
    ui->tableView->hideColumn(0);
    ui->tableView->hideColumn(4);

    QSqlRelationalTableModel *comboModel = new QSqlRelationalTableModel(0);
    comboModel->setTable("hm_fuel");
    int comboIndex = comboModel->fieldIndex("id");
    comboModel->setRelation(comboIndex, QSqlRelation("hm_fuel", "id", "name"));
    comboModel->select();
    QSqlTableModel *comboRelModel = comboModel->relationModel(comboIndex);
    ui->comboBox->setModel(comboRelModel);
    ui->comboBox->setModelColumn(comboRelModel->fieldIndex("name"));
}

void BadgeWindow::timer()
{
    DialogTimer dialogTimer;

    bool saveRequest;
    QString taskName;
    QTime elapsedTime;

    saveRequest = dialogTimer.getTaskInfo(taskName, elapsedTime);

    if (saveRequest) {
        DialogTask dialog;
        QSqlRelationalTableModel *taskModel;

        taskModel = new QSqlRelationalTableModel();

        taskModel->setEditStrategy(QSqlTableModel::OnManualSubmit);
        taskModel->setTable("task");
        qDebug() << "Day" << QDate::currentDate().toString("yyyy-MM-dd") << endl;
        taskModel->setFilter("day='" + QDate::currentDate().toString("yyyy-MM-dd") + "'");
        //taskModel.setEditStrategy(QSqlTableModel::OnFieldChange);
        taskModel->setHeaderData(0, Qt::Horizontal, QObject::tr("ID"));
        taskModel->setHeaderData(1, Qt::Horizontal, QObject::tr("Date"));
        taskModel->setHeaderData(2, Qt::Horizontal, QObject::tr("Elapsed"));
        //taskModel->setHeaderData(3, Qt::Horizontal, QObject::tr("Attivita'"));
        taskModel->setRelation(3, QSqlRelation("attivita", "id", "attivita"));

        taskModel->select();

        // TODO: if current date don't exist create it


        dialog.openTask("Task", "Elapsed", taskModel, QDate::currentDate(), "task", elapsedTime, taskName);

        delete taskModel;
    }
}

Widget::Widget(QWidget *parent)
    : QWidget(parent)
{
    resize(400,300);
    if(connect())
    {
        QTableView *view = new QTableView(this);
        QSqlRelationalTableModel *model = new QSqlRelationalTableModel;//要先有数据才能创建model,也就是先连接数据库局
        view->setGeometry(0,0,400,300);

        view->setModel(model);
        model->setTable("student");
        model->setSort(0,Qt::AscendingOrder);
//        model->setHeaderData(0,Qt::Horizontal,"Name");//from 0
//        model->setHeaderData(1,Qt::Horizontal,"Age");
//        model->setHeaderData(2,Qt::Horizontal,"likes");
        model->setRelation(3,QSqlRelation("city","id","name"));
        model->select();


//        view->setModel(model);

        view->setSelectionMode(QAbstractItemView::SingleSelection);//设置选择模式
        view->setSelectionBehavior(QAbstractItemView::SelectRows);//选择行
//        view->resizeColumnsToContents();//tiaozhengziti
        view->setEditTriggers(QAbstractItemView::DoubleClicked);//不可编辑?

        QHeaderView *header =  view->horizontalHeader();
        header->setStretchLastSection(true);
        view->setItemDelegate(new QSqlRelationalDelegate(view));
//        QHeaderView *head = view->verticalHeader();
//        head->setStretchLastSection(true);
//        view->show();
//        this?->show();
    }
}

/*
** public:
*/
MainWindow::MainWindow(QWidget *parent)
    : QMainWindow(parent)
    , m_timer(new QBasicTimer)
    , m_dataProvider(new DataProvider(this))
    , m_dialogSettings(0) // Guarded by QPointer
    , m_dialogAbout(0)    // Guarded by QPointer
{
    qDebug() << Q_FUNC_INFO;

/*
    m_dataModel = new QStandardItemModel(this);
    m_dataModel->setColumnCount(5);
*/
    setupUi(this);

/*
    QStringList labels;
    labels << tr("Show name") << tr("Episode name")
           << tr("Season #") << tr("Episode #") << tr("Date");
    m_dataModel->setHorizontalHeaderLabels(labels);
//    showsTableView->setModel(m_dataModel);
    showsTableView->setModel(DbInterface::instance().readEpisodes());
    showsTableView->verticalHeader()->hide();
    showsTableView->horizontalHeader()->setResizeMode(QHeaderView::Interactive);
    showsTableView->horizontalHeader()->setStretchLastSection(true);
    showsTableView->horizontalHeader()->setCascadingSectionResizes(true);
    showsTableView->horizontalHeader()->setHighlightSections(true);
    showsTableView->horizontalHeader()->setMovable(true);

    srand(QTime::currentTime().toString("hhmmsszzz").toUInt());

    // Fill with random stuff
    for (int i=0; i<30; ++i) {
        m_dataModel->setItem(i, 0, new QStandardItem(QString("show_%1").arg(i)));
        m_dataModel->setItem(i, 1, new QStandardItem(QString("episode_%1").arg(i)));
        m_dataModel->setItem(i, 2, new QStandardItem(QString("%1").arg(rand()%10+1)));
        m_dataModel->setItem(i, 3, new QStandardItem(QString("%1").arg(rand()%24+1)));
        QDate rndDate = QDate::currentDate().addDays(i);
        m_dataModel->setItem(i, 4, new QStandardItem(rndDate.toString("yyyy/MM/dd")));
    }
*/

    setWindowTitle(QString("nextShows - v%1").arg(NEXTSHOWS_VERSION));
    statusBar()->showMessage(tr("nextShows started"), 1000*3);


    // TEMPORARY CODE FOR TESTING ////////////////////////////////////////////
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "NS_View");
    db.setDatabaseName("ns.db");
    db.open();
    QSqlRelationalTableModel *model = new QSqlRelationalTableModel(this, db);
    model->setTable("T_Episodes");
    model->setRelation(model->fieldIndex("shows_id"),
                       QSqlRelation("T_Shows", "idT_Shows", "ShowName"));
    model->setSort(model->fieldIndex("Date"), Qt::AscendingOrder);
    model->select();

    QStringList hideFields;
    hideFields << "idT_Episodes" << "ProdNumber" << "EpisodeCount"
               << "EpisodeUrl"   << "isSpecial";
    foreach (QString field, hideFields) {
        model->removeColumn(model->fieldIndex(field));
    }

HandbookDialog::HandbookDialog(QString tableName, QString tableRusName,
                               const QSqlDatabase &database,
                               QWidget *parent,
                               QStringList hiddenColumns):
    m_database(database),
    mHiddenColumns(hiddenColumns),
    QDialog(parent),
    ui(new Ui::HandbookDialog)
{
    ui->setupUi(this);

    ui->label->setText(tableRusName);
    this->setWindowTitle("Редактирование справочника: " + tableRusName);

    QMap<QString, QString> rusFieldNames = DataBaseExpert::fieldTranslationMap(m_database);
    auto ralTablesForFields = DataBaseExpert::ralationTablesForFields(m_database);
    m_record = database.record(tableName);

    QSqlRelationalTableModel * model = new QSqlRelationalTableModel(0, database);
    model->setTable(tableName);
    for (int i = 0; i < m_record.count(); ++i)
    {
        QString fieldName = m_record.fieldName(i);
        if (fieldName.size() > 3 && fieldName.right(3) == "_FK")
        {
            model->setRelation(i, QSqlRelation(ralTablesForFields[fieldName], "UID", "NAME"));
        }
    }
    model->setEditStrategy(QSqlTableModel::OnManualSubmit);
    //model->removeColumn(0);
    model->select();

    for (int i = 0; i < m_record.count(); ++i)
    {
        model->setHeaderData(i, Qt::Horizontal, rusFieldNames[m_record.fieldName(i)]);
        if (mHiddenColumns.contains(m_record.fieldName(i), Qt::CaseInsensitive))
        {
            ui->tableView->setColumnHidden(i, true);
        }
    }

    ui->tableView->setModel(model);
    ui->tableView->setItemDelegate(new MySqlRelationDelegate(ui->tableView));

    for (int i = 0; i < m_record.count(); ++i)
    {
        if (mHiddenColumns.contains(m_record.fieldName(i), Qt::CaseInsensitive))
        {
            ui->tableView->setColumnHidden(i, true);
        }
    }

    connect(ui->btnInsert, &QPushButton::clicked, [model]()
    {
        model->insertRow(model->rowCount());
    });
    connect(ui->btnSave, &QPushButton::clicked, [model]()
    {
        if (!model->submitAll())
            qDebug() << model->lastError().text();
    });
    connect(ui->btnRevert, &QPushButton::clicked, [model]()
    {
        model->revertAll();
    });
    connect(ui->btnExit, &QPushButton::clicked, [this]()
    {
        this->accept();
    });

    ui->tableView->horizontalHeader()->setSectionResizeMode(QHeaderView::ResizeMode::Stretch);


    connect(ui->tableView, &QTableView::customContextMenuRequested, [this, &tableName, model] (const QPoint& pos)
    {
        QMenu * contextMenu = new QMenu(tr("Выбор действия со строкой"), ui->tableView);

        QAction* insertAction = new QAction(tr("Добавить"), contextMenu);
        contextMenu->addAction(insertAction);
        connect(insertAction, &QAction::triggered, [this, &model] ()
        {
            model->insertRow(model->rowCount());
        });

        QAction * delAction = new QAction(tr("Удалить"), contextMenu);
        contextMenu->addAction(delAction);
        connect(delAction, &QAction::triggered, [this, &pos, &tableName, model] ()
        {
            QPoint transformPos = pos;
            transformPos.setX(20);
            QModelIndex index = ui->tableView->indexAt(transformPos);
            QString recordId = ui->tableView->model()->data(index).toString();
            QSqlQuery query("DELETE FROM " + tableName + " WHERE UID = " + recordId, m_database);
            query,exec();
            model->submitAll();
        });


        contextMenu->exec(ui->tableView->viewport()->mapToGlobal(pos));
    });
//.........这里部分代码省略.........