[PyQt] Selection query in relational table model

[Jorge Tornero - Listas]

Hello,

I've loooked CAMELOT and SQLalchemy and I think I'll give a try in the 
future but by now i'll tryi to implement sort of my own relational table 
model, because sqlalchemy et al. need to be learned and there is no time 
for it right now.

I'm so surprised because it looks like is a basic feature (to be able to 
set custom queries to the relational table model) but I'm sure there are 
good reasons for this.

Anyway, thank you very much for your ANSWERS (not responses, sorry...)

Jorge Tornero

El 30/10/12 11:09, tw55413 at gmail.com escribió:
> you can take a look at
> Www.python-camelot.com
> On 30/10/12 10:02 Jorge Tornero - Listas wrote:
> 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
>
> _______________________________________________
> PyQt mailing list    PyQt at riverbankcomputing.com
> http://www.riverbankcomputing.com/mailman/listinfo/pyqt
>