Set a filter on a model/view

[graciano]

Hi,

I have this model:

void CidadaoDialog::construirModeloCidadao()
{
    modeloCidadao->setTable("Cidadao");
    modeloCidadao->setRelation(cidadao_localidade, QSqlRelation("Localidade", "idLocalidade", "nome"));
    modeloCidadao->setHeaderData(cidadao_idCidadao, Qt::Horizontal, tr("B.I."));
    modeloCidadao->setHeaderData(cidadao_nome, Qt::Horizontal, trUtf8("Nome"));
    modeloCidadao->setHeaderData(cidadao_localidade, Qt::Horizontal, trUtf8("Localidade"));
    modeloCidadao->setEditStrategy(QSqlRelationalTableModel::OnRowChange);
    modeloCidadao->setSort(cidadao_nome, Qt::AscendingOrder);
    modeloCidadao->select();
}

void CidadaoDialog::construirModeloCidadao()
{
    modeloCidadao->setTable("Cidadao");
    modeloCidadao->setRelation(cidadao_localidade, QSqlRelation("Localidade", "idLocalidade", "nome"));
    modeloCidadao->setHeaderData(cidadao_idCidadao, Qt::Horizontal, tr("B.I."));
    modeloCidadao->setHeaderData(cidadao_nome, Qt::Horizontal, trUtf8("Nome"));
    modeloCidadao->setHeaderData(cidadao_localidade, Qt::Horizontal, trUtf8("Localidade"));
    modeloCidadao->setEditStrategy(QSqlRelationalTableModel::OnRowChange);
    modeloCidadao->setSort(cidadao_nome, Qt::AscendingOrder);
    modeloCidadao->select();
}

To copy to clipboard, switch view to plain text mode 

Used by this view:

void CidadaoDialog::construirVistaCidadao()
{
    vistaCidadao = ui->tableView;
    vistaCidadao->setModel(modeloCidadao);
    vistaCidadao->setItemDelegate(new QSqlRelationalDelegate(vistaCidadao));
    vistaCidadao->setSelectionMode(QAbstractItemView::SingleSelection);
    vistaCidadao->setSelectionBehavior(QAbstractItemView::SelectRows);
    vistaCidadao->setFixedWidth(605);
    vistaCidadao->verticalHeader()->hide();
    vistaCidadao->setColumnWidth(cidadao_idCidadao, 100);
    vistaCidadao->setColumnWidth(cidadao_nome, 300);
    vistaCidadao->setColumnWidth(cidadao_localidade, 200);
}

void CidadaoDialog::construirVistaCidadao()
{
    vistaCidadao = ui->tableView;
    vistaCidadao->setModel(modeloCidadao);
    vistaCidadao->setItemDelegate(new QSqlRelationalDelegate(vistaCidadao));
    vistaCidadao->setSelectionMode(QAbstractItemView::SingleSelection);
    vistaCidadao->setSelectionBehavior(QAbstractItemView::SelectRows);
    vistaCidadao->setFixedWidth(605);
    vistaCidadao->verticalHeader()->hide();
    vistaCidadao->setColumnWidth(cidadao_idCidadao, 100);
    vistaCidadao->setColumnWidth(cidadao_nome, 300);
    vistaCidadao->setColumnWidth(cidadao_localidade, 200);
}

To copy to clipboard, switch view to plain text mode 

If i set a filter like this ...

void CidadaoDialog::filtrar_nome()
{
    QString filtro;
    filtro = "nome like '%"+ui->filtroNome->text()+"%'";
    modeloCidadao->setFilter(filtro);
    modeloCidadao->select();
}

void CidadaoDialog::filtrar_nome()
{
    QString filtro;
    filtro = "nome like '%"+ui->filtroNome->text()+"%'";
    modeloCidadao->setFilter(filtro);
    modeloCidadao->select();
}

To copy to clipboard, switch view to plain text mode 

... i get no records displyed on the view.

If i comment line 4 on the model code it works as expected, displaying all record tha match the texte entered on ui->filtroNome (lineEdit Widget).

What am i missing here?
Thanks

[graciano]

Well!
Looks like i had two fields with the same name in the original table, as well as in the referenced table.
I thinks this showld not be a problem ... but it is!

[saa7_go]

So, is your cidadao_localidade value is correct? Looking at your diagram it should be 2.

Try to identify your sql query:

add this:

qDebug() << modeloCidadao->query().lastQuery();

qDebug() << modeloCidadao->query().lastQuery();

To copy to clipboard, switch view to plain text mode 

after:

modeloCidadao->select();

modeloCidadao->select();

To copy to clipboard, switch view to plain text mode 

[graciano]

So, is your cidadao_localidade value is correct? Looking at your diagram it should be 2.

Yes.
If filtering names containing "P" ...
The last Query, in the working version gives me:

"SELECT Cidadao.`idCidadao`, Cidadao.`nome`, relTblAl_2.nomeLocalidade FROM Cidadao, Localidade relTblAl_2 WHERE (Cidadao.`localidade` = relTblAl_2.idLocalidade) AND (nome like '%P%')  ORDER BY Cidadao.`nome` ASC"

"SELECT Cidadao.`idCidadao`, Cidadao.`nome`, relTblAl_2.nomeLocalidade FROM Cidadao, Localidade relTblAl_2 WHERE (Cidadao.`localidade` = relTblAl_2.idLocalidade) AND (nome like '%P%')  ORDER BY Cidadao.`nome` ASC"

To copy to clipboard, switch view to plain text mode 

The not working version:

