Set a filter on a model/view

Hi,

I have this model:

Code:

---

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();
}

---

Used by this view:

Code:

---

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);
}

---

If i set a filter like this ...

Code:

---

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

---

... 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

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!

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

Try to identify your sql query:

add this:

Code:

---

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

---

after:

Code:

---

modeloCidadao->select();

---

Quote:

---

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:

Code:

---

"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"

---

The not working version:

Code:

---

"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"

---

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 !?

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

modify your filter:

Code:

---

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

---

to:

Code:

---

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

---

Quote:

---

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...

Code:

---

"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"

---

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

OK ... i have nothing better to do ;)

I created a more readable model that goes like this:

Code:

---

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>

---

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:

Code:

---

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

---

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?