/*!
\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;
}
