How to get data from sqlquery that result 3 columns in a row and show only 1 Colum and use the other columns data



  • hello all
    im using QSqlQueryModel subclass to excute sql querys and popolate them to tableview
    now i have simple sql query that results 3 coulms / fields in arow that invoked in the QSqlQueryModel subclas like this :
    @

    QString query_local = "SELECT foo1,foo2,foo3 FROM tbl" ;
    QSqlQueryModel::setQuery(query_local, queryDB);
    if (lastError().type() != QSqlError::NoError)
    {
    QString err = lastError().text();
    }
    @

    now later i set this to show me only foo3 in the table view that contains 1 column

    @QSqlQueryModel::removeColumns(0,2);@

    what i need is that the data from foo1 and foo2 and set in the the UserRole of each row in the tableview ( i have itemDelegate of the tableView ) and show in the display only foo3
    how can it be done ?



  • Create your own model. Subclass QSqlQueryModel and reimp QSqlQueryModel::data(). If role is Qt::MyMagicFoo1 then use e.g. @foo1Idx = index.sibling(index.row(), 0);
    // data of foo1
    index.data(Qt::DisplayRole);@

    EDIT: where 0 is the column of foo1 sql position.



  • hi
    Thanks for the replay but i didnt understand it .
    in the data subclass method i did :
    @ QVariant value;
    int c = index.column();
    int r = index.row();

    value = QSqlQueryModel::data(index, role);
    QString s = value.toString();
    if(role == Qt::DisplayRole)
    {
    value = QSqlQueryModel::data(index, role);
    QString s = value.toString();
    }
    if(c ==2)
    {
    return value;
    }
    value ="";
    return value;@

    but it still show me in the result 3 columns , and only in the third the display
    i dont what to display 3 columns only one



  • The way I see it, you have two options:

    Just hide the columns you don't want to see in the view. [[doc:QTableView]] supports the hideColumn(int column) method for that.

    Use a [[doc:QSortFilterProxyModel]] to hide the columns you don't want, by subclassing that class and reimplementing the filterAcceptsColumn method to return false for the columns you want to hide.



  • hi and thanks for the replay i tryed to use hideColumn but it didnt work

    @m_pPlayListMiniItemDelegate =new PlayListMiniItemDelegate(this);
    ui->PlayListMini_tableView->setSelectionBehavior(QAbstractItemView::SelectRows);
    ui->PlayListMini_tableView->setSelectionMode(QAbstractItemView::SingleSelection);
    ui->PlayListMini_tableView->setItemDelegate(m_pPlayListMiniItemDelegate);
    ui->PlayListMini_tableView->setAlternatingRowColors(true);
    ui->PlayListMini_tableView->setModel(PlayListMiniSqlModel::instance());
    ui->PlayListMini_tableView->hideColumn(0);
    ui->PlayListMini_tableView->hideColumn(1);@

    i want to avoid to add yet another model to the stack



  • You mean that even after calling hideColumn, it is still visible in your view?



  • yes , but i implementing the QSortFilterProxyModel now i hope it will work finally ...



  • Well, that would be a bug in Qt. If you could create a minimal simple, self-contained example that has this behaviour, then please share that.



  • will do , it will take time to build complex GUI as sample . but i will



  • Please keep it as simple as possible when creating a sample. Less is more in these cases!



  • sure , by the way the filter proxy worked . thanks!



  • Hi but now i have another problem , in the proxy model
    i set to filter the 2 first columns like this :
    @bool PlayListMiniSortFilterProxyModel::filterAcceptsColumn(int source_column, const QModelIndex& index) const
    {
    //qDebug() << "filterAcceptsColumn(): column = " << source_column ;
    if(source_column ==2)
    return QSortFilterProxyModel::filterAcceptsColumn(source_column,index);
    return false;
    }@

    and in the PlayListMiniSqlModel ( its the QSqlQueryModel im using in the table)
    when i try to get the display string of the hidden 2 columns i only get the 3 column .
    @QVariant PlayListMiniSqlModel::data(const QModelIndex &index, int role) const
    {

    QVariant value;

    int c = index.column(); //HERE IT ONLY SHOW ME 2 , NEVER 0 and 1
    int r = index.row();

    value = QSqlQueryModel::data(index, role);
    QString s = value.toString();
    if(role == Qt::DisplayRole)
    {
    value = QSqlQueryModel::data(index, role);
    QString s = value.toString();
    }

    return value;
    }@

    where in the model i can get the hidden column data ?



  • im using QSortFilterProxyModel to filter column that im getting from QSqlQueryModel model but becose the filterAcceptsColumn method is const "all the way" i have problem to set Qt::UserRole data in the right index . how can i overcome this?

    @bool MiniSortFilterProxyModel::filterAcceptsColumn(int source_column, const QModelIndex& index) const
    {
    QVariant tmp ;
    if(source_column ==0)
    {

            setRowid(index.data(Qt::DisplayRole)); 
    

    // here im getting compilation error
    //: error C2678: binary '=' : no operator found which takes a left-hand operand of type 'const QString' (or there is no acceptable conversion)
    m_rowId = index.model()->data(index,Qt::DisplayRole).toString();
    return false;
    }
    else if(source_column ==1)
    {
    setYTid(index.data(Qt::DisplayRole));
    return false;
    }
    else if(source_column ==2)
    {

            setNewData(index);
            return QSortFilterProxyModel::filterAcceptsColumn(source_column,index);
        }
        return false;
    }
    
    
    void MiniSortFilterProxyModel::setRowid(QVariant rowId) const 
    {
    

    // here also compilation error:
    m_rowId = rowId.toString();
    }

    void MiniSortFilterProxyModel::setYTid(QVariant ytId) const 
    {
    

    / here also compilation error:
    m_ytId = ytId.toString();
    }

    void MiniSortFilterProxyModel::setNewData(QModelIndex& index) const 
    {
    

    // here also compilation error:
    //error C2511: 'void MiniSortFilterProxyModel::setNewData(QModelIndex &) const' : //overloaded member function not found in 'MiniSortFilterProxyModel'
    QVariant data = m_rowId+"_"+m_ytId;
    index.model()->setData(index,data,Qt::UserRole);
    }
    @



  • [quote author="umen242" date="1333355148"]Hi but now i have another problem , in the proxy model
    i set to filter the 2 first columns like this :
    @bool PlayListMiniSortFilterProxyModel::filterAcceptsColumn(int source_column, const QModelIndex& index) const
    {
    //qDebug() << "filterAcceptsColumn(): column = " << source_column ;
    if(source_column ==2)
    return QSortFilterProxyModel::filterAcceptsColumn(source_column,index);
    return false;
    }@

    and in the PlayListMiniSqlModel ( its the QSqlQueryModel im using in the table)
    when i try to get the display string of the hidden 2 columns i only get the 3 column .
    @QVariant PlayListMiniSqlModel::data(const QModelIndex &index, int role) const
    {

    QVariant value;

    int c = index.column(); //HERE IT ONLY SHOW ME 2 , NEVER 0 and 1
    int r = index.row();

    value = QSqlQueryModel::data(index, role);
    QString s = value.toString();
    if(role == Qt::DisplayRole)
    {
    value = QSqlQueryModel::data(index, role);
    QString s = value.toString();
    }

    return value;
    }@

    where in the model i can get the hidden column data ?[/quote]

    That sounds logical to me. The data() method will be called by the view or proxy model on top of the model. That object will (as much as possible) only query for the items it needs. In this case, that would be column 2, not column 0 or 1, as your proxy hides these. Why do you expect this method to be called for all cells, even the ones you're not showing?



  • [quote author="umen242" date="1333364703"]im using QSortFilterProxyModel to filter column that im getting from QSqlQueryModel model but becose the filterAcceptsColumn method is const "all the way" i have problem to set Qt::UserRole data in the right index . how can i overcome this?
    [/quote]
    I fail to understand why you'd need to modify the model from your filterAcceptsColumn() implementation.



  • very simple , sorry im not native English speaker so the semantics are wrong (most of the time..).
    what im trying to do is very simple ( very simple in the logic of it )

    1. run sql query that returns 3 columns in each row ("select foo1,foo2,foo3 from tbl")
    2. use the data from column 1 and 2 ( concat the 2 strings )
    3. set it to column 3 Qt::UserRole placeholder ( i need it for later use ) that in the end becomes the only row that is displayed
      4 this is for each row / record that returns from the sql query


  • OK. You don't need to modify the model at all for that. In either your base model or the proxy model, I would just reimplement the data method to return the right value for your Qt::UserRole for the column you're after. The filterAcceptsColumn method has nothing to do with that.



  • well i have reimplement the data method in the QSqlQueryModel
    it looks like this :
    @QVariant MiniSqlModel::data(const QModelIndex &index, int role) const
    {
    QVariant value;
    int c = index.column(); //HERE IT IS ALWAYS == 2
    int r = index.row();
    value = QSqlQueryModel::data(index, role);
    QString s = value.toString();
    if(role == Qt::DisplayRole)
    {
    value = QSqlQueryModel::data(index, role);
    QString s = value.toString();
    }

    return value;
    }@

    the problem is that im getting only the last column (2) and i dont have the data of the other 2 .
    the only place i managed to catch the data of the first 2 columns (0,1) is in the filterAcceptsColumn method of the proxy .

    i missing here something small .. i feel i realy close to solve this . ( already 5 days .. )



  • Of course! Your proxy model only has one column! That's what you wanted to achieve, after all...
    Your source model will still have all three columns, so you can still query these from the data method of proxy model.

    Edit:
    I would do something like this:
    @
    MyProxyModel::data(const QModelIndex& index, int role) const
    {
    if (!sourceModel())
    return QVariant(); //we don't do useful things without a source model

    if (index.column() == 0 && role == Qt::UserRole) {
    //get the merged data from the underlying model
    QModelIndex sourceIndex = mapToSource(index);
    if (!sourceIndex.isValid())
    return QVariant(); //you never know, right?

    QModelIndex col1Index = sourceModel()->index(sourceIndex.row(), 0, sourceIndex.parent());
    QString column1 = col1Index.data(Qt::DisplayRole).toString();
    QModelIndex col2Index = sourceModel()->index(sourceIndex.row(), 1, sourceIndex.parent());
    QString column2 = col1Index.data(Qt::DisplayRole).toString();
    
    QString result = column1 + " - " + column2;
    return QVariant(result);
    

    }
    return QSortFilterProxyModel(index, role);
    }
    @

    Note: the above is untested, just typed into the editor directly.



  • no its the opposite, the data function is in the QSqlQueryModel.
    i dont understand this :
    @"...so you can still query these from the data method of proxy model...."@

    how ? where ?

    do you mean i need to implement the data method of the proxy ?



  • [quote author="umen242" date="1333367911"]no its the opposite, the data function is in the QSqlQueryModel.
    i dont understand this :
    @"...so you can still query these from the data method of proxy model...."@

    how ? where ?

    do you mean i need to implement the data method of the proxy ?[/quote]
    See the edit to my post above for an example.



  • Thanks very much i will try it now



  • Note that this example does not try to modify any data. It just changes whatever it tells the user of the model is the data in it. Also note that I did not implement setData(). If you need to support setData too (for this role), things will quickly become more complicated...



  • hi i tryed you example , and there is 2 problems and that i dont understand
    first one is that when it is:
    @if (index.column() == 0 && role == Qt::UserRole) {@
    it never enter the if , but when i change it to
    @if (c == 0 && role == Qt::DisplayRole) {@

    it does Enter , but as expected the joined string is displayed in the row displayRole
    how can i put it in the UserRole?



  • Of course it is not entered, it is only entered when the UserRole is actually requested. That is how it ends up "in" the display role. The role is only the argument you pass to the data() function. Nothing more, nothing less. The normal delegates don't query for the UserRole (they only need the standard roles).



  • ok so how do i trigger it to be requested ? so i finally could set it



  • You simply request the data manually, where you need it. I assume you set this dat to the userRole for a reason, right? And that reason is presumably that you need this bit of information somewhere. So, how about actually requesting the data where you need it? It would be as simple as getting the right QModelIndex (that is: an index pointing to your proxy model; your view may supply it), and calling data(Qt::UserRole) on it.



  • finally !
    this wasn't trivial at all man . its working thanks to you .
    thanks!


Log in to reply
 

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