Advice on implementing user-checkable SQL model with specific field as check state

Hello and merry christmas!

I'd like to display a table of a SQLITE database, which looks like this:
Attachment 9882

The first column in my table view should be "name", and the rows should be user-checkable. Their check state is specified by the values in the "use" column. This column itself however should not be displayed in the table view. The rest of the columns are read-only. These and some other requirements make it obvious for subclassing one of the SQL models, because to my knowledge none of them provide this functionality by default.

While I subclassed some QAbstractTableModels with my own data structure before, I am having trouble getting my head around these SQL models. I think, the QSqlQueryModel would be the best choice, by reimplementing flags(), data() and setData() (for the check state). So far, I wasn't able to properly display the checkstate but not displaying the column "use". In my data() method, I called the base class' data() method and depending on the column and role, I decided what will finally be returned.

Here's an example (not working; I use PyQt, it seems... :) ):

Code:

---

def data(self, index, role=Qt.DisplayRole):
        if not index.isValid():
            return QVariant()
 
        row = index.row()
        col = index.column()
        data = super(SkyObjectModel, self).data(index, role)
 
 
        if role == Qt.CheckStateRole:
            return self.record(row).field('use').value()
        elif role == Qt.DisplayRole:
            return data
        else:
            return QVariant()

---

So, my questions are:
- what model would I subclass (if necessary) ?
- how would I reimplement the data()/setData() method?

I am grateful for a firm push in the right direction.

Best regards,
Andi

I don't know how you are going to use that, but if you are using it with a QTableView or QTreeView, see QHeaderView::hideSection()

Cheers,
_

Hi,
thanks for replying. You are correct, I use that with a QTableView with the first column items as user-checkable. In my case, the first column would be "name" and has a checkbox representing the value in the column "use".

I already came across the hideSection() function, and that would do the job of hiding that "use" column just fine.
But what's really troubling me is the retrieval of the checkstate from my table. And the writing of a new state into the database table, of course. Here are some more details.

I subclassed QSqlQueryModel and set its query to:

Quote:

---

select name, type, x, y, ..., use from skyobjects

---

I put "use" at the end, because if I do hideSection(0) on my table view, the checkboxes are gone, too.

In my model, I want to return an appropriate checkstate, if it is requested for column 0. In any other case, I return the usual data. Here's, how I'm trying to do that:

Code:

---

def data(self, index, role=Qt.DisplayRole):
  row = index.row()
  col = index.column()
  data = super(SkyObjectModel, self).data(index, role)
 
  if role == Qt.CheckStateRole and col == 0:
    usage = self.record(row).value('use')
    # would return Qt.Checked/Qt.Unchecked based on usage,
    # but the QVariant returned is always invalid for all fields in the record!
  elif role == Qt.DisplayRole:
    return data
  else:
    return QVariant()

---

Any idea, why the QSqlQueryModel::record(int) returns a record with all fields being invalid? I got a feeling, that record() shouldn't be called there...


Andi

You are right, hiding the first column will do you no good since you want the columns data, but as a checkbox instead of text.

I think what you need is something like this (not sure I get the Python syntax right :))

Code:

---

if col == 0:
  if role == Qt.CheckStateRole:
    return data.toBool()
  else:
    return QVariant()
else:
  return data

---

I.e.: if it is the first collumn, only answer for CheckStateRole and use the value in data.
For all other columns answer what the base class would have answered.

Cheers,
_

That looks promising and would work -- if the QSqlQueryModel::data() would actually return data for the Qt::CheckStateRole.
As it turns out, QSqlQueryModel returns an invalid QVariant, if the provided role is not DisplayRole or EditRole.

So, I just call data() from QSqlQueryModel, but when I need the CheckStateRole, I call data() again with DisplayRole, but with a modified index to look at another column where the checkstate actually is. That way, I can put the "name" column at the very left and give it the checkbox, while having the "use" column at the very right. There, I can hide it by returning the base class' columnCount() minus 1 (or use hideSection() from the headerView).

Code:

---

def data(self, index, role=Qt.DisplayRole):
    if not index.isValid():
        return QVariant()
 
    row = index.row()
    col = index.column()
    data = super(SkyObjectModel, self).data(index, role)
 
    if role == Qt.CheckStateRole and col == 0:
        idx = self.createIndex(row, 8)
        use,_ = super(SkyObjectModel, self).data(idx, Qt.DisplayRole).toInt()
        return Qt.Checked if use == 1 else Qt.Unchecked
    else:
        return data

---

The result is what I had in mind: checkstate, but not "use" column:
Attachment 9883

With that problem out of the way, setting new checkstates should not be too difficult...


Thanks for your help, anda.

Quote:

---

Originally Posted by antimatter

That looks promising and would work -- if the QSqlQueryModel::data() would actually return data for the Qt::CheckStateRole.

---

Right, of course. You need to call the super class data() with a different role, e.g. DisplayRole or, maybe even better, EditRole.

Quote:

---

Originally Posted by antimatter

So, I just call data() from QSqlQueryModel, but when I need the CheckStateRole, I call data() again with DisplayRole, but with a modified index to look at another column where the checkstate actually is.

---

Ah. Your original posting's screenshot had it in the first column.

Cheers,
_