QSortFilterProxyModel - filtering for NULL values

hsfougaris

I am having trouble filtering a QSqlTableModel with a QSortFilterProxyModel.
I am looking for columns where a particular field is null.

What I have so far is this:
@
QSqlQuery qry;
qry.exec("CREATE TABLE dataTbl (id INT NOT NULL, descr VARCHAR(100) NOT NULL, hlpID INT NULL)");
qry.exec("INSERT INTO dataTbl (id, descr, hlpID) VALUES (1, 'descr1', 1)");
qry.exec("INSERT INTO dataTbl (id, descr, hlpID) VALUES (2, 'descr2', 2)");
qry.exec("INSERT INTO dataTbl (id, descr, hlpID) VALUES (3, 'descr3', 3)");
qry.exec("INSERT INTO dataTbl (id, descr, hlpID) VALUES (4, 'descr4', NULL)");
qry.exec("INSERT INTO dataTbl (id, descr, hlpID) VALUES (5, 'descr5', NULL)");

QSqlTableModel tMdl;
tMdl.setTable("dataTbl");
tMdl.select();

QSortFilterProxyModel fMdl;
fMdl.setSourceModel(&tMdl);
qDebug() << "Model contents (unfiltered): " << fMdl.rowCount();
fMdl.setFilterKeyColumn( tMdl.fieldIndex("hlpID"));
fMdl.setFilterRegExp(QRegExp("^$", Qt::CaseInsensitive, QRegExp::RegExp));
qDebug() << "Model contents (filtered ^$): " << fMdl.rowCount();

@

I've been trying to do it with a reg expr of ^$, meaning blank for me.
However the proxy model seems to do some sort of conversion and all null integer fields, become 0.
If I use a reg.expr of ^0$, I get the 2 rows that I should be getting, but that of course is a non-solution as I might actually have a 0 there.

The only way it works correctly is when working against text fields (it also when using a SQLITE data source, which doesn't really have types and everything is strings as far as I know).

I've been trying it against a PostgreSQL database.
Is there something I can do here?

Thanks.

andre

Nulls are always a bit of a problem. IMHO, the Qt SQL framework should return them as invalid QVariants, but that doesn't always seem to be the case.
A workaround would be to do the filtering on the query level. At that level, SQL provides you the means to do the filtering. That would mean using a QSqlQueryModel instead of a QSqlTableModel.

hsfougaris

In my case I can't do that, as I'm doing some complicated table processing, and I need the QSqlTableModel to cache everything until I finish, and then submit.
I guess from your reply that even a QSortFilterProxyModel subclass won't be able to handle this properly (if the framework is not returning the data properly), but I think I'll give that a try if there is no other way...

hsfougaris

Andre, do you receive emails sent through your profile?
I sent you an e-mail a couple of days ago, and although it's nothing urgent, I'm not sure you actually received it...

andre

I do, yes, but sometimes they end up under a heap of other mails. You are refering to the post on ODBC, right? I did read it, and even did some basic research into it, but I don't have a clear answer (yet). Might I recommend that you post it here instead? More eyes would be able to look at it that way, and discussing it out in the open would benefit everyone (also in the future), not just you. Is there some reason you can't do that in this case?

hsfougaris

Because it's a very specific problem, I wasn't sure if I should post it...
I'll start a new discussion with it then - thanks.

andre

[quote author="harry" date="1302374293"]In my case I can't do that, as I'm doing some complicated table processing, and I need the QSqlTableModel to cache everything until I finish, and then submit.
[/quote]
Pitty...

[quote]I guess from your reply that even a QSortFilterProxyModel subclass won't be able to handle this properly (if the framework is not returning the data properly), but I think I'll give that a try if there is no other way...[/quote]
Well, your problem may be at another level actually. Even if the framework would properly return an invalid QVariant for the data and edit role (you can test if it does in your case), a QRegExp would not solve your problem. A regexp works on text, and as you say yourself, an empty text is conceptually different from no text at all. But a regexp would not be able to distinguish the two, and in fact, if you force an invalid QVariant to a QString, you get an empty string. So, you would have to do the matching on invalid QVariants yourself anyway, and not rely on a QRegexp. You might still try if that works for you.

hsfougaris

Ok, I got something, just not as elegant as it should be.
If I use q QSortProxyFilterModel subclass and override only the filterAcceptsRow function with something like :
@
bool myQSFPM::filterAcceptsRow ( int source_row, const QModelIndex & source_parent ) const {
Q_UNUSED(source_parent);
QSqlQueryModel m = static_cast<QSqlQueryModel>(sourceModel());
bool r = m->record(source_row).isNull("hlpid");
return !r;
}
@
it works ok for me.

The ugly bit: if I try to use the QVariant returned by data() [in the above example sourceModel()->data( sourceModel()->index( source_row, myColNo ))], both the Qt::EditRole and Qt::DisplayRole are 0 for NULL fields. Very unexpected behavior...

Thankfully, at least the isNull function of QSqlRecord returns the correct value.
The of course limits the usefulness of this subclass to models that are or inherit from QSqlQueryModel (as a cast is needed), but I think it can prove to be very useful (provided of course a function is added where you can set which columns should not be null).

hsfougaris

I updated my QSFPM sublass in the wiki http://developer.qt.nokia.com/wiki/QSortFilterProxyModel_subclass_for_readonly_columns_columns_with_checkboxes_and_password_columns to handle this case in a more generic way, so everything is in one place, in case it help someone else

andre

Thanks for letting us know of your results, and sharing your solution.