Solved How to show DB NULL in QTableView
-
In
QtWidget
app, If I do this:model = new QSqlQueryModel(this); model->setQuery(query); table->setModel(model);
it doesn't show
NULL
in theQTableView
column where the the value isNULL
in the database. -
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 likeQStyledItemDelegate::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]"; }
-
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.
-
@ChrisW67, in database the last two columns are
NULL
but In my app those are blank like this:
In the subclass ofQStyledItemDelegate
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. -
@Emon-Haque
First try!value.isValid()
instead in case that works.Otherwise debug/put in
qDebug()
statements to see either whatdisplayText()
is called with when the database value isNULL
or that it is not called at all forNULL
, since Qt considers there is "nothing there" to display, in which case report that back and we will reconsider what you need to do forNULL
.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 isNULL
. Right? So far as it is concerned there is "nothing there to display". I will have a think about that.... -
@Emon-Haque
I am not finding any questions/answers as to what to do aboutQStyledItemDelegate::displayText()
apparently not being called when database value isNULL
. 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 overridedata()
method to discover just what kind ofQVariant
is being stored/returned forNULL
. - All I can think of is interpose some kind of
QIdentityProxyModel
betweenQTableView
andQSqlQueryModel
, where you override itsdata()
method to recogniseNULL
and return"NULL"
for the table view's purpose. Until someone shows you something better....
-
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 likeQStyledItemDelegate::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]"; }
-
@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.
-
@Emon-Haque
Yep, this is good. I did guess that maybedisplayText()
is simply not called onNULL
/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 returnQt::red
forQt::ForegroundRole
on yourNULL
data, or appendoption->palette.setBrush(QPalette::Text, QBrush(Qt::red));
in your case in
initStyleOption()
. -
@JonB, now it's perfect. Thanks once again for the additional answer.