Choosing columns for display in an QSqlTableModel



  • Hi,
    I display the data from a database using the following code:

    	model = new QSqlTableModel(this);
        model->setTable ("Items");
        model->select ();
        ui->tableView->setModel (model);
    

    How can I choose which columns to be displayed and how can I display images from the database (stored in a BLOB column in the database)?
    Thank you.


  • Qt Champions 2016

    Hi
    Im not sure you can select columns
    with QSqlTableModel as its mapping whole table.

    Here is example of save & load image to db
    https://wiki.qt.io/How_to_Store_and_Retrieve_Image_on_SQLite



  • @mrjj
    Is it possible to display the image (stored as a BLOB) through tablemodel?
    Thank you


  • Qt Champions 2016

    @mrjj said:

    QSqlTableModel

    If you look at docs there is sample
    QSqlTableModel model;
    model.setTable("employee");
    model.select();
    int salary = model.record(4).value("salary").toInt(); << you would use toByteArray() or something like that.

    You can do the same with your blob to get the field with the blob
    and then use the code from.
    How_to_Store_and_Retrieve_Image_on_SQLite



  • Hi,
    I came up with the following code:

     QSqlQuery query2 ("SELECT Count(*) FROM Items");
        query2.first ();
        row1 = query2.value (0).toInt ();
    
    	 QStandardItemModel *smodel = new QStandardItemModel;
    
    	ui->tableView->setModel (smodel);
        ui->tableView->setColumnHidden (0,true);
    
        for(int i = 4; i < 14; i++)
        {
            ui->tableView->setColumnHidden (i,true);
        }
        row1 = 0;
    
        while(query.next ())
        {
    		QStandardItem *Item1 = new QStandardItem();
            QStandardItem *Item2 = new QStandardItem();
            QStandardItem *Item3 = new QStandardItem();
    
            Item1->setData (NameD = query.value(0).toString (),Qt::DisplayRole);
    
            byteArray = query.value (1).toByteArray ();
            Pixmap.loadFromData (byteArray);
            Pixmap = Pixmap.scaled (100,100,Qt::KeepAspectRatio);
    
            Item2->setData (QVariant(Pixmap),Qt::DecorationRole );
          smodel->setItem (row1++,1,Item2);
            Item3->setData (DescrD = query.value (2).toString (),Qt::DisplayRole);
        }
    
    Now it displays the images, but not the texts. What did I miss? Thank you for your help.
    
    


  • No need to use the QStandardItem here

     for(;query.next ();++row1)
        {
            smodel->setData (smodel->index(row1,0), NameD = query.value(0).toString ());
            Pixmap.loadFromData (query.value (1).toByteArray (););
            smodel->setData (smodel->index(row1,1),Pixmap,Qt::DecorationRole );
            smodel->setData (smodel->index(row1,2), DescrD = query.value (2).toString ());
        }
    


  • @VRonin
    Thank you. I have the following:

    	 QStandardItemModel *smodel = new QStandardItemModel;
    
        row1 = 0;
    
    	for(;query.next (); row1++)
        {
            smodel->setData (smodel->index(row1,0), NameD = query.value(0).toString ());
            qDebug() << "row1: " << row1;
            qDebug() << "NameD: " << NameD;
            Pixmap.loadFromData (query.value (1).toByteArray ());
            smodel->setData (smodel->index(row1,1),Pixmap,Qt::DecorationRole );
            smodel->setData (smodel->index(row1,2), DescrD = query.value (2).toString ());
            qDebug() << "DescrD: " << DescrD;
        }
    
    	ui->tableView->show ();
    

    qDebug() shows that the variables have the right values, but nothing shows in the table. What did I miss?


  • Qt Champions 2016

    Hi
    Maybe
    smodel->setData (xx for DescrD needs Qt::DisplayRole
    like u do for Qt::DecorationRole ?



  • @mrjj
    Unfortunately

            smodel->setData (smodel->index(row1,0), NameD = query.value(0).toString (),Qt::DisplayRole);
    

    did nothing. The table is still empty.


  • Qt Champions 2016

    @gabor53
    and u are 100000% sure that it adds NameD ?
    and its not empty?



  • the code you posted is missing the necessary smodel->insertRows() smodel->inserColumns() are you doing it?



  • @VRonin
    Hi
    I don't have it. Whete is this supposed to go?
    Thank you


  • Lifetime Qt Champion

    Hi,

    Why not just use a QSqlQueryModel and a QStyledItemDelegate ?

    If you want to have specific names for the columns you are selecting then use the SELECT myColumn AS NewName construct.



  • QStandardItemModel *smodel = new QStandardItemModel(this); // maybe give it a parent to avoid memory leaks
    
    for(int row1=0;query.next (); ++row1)
        {
    if(row1==0){
    const QSqlRecord qRec=query.record();
    smodel->insertColumns(0,qRec.count());
    Q_ASSERT(query.size()>=0) // if NO COUNT is set in the database then you have to calculate the size manually
    smodel->inserRows(0,query.size());
    
    // Optional: set headers
    for(int i=0;i<qRec.count();++i)
    smodel->setHeaderData(i,Qt::Horizontal,qRec.fieldName(i));
    }
            smodel->setData (smodel->index(row1,0), NameD = query.value(0).toString ());
            qDebug() << "row1: " << row1;
            qDebug() << "NameD: " << NameD;
            Pixmap.loadFromData (query.value (1).toByteArray ());
            smodel->setData (smodel->index(row1,1),Pixmap,Qt::DecorationRole );
            smodel->setData (smodel->index(row1,2), DescrD = query.value (2).toString ());
            qDebug() << "DescrD: " << DescrD;
        }
    


  • @VRonin
    Thank you very much for the code. Now it shows the column headers, but still no data.
    I added

    ui->tableView->setModel (smodel);
    

    to view it in tableView.Any Idea why no records show?
    Thank you.



  • Looks like rows are still mot inserted, try this:

    for(int row1=0;query.next (); ++row1)
        {
    if(row1==0){
    const QSqlRecord qRec=query.record();
    smodel->insertColumns(0,qRec.count());
    
    // Optional: set headers
    for(int i=0;i<qRec.count();++i)
    smodel->setHeaderData(i,Qt::Horizontal,qRec.fieldName(i));
    }
    
    smodel->inserRow(row1);
            smodel->setData (smodel->index(row1,0), query.value(0).toString ());
            Pixmap.loadFromData (query.value (1).toByteArray ());
            smodel->setData (smodel->index(row1,1),Pixmap,Qt::DecorationRole );
            smodel->setData (smodel->index(row1,2), query.value (2).toString ());
        }
    


  • @VRonin
    Thank you. This one displays data from the database but only one record (1 line). Trying to figure out why.



  • Any idea why this code displays only 1 record from the database?


  • Lifetime Qt Champion

    Check your query and your database content.



  • @SGaist Thank you. I was missing a }.


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.