waldo1979
6th April 2009, 07:11
I'm having a bit of trouble with QSqlRelationalTableModel. Every time I submitAll(), the value of a relational column (Customers.name) is written to my table instead of the integer key (Projects.customerId). Below is some pseudo code. Any suggestions for how to debug and/or resolve this issue?
The field correctly reads (customerNameLineEdit gets populated with the correct value) however a problem occurs on write. I've tried with and without foreign keys. I'm using MySql as my database.
CREATE TABLE IF NOT EXISTS `projects` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`customerId` int(11) NOT NULL,
`industryId` int(11) DEFAULT NULL,
`opCode` varchar(255) DEFAULT NULL,
`statusId` int(11) DEFAULT NULL,
`startDate` date DEFAULT NULL,
`endDate` date DEFAULT NULL,
`notes` text,
PRIMARY KEY (`id`),
UNIQUE KEY `opCode` (`opCode`),
KEY `customerId` (`customerId`),
KEY `status` (`statusId`),
KEY `industryId` (`industryId`),
KEY `customerId_2` (`customerId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=39 ;
CREATE TABLE IF NOT EXISTS `customers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=16 ;
// SETUP THE TABLE
projectsModel.setTable("Projects");
projectsModel.setRelation(1, QSqlRelation("Customers", "id", "name"));
projectsModel.setRelation(2, QSqlRelation("refIndustries", "naicCode", "name"));
projectsModel.setRelation(4, QSqlRelation("refStatuses", "id", "name"));
projectsModel.select();
mapper = new QDataWidgetMapper(this);
mapper->setModel(projectModel);
mapper->setItemDelegate(new QSqlRelationalDelegate(mapper));
mapper->addMapping(ui->customerNameLineEdit, projectModel->fieldIndex("Customers_Name"));
mapper->addMapping(ui->industryComboBox, projectModel->fieldIndex("refIndustries_name"));
mapper->addMapping(ui->projectCodeLineEdit, projectModel->fieldIndex("opCode"));
mapper->addMapping(ui->statusComboBox, projectModel->fieldIndex("refStatuses_name"));
mapper->addMapping(ui->startDateDateEdit, projectModel->fieldIndex("startDate"));
mapper->addMapping(ui->endDateDateEdit, projectModel->fieldIndex("endDate"));
mapper->addMapping(ui->notesTextEdit, projectModel->fieldIndex("notes"));
mapper->setSubmitPolicy(QDataWidgetMapper::ManualSubmit);
mapper->setCurrentIndex(rowId);
// LATER I DO A SUBMIT LIKE THIS
mapper->submit())
projectModel->submitAll()
The field correctly reads (customerNameLineEdit gets populated with the correct value) however a problem occurs on write. I've tried with and without foreign keys. I'm using MySql as my database.
CREATE TABLE IF NOT EXISTS `projects` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`customerId` int(11) NOT NULL,
`industryId` int(11) DEFAULT NULL,
`opCode` varchar(255) DEFAULT NULL,
`statusId` int(11) DEFAULT NULL,
`startDate` date DEFAULT NULL,
`endDate` date DEFAULT NULL,
`notes` text,
PRIMARY KEY (`id`),
UNIQUE KEY `opCode` (`opCode`),
KEY `customerId` (`customerId`),
KEY `status` (`statusId`),
KEY `industryId` (`industryId`),
KEY `customerId_2` (`customerId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=39 ;
CREATE TABLE IF NOT EXISTS `customers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=16 ;
// SETUP THE TABLE
projectsModel.setTable("Projects");
projectsModel.setRelation(1, QSqlRelation("Customers", "id", "name"));
projectsModel.setRelation(2, QSqlRelation("refIndustries", "naicCode", "name"));
projectsModel.setRelation(4, QSqlRelation("refStatuses", "id", "name"));
projectsModel.select();
mapper = new QDataWidgetMapper(this);
mapper->setModel(projectModel);
mapper->setItemDelegate(new QSqlRelationalDelegate(mapper));
mapper->addMapping(ui->customerNameLineEdit, projectModel->fieldIndex("Customers_Name"));
mapper->addMapping(ui->industryComboBox, projectModel->fieldIndex("refIndustries_name"));
mapper->addMapping(ui->projectCodeLineEdit, projectModel->fieldIndex("opCode"));
mapper->addMapping(ui->statusComboBox, projectModel->fieldIndex("refStatuses_name"));
mapper->addMapping(ui->startDateDateEdit, projectModel->fieldIndex("startDate"));
mapper->addMapping(ui->endDateDateEdit, projectModel->fieldIndex("endDate"));
mapper->addMapping(ui->notesTextEdit, projectModel->fieldIndex("notes"));
mapper->setSubmitPolicy(QDataWidgetMapper::ManualSubmit);
mapper->setCurrentIndex(rowId);
// LATER I DO A SUBMIT LIKE THIS
mapper->submit())
projectModel->submitAll()