How to show DB NULL in QTableView

[deleted385]

In QtWidget app, If I do this:

model = new QSqlQueryModel(this);
model->setQuery(query);
table->setModel(model);

it doesn't show NULL in the QTableView column where the the value is NULL in the database.

[JonB]

UPDATE
QStyledItemDelegate::initStyleOption() source code at https://code.woboq.org/qt5/qtbase/src/widgets/itemviews/qstyleditemdelegate.cpp.html#346 has:

    value = index.data(Qt::DisplayRole);
    if (value.isValid() && !value.isNull()) {
        option->features |= QStyleOptionViewItem::HasDisplay;
        option->text = displayText(value, option->locale);
    }

So an invalid or null value never calls displayText(). I am guessing this must be your case!

So maybe you need to override QStyledItemDelegate::initStyleOption() with something like

    QStyledItemDelegate::initStyleOption(option, index);    // call base initialisation first
    value = index.data(Qt::DisplayRole);
    if (whatever condition to recognise `NULL` in value/column)
    {
         option->features |= QStyleOptionViewItem::HasDisplay; 
         option->text = "[NULL]";
    }

[ChrisW67]

It shows nothing in the cell because there is nothing in the database. If you would like to customise that behaviour you can do it with a QStyledItemDelegate. Something like:

MyDelegate::displayText(const QVariant &value, const QLocale &locale) const
{
  if (value.isNull()) {
    return QStringLiteral("{null}");
  else 
    return QStyledItemDelegate::displayText(value, locale);
}

but you can get much more fancy if you need to. You should consider what happens if the database column actually contains the string "NULL" or "{null}" or whatever placeholder you use.

[deleted385]

@ChrisW67, in database the last two columns are NULL but In my app those are blank like this:

In the subclass of QStyledItemDelegate I've done this:

QString StyleDelegate::displayText(const QVariant &value, const QLocale &locale) const
{
    qDebug() << "here " << value;
    if (value.isNull()) {
        return QStringLiteral("{null}");
        //return QStyledItemDelegate::displayText("NULL", locale);
    }
    else
        return QStyledItemDelegate::displayText(value, locale);
}

and in the constructor of QTableView sub class have done this:

itemDelegate = new StyleDelegate(this);
setItemDelegate(itemDelegate);

in the QtCreator output I've got these:

here  QVariant(qlonglong, 10)
here  QVariant(qlonglong, 108)
here  QVariant(qlonglong, 21)
here  QVariant(qlonglong, 1)
here  QVariant(QString, "????")
here  QVariant(QString, "PRON")
here  QVariant(QString, "1S")
here  QVariant(QString, "")

looks like for the last two columns the StyleDelegate didn't do anything.

[JonB]

@Emon-Haque
First try !value.isValid() instead in case that works.

Otherwise debug/put in qDebug() statements to see either what displayText() is called with when the database value is NULL or that it is not called at all for NULL, since Qt considers there is "nothing there" to display, in which case report that back and we will reconsider what you need to do for NULL.

EDIT
Actually from what you showed of the output it looks like you are indeed in the second case, displayText() is not being called to display anything when the value is NULL. Right? So far as it is concerned there is "nothing there to display". I will have a think about that....

[JonB]

@Emon-Haque
I am not finding any questions/answers as to what to do about QStyledItemDelegate::displayText() apparently not being called when database value is NULL. Which is perhaps surprising if that is indeed the case. I leave you with a few thoughts:

• Verify that that this indeed appears to be the case.
• Subclass QSqlQueryModel and override data() method to discover just what kind of QVariant is being stored/returned for NULL.
• All I can think of is interpose some kind of QIdentityProxyModel between QTableView and QSqlQueryModel, where you override its data() method to recognise NULL and return "NULL" for the table view's purpose. Until someone shows you something better....

[JonB]

UPDATE
QStyledItemDelegate::initStyleOption() source code at https://code.woboq.org/qt5/qtbase/src/widgets/itemviews/qstyleditemdelegate.cpp.html#346 has:

    value = index.data(Qt::DisplayRole);
    if (value.isValid() && !value.isNull()) {
        option->features |= QStyleOptionViewItem::HasDisplay;
        option->text = displayText(value, option->locale);
    }

So an invalid or null value never calls displayText(). I am guessing this must be your case!

So maybe you need to override QStyledItemDelegate::initStyleOption() with something like

    QStyledItemDelegate::initStyleOption(option, index);    // call base initialisation first
    value = index.data(Qt::DisplayRole);
    if (whatever condition to recognise `NULL` in value/column)
    {
         option->features |= QStyleOptionViewItem::HasDisplay; 
         option->text = "[NULL]";
    }

[deleted385]

@JonB, excellent, that was the trick. With these:

void StyleDelegate::initStyleOption(QStyleOptionViewItem *option, const QModelIndex &index) const
{
    QStyledItemDelegate::initStyleOption(option, index);
    auto value = index.data(Qt::DisplayRole);
    if (value.isNull())
    {
        option->features |= QStyleOptionViewItem::HasDisplay;
        option->text = "NULL";
        option->backgroundBrush = QBrush(Qt::red);
    }
}

it shows NULL with red background. Need to find a way to change the foreground brush instead of background.

Thanks.

[JonB]

@Emon-Haque
Yep, this is good. I did guess that maybe displayText() is simply not called on NULL/no valid value from database, and that is indeed the case. I am surprised I could not find a web question/answer to this effect.

Need to find a way to change the foreground brush instead of background.

The base code has:

    value = index.data(Qt::ForegroundRole);
    if (value.canConvert<QBrush>())
        option->palette.setBrush(QPalette::Text, qvariant_cast<QBrush>(value));

So either override the model's data() method to return Qt::red for Qt::ForegroundRole on your NULL data, or append

        option->palette.setBrush(QPalette::Text, QBrush(Qt::red));

in your case in initStyleOption().

[deleted385]

@JonB, now it's perfect. Thanks once again for the additional answer.