"SELECT Cidadao.`idCidadao`, Cidadao.`nome`, relTblAl_2.nome AS Localidade_nome_2 FROM Cidadao, Localidade relTblAl_2 WHERE (Cidadao.`localidade` = relTblAl_2.idLocalidade) AND (nome like '%P%')  ORDER BY Cidadao.`nome` ASC"

"SELECT Cidadao.`idCidadao`, Cidadao.`nome`, relTblAl_2.nome AS Localidade_nome_2 FROM Cidadao, Localidade relTblAl_2 WHERE (Cidadao.`localidade` = relTblAl_2.idLocalidade) AND (nome like '%P%')  ORDER BY Cidadao.`nome` ASC"

To copy to clipboard, switch view to plain text mode 

Aparently an alias is created when the duplicate name is detected (relTblAl_2.nome AS Localidade_nome_2) but, once again, it showld not be a reason not to work as expected ... i think !?

[saa7_go]

maybe you need to be more specific when setting a filter in QSqlRelationalTableModel.

modify your filter:

filtro = "nome like '%"+ui->filtroNome->text()+"%'";

filtro = "nome like '%"+ui->filtroNome->text()+"%'";

To copy to clipboard, switch view to plain text mode 

to:

filtro = QString("%1.nome like '\%%2\%'").arg(modeloCidadao->tableName()).arg(ui->filtroNome->text());

filtro = QString("%1.nome like '\%%2\%'").arg(modeloCidadao->tableName()).arg(ui->filtroNome->text());

To copy to clipboard, switch view to plain text mode 

[graciano]

maybe you need to be more specific when setting a filter in QSqlRelationalTableModel.

You are right about this.
From now on i will allways use TableName.FieldName.
This way all ambiguity will desapear.

Anyway ... QSqlRelationalTableModel class showld be able to deal with this because, looking at the query generated by the model...

"SELECT Cidadao.`idCidadao`, Cidadao.`nome`, relTblAl_2.nome AS Localidade_nome_2 FROM Cidadao, Localidade relTblAl_2 WHERE (Cidadao.`localidade` = relTblAl_2.idLocalidade) AND (nome like '%p%')  ORDER BY Cidadao.`nome` ASC"

"SELECT Cidadao.`idCidadao`, Cidadao.`nome`, relTblAl_2.nome AS Localidade_nome_2 FROM Cidadao, Localidade relTblAl_2 WHERE (Cidadao.`localidade` = relTblAl_2.idLocalidade) AND (nome like '%p%')  ORDER BY Cidadao.`nome` ASC"

To copy to clipboard, switch view to plain text mode 

... i realise it's performing some kind of join, without considering the possibility of fiels with the same name in the related tables.

[graciano]

OK ... i have nothing better to do

I created a more readable model that goes like this:

mysql> describe TableA;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| field01 | int(11)     | NO   | PRI | 0       |       | 
| field02 | varchar(45) | NO   |     | NULL    |       | 
| field03 | int(11)     | NO   | MUL | NULL    |       | 
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
 
mysql> describe TableB;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| field01 | int(11)     | NO   | PRI | NULL    |       | 
| field02 | varchar(45) | NO   |     | NULL    |       | 
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
 
mysql> select TableA.field01, TableA.field02, TableB.field02 from TableA, TableB where TableA.field03 = TableB.field01 and field02 like "%p%";
 ERROR 1052 (23000): Column 'field02' in where clause is ambiguous
mysql> select TableA.field01, TableA.field02, TableB.field02 from TableA, TableB where TableA.field03 = TableB.field01 and TableA.field02 like "%p%"; 
+---------+---------+---------+
| field01 | field02 | field02 |
+---------+---------+---------+
|       2 | pedro   | ?       | 
+---------+---------+---------+
1 row in set (0.00 sec)
 
mysql>

mysql> describe TableA;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| field01 | int(11)     | NO   | PRI | 0       |       | 
| field02 | varchar(45) | NO   |     | NULL    |       | 
| field03 | int(11)     | NO   | MUL | NULL    |       | 
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> describe TableB;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| field01 | int(11)     | NO   | PRI | NULL    |       | 
| field02 | varchar(45) | NO   |     | NULL    |       | 
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select TableA.field01, TableA.field02, TableB.field02 from TableA, TableB where TableA.field03 = TableB.field01 and field02 like "%p%";
 ERROR 1052 (23000): Column 'field02' in where clause is ambiguous
mysql> select TableA.field01, TableA.field02, TableB.field02 from TableA, TableB where TableA.field03 = TableB.field01 and TableA.field02 like "%p%"; 
+---------+---------+---------+
| field01 | field02 | field02 |
+---------+---------+---------+
|       2 | pedro   | ?       | 
+---------+---------+---------+
1 row in set (0.00 sec)

mysql>

To copy to clipboard, switch view to plain text mode 

As said above, the use of the Tablename eliminates the "ambiguous" caused error.
Using this database with my qt example, the generates query when filtering is:

SELECT TableA.`field01`, TableA.`field02`, relTblAl_2.field02 AS TableB_field02_2 FROM TableA, TableB relTblAl_2 WHERE (TableA.`field03` = relTblAl_2.field01) AND (TableA.field02 like '%p%')  ORDER BY TableA.`field01` ASC

SELECT TableA.`field01`, TableA.`field02`, relTblAl_2.field02 AS TableB_field02_2 FROM TableA, TableB relTblAl_2 WHERE (TableA.`field03` = relTblAl_2.field01) AND (TableA.field02 like '%p%')  ORDER BY TableA.`field01` ASC

To copy to clipboard, switch view to plain text mode 

So ... what is this relTblAl_2 doing in the query?
It is used in the Qt part of the Query ... but the the programmer is allowed to enter an ambiguous fieldname?!
Showld't this be fixed?