[PyQt] Selection query in relational table model

[Jorge Tornero - Listas]

Hello,

I am trying to develop a small application where a database (PostgreSQL) 
is involved.

I've created a custom table view which several custom delegates that 
works well for me. The table receives a QSqlRelationalTable Model and is 
able to remove records and edit them with a popup dialog which shows 
line editors and/or combo selectors depending on the underlying database 
field type. I had to implement this because my database tables have 
primary keys than contains relations to another tables, thus direct 
editing is not possible. I am using QSqlRelationalTableModel just for 
foreign key resolution, that is, mainly for display purposes.

But now I want to to use two of that table view together. Both table 
views will use the same table of the database, say we have a table like:

survey PK
haul PK
species PK (FK with another table species, for instance)
length_class
number
total weight

say we have several  records in the table:

    survey    |haul|species|length|number|total_weight
-------------+----+-------+------+------+-------------
BOCADEVA0711 |  1 | 10156 | 10.5 |  21  |    56.56
BOCADEVA0711 |  1 | 10156 | 11.0 |  33  |    56.56
BOCADEVA0711 |  1 | 10156 | 11.5 |  11  |    56.56
BOCADEVA0711 |  1 | 10152 | 21.5 |  99  |   123.46
BOCADEVA0711 |  1 | 10152 | 22.0 |  10  |   123.46
BOCADEVA0711 |  1 | 10152 | 22.5 |   9  |   123.46
BOCADEVA0711 |  1 | 10288 | 10.5 |  18  |    60.44
BOCADEVA0711 |  1 | 10288 | 11.5 |  33  |   100.01

With my custom view there is no problem when I show the data (even if i 
need to hide columns, etc.)

But the problem comes when the first view should show aggregated data, 
for instance just survey, haul, species and total weight, like

    survey    |haul|species|total_weight
-------------+----+-------+-------------
BOCADEVA0711 |  1 | 10156 |    56.56
BOCADEVA0711 |  1 | 10152 |   123.46
BOCADEVA0711 |  1 | 10288 |    60.44

I will obtain this results in sql with just:

SELECT DISTINCT survey,haul,species,total_weigth FROM the_table;
or
SELECT survey,haul,species,total_weigth FROM the_table GROUP BY 1,2,3,4;

Unfortunately I have not been succesful trying to do tha with 
QSqlRelationalTableModel. I don't know how to set the model's query. 
I've tried with setQuery() (I am aware of the documentarion advice about 
don't use setQuery, but I haven¡t another choice AFAIK) but two things 
happen:

- I get foreign keys unresolved
- View resets and the set query is sort of "forgotten" and I get the 
whole table again.

Do you have any suggestions? Saint Google hasn't offered much help so far.

Thank you very much for your responses.

Best regards,

Jorge Tornero
Instituto Español de Oceanografía
Centro Oceanográfico de Cádiz

www.ieo.es