diff options
author | DaNiMoTh <jjdanimoth@gmail.com> | 2011-07-19 14:57:07 +1000 |
---|---|---|
committer | Qt by Nokia <qt-info@nokia.com> | 2011-07-20 08:24:59 +0200 |
commit | 23e4d1a51765f62fc6b95d0ebdb426b03fb1d059 (patch) | |
tree | 25cd8c50635bad09cc988501f7bce57d2625b5be /src/sql/models/qsqlrelationaltablemodel.cpp | |
parent | 61d2ba9b89f413b7b39b18e21fd0293297257e1b (diff) |
Fixed behavior on NULL foreign keys
Instead of using a simple INNER JOIN, like:
SELECT a,b,rel.c FROM table1, table2 WHERE (table1.smthing =
table2.smthing)
which doesn't show row where foreign keys are NULL, allow use of LEFT
JOIN like:
SELECT a,b,rel.c FROM table1 LEFT JOIN table2 ON table1.smthing =
table2.smthing
The trick works also for multi-relational tables.
Just remember to use the new API setJoinMode.
Signed-off-by: DaNiMoTh <jjdanimoth@gmail.com>
Task-number:QTBUG-8217
Reviewed-by:Michael Goddard
Reviewed-by:Charles Yin
Merge-request: 2576
Reviewed-by: Charles Yin <charles.yin@nokia.com>
(cherry picked from commit c4280dbd9bb37cca21d007f5f8b9217f80b44043)
Change-Id: I349f9418e4859923977942add59872b000cac2c5
Reviewed-on: http://codereview.qt.nokia.com/1853
Reviewed-by: Charles Yin <charles.yin@nokia.com>
Diffstat (limited to 'src/sql/models/qsqlrelationaltablemodel.cpp')
-rw-r--r-- | src/sql/models/qsqlrelationaltablemodel.cpp | 63 |
1 files changed, 51 insertions, 12 deletions
diff --git a/src/sql/models/qsqlrelationaltablemodel.cpp b/src/sql/models/qsqlrelationaltablemodel.cpp index bdc32ea6ac..bc96e8362a 100644 --- a/src/sql/models/qsqlrelationaltablemodel.cpp +++ b/src/sql/models/qsqlrelationaltablemodel.cpp @@ -224,7 +224,8 @@ class QSqlRelationalTableModelPrivate: public QSqlTableModelPrivate Q_DECLARE_PUBLIC(QSqlRelationalTableModel) public: QSqlRelationalTableModelPrivate() - : QSqlTableModelPrivate() + : QSqlTableModelPrivate(), + joinMode( QSqlRelationalTableModel::InnerJoin ) {} QString relationField(const QString &tableName, const QString &fieldName) const; @@ -237,6 +238,7 @@ public: void revertCachedRow(int row); void translateFieldNames(int row, QSqlRecord &values) const; + QSqlRelationalTableModel::JoinMode joinMode; }; static void qAppendWhereClause(QString &query, const QString &clause1, const QString &clause2) @@ -575,29 +577,55 @@ QString QSqlRelationalTableModel::selectStatement() const 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())); + if (d->joinMode == QSqlRelationalTableModel::InnerJoin) { + // this needs fixing!! the below if is borken. + // Use LeftJoin mode if you want correct behavior + 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 { + tables.append(QLatin1String(" LEFT JOIN")); + tables.append(relation.tableName().append(QLatin1Char(' ')).append(relTableAlias)); + tables.append(QLatin1String("ON")); + + QString clause; + clause.append(d->relationField(tableName(), d->db.driver()->escapeIdentifier(rec.fieldName(i), QSqlDriver::FieldName))); + clause.append(QLatin1String(" = ")); + clause.append(d->relationField(relTableAlias, relation.indexColumn())); + + tables.append(clause); + } } else { if (!fList.isEmpty()) fList.append(QLatin1String(", ")); fList.append(d->relationField(tableName(), d->db.driver()->escapeIdentifier(rec.fieldName(i), QSqlDriver::FieldName))); } } - if (!tables.isEmpty()) + + if (d->joinMode == QSqlRelationalTableModel::InnerJoin && !tables.isEmpty()) { tList.append(tables.join(QLatin1String(", "))); + if(!tList.isEmpty()) + tList.prepend(QLatin1String(", ")); + } else + 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()); + + if (d->joinMode == QSqlRelationalTableModel::InnerJoin) { + qAppendWhereClause(query, where, filter()); + } else if (!filter().isEmpty()) { + query.append(QLatin1String(" WHERE (")); + query.append(filter()); + query.append(QLatin1String(")")); + } QString orderBy = orderByClause(); if (!orderBy.isEmpty()) @@ -648,7 +676,18 @@ void QSqlRelationalTableModel::clear() d->relations.clear(); QSqlTableModel::clear(); } +/*! + Sets the SQL join mode to show or hide rows with NULL foreign keys. + In InnerJoin mode (the default) these rows will not be showed: use the + LeftJoin mode if you want to show them. + \since 4.8 +*/ +void QSqlRelationalTableModel::setJoinMode( QSqlRelationalTableModel::JoinMode joinMode ) +{ + Q_D(QSqlRelationalTableModel); + d->joinMode = joinMode; +} /*! \reimp */ |