/*!
\reimp
*/
QString QSqlRelationalTableModel::orderByClause() const
{
Q_D(const QSqlRelationalTableModel);
const QSqlRelation rel = d->relations.value(d->sortColumn).rel;
if (!rel.isValid())
return QSqlTableModel::orderByClause();
QString f = d->fullyQualifiedFieldName(Sql::relTablePrefix(d->sortColumn), rel.displayColumn());
f = d->sortOrder == Qt::AscendingOrder ? Sql::asc(f) : Sql::desc(f);
return Sql::orderBy(f);
}
/*!
\reimp
*/
QString QSqlRelationalTableModel::orderByClause() const
{
Q_D(const QSqlRelationalTableModel);
const QSqlRelation rel = d->relations.value(d->sortColumn).rel;
if (!rel.isValid())
return QSqlTableModel::orderByClause();
QString s = QLatin1String("ORDER BY ");
s.append(d->relationField(QLatin1String("relTblAl_") + QString::number(d->sortColumn),
rel.displayColumn()));
s += d->sortOrder == Qt::AscendingOrder ? QLatin1String(" ASC") : QLatin1String(" DESC");
return s;
}
/*!
\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;
}
bool QRelation::isValid()
{
return (rel.isValid() && m_parent != NULL);
}
/*!
\reimp
*/
QString QSqlRelationalTableModel::selectStatement() const
{
Q_D(const QSqlRelationalTableModel);
if (tableName().isEmpty())
return QString();
if (d->relations.isEmpty())
return QSqlTableModel::selectStatement();
// Count how many times each field name occurs in the record
QHash<QString, int> fieldNames;
QStringList fieldList;
for (int i = 0; i < d->baseRec.count(); ++i) {
QSqlRelation relation = d->relations.value(i).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);
const 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 = d->baseRec.fieldName(i);
}
fieldNames[name] = fieldNames.value(name, 0) + 1;
fieldList.append(name);
}
QString fList;
QString conditions;
QString from = Sql::from(tableName());
for (int i = 0; i < d->baseRec.count(); ++i) {
QSqlRelation relation = d->relations.value(i).rel;
const QString tableField = d->fullyQualifiedFieldName(tableName(), d->db.driver()->escapeIdentifier(d->baseRec.fieldName(i), QSqlDriver::FieldName));
if (relation.isValid()) {
const QString relTableAlias = Sql::relTablePrefix(i);
QString displayTableField = d->fullyQualifiedFieldName(relTableAlias, relation.displayColumn());
// Duplicate field names 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);
const QString alias = QString::fromLatin1("%1_%2_%3").arg(relTableName).arg(displayColumn).arg(fieldNames.value(fieldList[i]));
displayTableField = Sql::as(displayTableField, alias);
--fieldNames[fieldList[i]];
}
fList = Sql::comma(fList, displayTableField);
// Join related table
const QString tblexpr = Sql::concat(relation.tableName(), relTableAlias);
const QString relTableField = d->fullyQualifiedFieldName(relTableAlias, relation.indexColumn());
const QString cond = Sql::eq(tableField, relTableField);
if (d->joinMode == QSqlRelationalTableModel::InnerJoin) {
// FIXME: InnerJoin code is known to be broken.
// Use LeftJoin mode if you want correct behavior.
from = Sql::comma(from, tblexpr);
conditions = Sql::et(conditions, cond);
} else {
from = Sql::concat(from, Sql::leftJoin(tblexpr));
from = Sql::concat(from, Sql::on(cond));
}
} else {
fList = Sql::comma(fList, tableField);
}
}
if (fList.isEmpty())
return QString();
const QString stmt = Sql::concat(Sql::select(fList), from);
const QString where = Sql::where(Sql::et(Sql::paren(conditions), Sql::paren(filter())));
return Sql::concat(Sql::concat(stmt, where), orderByClause());
}
/*!
\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;
}