/*! \reimp */ QString QSqlRelationalTableModel::selectStatement() const { Q_D(const QSqlRelationalTableModel); QString query; if (tableName().isEmpty()) return query; if (d->relations.isEmpty()) return QSqlTableModel::selectStatement(); QString tList; QString fList; QString where; QSqlRecord rec = d->baseRec; QStringList tables; const QRelation nullRelation; // Count how many times each field name occurs in the record QHash<QString, int> fieldNames; QStringList fieldList; for (int i = 0; i < rec.count(); ++i) { QSqlRelation relation = d->relations.value(i, nullRelation).rel; QString name; if (relation.isValid()) { // Count the display column name, not the original foreign key name = relation.displayColumn(); if (d->db.driver()->isIdentifierEscaped(name, QSqlDriver::FieldName)) name = d->db.driver()->stripDelimiters(name, QSqlDriver::FieldName); QSqlRecord rec = database().record(relation.tableName()); for (int i = 0; i < rec.count(); ++i) { if (name.compare(rec.fieldName(i), Qt::CaseInsensitive) == 0) { name = rec.fieldName(i); break; } } } else name = rec.fieldName(i); fieldNames.insert(name, fieldNames.value(name, 0) + 1); fieldList.append(name); } for (int i = 0; i < rec.count(); ++i) { QSqlRelation relation = d->relations.value(i, nullRelation).rel; if (relation.isValid()) { QString relTableAlias = QString::fromLatin1("relTblAl_%1").arg(i); if (!fList.isEmpty()) fList.append(QLatin1String(", ")); fList.append(d->relationField(relTableAlias,relation.displayColumn())); // If there are duplicate field names they must be aliased if (fieldNames.value(fieldList[i]) > 1) { QString relTableName = relation.tableName().section(QChar::fromLatin1('.'), -1, -1); if (d->db.driver()->isIdentifierEscaped(relTableName, QSqlDriver::TableName)) relTableName = d->db.driver()->stripDelimiters(relTableName, QSqlDriver::TableName); QString displayColumn = relation.displayColumn(); if (d->db.driver()->isIdentifierEscaped(displayColumn, QSqlDriver::FieldName)) displayColumn = d->db.driver()->stripDelimiters(displayColumn, QSqlDriver::FieldName); fList.append(QString::fromLatin1(" AS %1_%2_%3").arg(relTableName).arg(displayColumn).arg(fieldNames.value(fieldList[i]))); fieldNames.insert(fieldList[i], fieldNames.value(fieldList[i])-1); } // this needs fixing!! the below if is borken. tables.append(relation.tableName().append(QLatin1Char(' ')).append(relTableAlias)); if(!where.isEmpty()) where.append(QLatin1String(" AND ")); where.append(d->relationField(tableName(), d->db.driver()->escapeIdentifier(rec.fieldName(i), QSqlDriver::FieldName))); where.append(QLatin1String(" = ")); where.append(d->relationField(relTableAlias, relation.indexColumn())); } else { if (!fList.isEmpty()) fList.append(QLatin1String(", ")); fList.append(d->relationField(tableName(), d->db.driver()->escapeIdentifier(rec.fieldName(i), QSqlDriver::FieldName))); } } if (!tables.isEmpty()) tList.append(tables.join(QLatin1String(", "))); if (fList.isEmpty()) return query; if(!tList.isEmpty()) tList.prepend(QLatin1String(", ")); tList.prepend(tableName()); query.append(QLatin1String("SELECT ")); query.append(fList).append(QLatin1String(" FROM ")).append(tList); qAppendWhereClause(query, where, filter()); QString orderBy = orderByClause(); if (!orderBy.isEmpty()) query.append(QLatin1Char(' ')).append(orderBy); return query; }
QString SqlRelationalTableModel::selectStatement() const { QString query; if (tableName().isEmpty()) return query; QString tList; QString jList; QString fList; QString where; QSqlRecord rec = record(); QStringList tables; // Count how many times each field name occurs in the record QHash<QString, int> fieldNames; QStringList fieldList; for (int i = 0; i < rec.count(); ++i) { SqlRelation relation = this->relation(i); QString name; if (relation.isValid()) { // Count the display column name, not the original foreign key name = relation.displayColumn(); if (database().driver()->isIdentifierEscaped(name, QSqlDriver::FieldName)) name = database().driver()->stripDelimiters(name, QSqlDriver::FieldName); QSqlRecord rec = database().record(relation.tableName()); for (int i = 0; i < rec.count(); ++i) { if (name.compare(rec.fieldName(i), Qt::CaseInsensitive) == 0) { name = rec.fieldName(i); break; } } } else name = rec.fieldName(i); fieldNames.insert(name, fieldNames.value(name, 0) + 1); fieldList.append(name); } for (int i = 0; i < rec.count(); ++i) { SqlRelation relation = this->relation(i); if (relation.isValid()) { QString relTableAlias = QString::fromLatin1("relTblAl_%1").arg(i); if (!fList.isEmpty()) fList.append(QLatin1String(", ")); fList.append(relationField(relTableAlias,relation.displayColumn())); // If there are duplicate field names they must be aliased if (fieldNames.value(fieldList[i]) > 1) { QString relTableName = relation.tableName().section(QChar::fromLatin1('.'), -1, -1); if (database().driver()->isIdentifierEscaped(relTableName, QSqlDriver::TableName)) relTableName = database().driver()->stripDelimiters(relTableName, QSqlDriver::TableName); QString displayColumn = relation.displayColumn(); if (database().driver()->isIdentifierEscaped(displayColumn, QSqlDriver::FieldName)) displayColumn = database().driver()->stripDelimiters(displayColumn, QSqlDriver::FieldName); fList.append(QString::fromLatin1(" AS %1_%2_%3").arg(relTableName).arg(displayColumn).arg(fieldNames.value(fieldList[i]))); fieldNames.insert(fieldList[i], fieldNames.value(fieldList[i])-1); } // this needs fixing!! the below if is borken. //tables.append(relation.tableName().append(QLatin1Char(' ')).append(relTableAlias)); //if(!where.isEmpty()) //where.append(QLatin1String(" AND ")); switch(relation.getJoinKind()){ case SqlRelation::RIGHT: jList.append(QLatin1String(" RIGHT JOIN ")); break; case SqlRelation::LEFT: jList.append(QLatin1String(" LEFT JOIN ")); break; case SqlRelation::OUTER: jList.append(QLatin1String(" FULL OUTER JOIN ")); break; case SqlRelation::INNER: jList.append(QLatin1String(" INNER JOIN ")); break; default: jList.append(QLatin1String(" JOIN ")); break; } jList.append(relation.tableName().append(QLatin1Char(' ')).append(relTableAlias)); jList.append(QLatin1String(" ON ")); jList.append(relationField(tableName(), database().driver()->escapeIdentifier(rec.fieldName(i), QSqlDriver::FieldName))); jList.append(QLatin1String(" = ")); jList.append(relationField(relTableAlias, relation.indexColumn())); } else { if (!fList.isEmpty()) fList.append(QLatin1String(", ")); fList.append(relationField(tableName(), database().driver()->escapeIdentifier(rec.fieldName(i), QSqlDriver::FieldName))); } } for(int i = 0; i < rec.count(); ++i) { SqlRelation relation = this->relation(i); if(relation.isValid()){ fList.append(QLatin1String(", ")); fList.append(relationField(QString::fromLatin1("relTblAl_%1").arg(i),relation.indexColumn())); fList.append(QString::fromLatin1(" AS %1_%2").arg(relation.tableName().section(QChar::fromLatin1('.'), -1, -1)).arg(relation.indexColumn())); } } if(!additionalField.isEmpty()) fList.append(additionalField+" "); if (!tables.isEmpty()) tList.append(tables.join(QLatin1String(", "))); if (fList.isEmpty()) return query; if(!tList.isEmpty()) tList.prepend(QLatin1String(", ")); tList.prepend(tableName()); query.append(QLatin1String("SELECT ")); query.append(fList).append(QLatin1String(" FROM ")).append(tList); query.append(jList); query = qAppendWhereClause(query, where, filter()); query.append(groupByClause); QString orderBy = orderByClause(); if (!orderBy.isEmpty()) query.append(QLatin1Char(' ')).append(orderBy); return query; }
/*! \reimp */ QString QSqlRelationalTableModel::selectStatement() const { Q_D(const QSqlRelationalTableModel); QString query; if (tableName().isEmpty()) return query; if (d->relations.isEmpty()) return QSqlTableModel::selectStatement(); QString tList; QString fList; QString where; QSqlRecord rec = d->baseRec; QStringList tables; const QRelation nullRelation; // Count how many times each field name occurs in the record QHash<QString, int> fieldNames; for (int i = 0; i < rec.count(); ++i) { QSqlRelation relation = d->relations.value(i, nullRelation).rel; QString name; if (relation.isValid()) // Count the display column name, not the original foreign key name = relation.displayColumn(); else name = rec.fieldName(i); fieldNames.insert(name, fieldNames.value(name, 0) + 1); } for (int i = 0; i < rec.count(); ++i) { QSqlRelation relation = d->relations.value(i, nullRelation).rel; if (relation.isValid()) { QString relTableAlias = QString::fromLatin1("relTblAl_%1").arg(i); fList.append(d->escapedRelationField(relTableAlias, relation.displayColumn())); // If there are duplicate field names they must be aliased if (fieldNames.value(relation.displayColumn()) > 1) fList.append(QString::fromLatin1(" AS %1_%2").arg(relation.tableName()).arg(relation.displayColumn())); fList.append(QLatin1Char(',')); if (!tables.contains(relation.tableName())) tables.append(d->db.driver()->escapeIdentifier(relation.tableName(), QSqlDriver::TableName).append(QLatin1String(" ")).append( d->db.driver()->escapeIdentifier(relTableAlias, QSqlDriver::TableName))); where.append(d->escapedRelationField(tableName(), rec.fieldName(i))); where.append(QLatin1Char('=')); where.append(d->escapedRelationField(relTableAlias, relation.indexColumn())); where.append(QLatin1String(" AND ")); } else { fList.append(d->escapedRelationField(tableName(), rec.fieldName(i))); fList.append(QLatin1Char(',')); } } if (!tables.isEmpty()) tList.append(tables.join(QLatin1String(","))).append(QLatin1String(",")); if (fList.isEmpty()) return query; tList.prepend(QLatin1Char(',')).prepend(d->db.driver()->escapeIdentifier(tableName(), QSqlDriver::TableName)); // truncate tailing comma tList.chop(1); fList.chop(1); query.append(QLatin1String("SELECT ")); query.append(fList).append(QLatin1String(" FROM ")).append(tList); if (!where.isEmpty()) where.chop(5); qAppendWhereClause(query, where, filter()); QString orderBy = orderByClause(); if (!orderBy.isEmpty()) query.append(QLatin1Char(' ')).append(orderBy); return query; }