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>

Change-Id: I349f9418e4859923977942add59872b000cac2c5
Task-number:QTBUG-8217
Reviewed-by:Michael Goddard
Reviewed-by:Charles Yin

Merge-request: 2576
Reviewed-by: Charles Yin <charles.yin@nokia.com>

2 files changed, 57 insertions, 12 deletions

diff --git a/src/sql/models/qsqlrelationaltablemodel.cpp b/src/sql/models/qsqlrelationaltablemodel.cpp
index bdc32ea..bc96e83 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
 */
diff --git a/src/sql/models/qsqlrelationaltablemodel.h b/src/sql/models/qsqlrelationaltablemodel.h
index 15faae9..32d88b3 100644
--- a/src/sql/models/qsqlrelationaltablemodel.h
+++ b/src/sql/models/qsqlrelationaltablemodel.h
@@ -76,6 +76,11 @@ class Q_SQL_EXPORT QSqlRelationalTableModel: public QSqlTableModel
     Q_OBJECT
 
 public:
+    enum JoinMode {
+        InnerJoin,
+        LeftJoin
+    };
+
     explicit QSqlRelationalTableModel(QObject *parent = 0,
                                       QSqlDatabase db = QSqlDatabase());
     virtual ~QSqlRelationalTableModel();
@@ -91,6 +96,7 @@ public:
     virtual void setRelation(int column, const QSqlRelation &relation);
     QSqlRelation relation(int column) const;
     virtual QSqlTableModel *relationModel(int column) const;
+    void setJoinMode( QSqlRelationalTableModel::JoinMode joinMode );
 
 public Q_SLOTS:
     void revertRow(int row);