Qt-interest Archive, March 2008
QSqlRelationalTableModel Filter
Message 1 in thread
I need to filter the select of a QSqlRealtionalTableModel based on one
of the relation columns. For example, say I have the example table
from http://doc.trolltech.com/4.3/qsqlrelationaltablemodel.html set
up, but now I want to filter the table to only select records where
"City" is equal to "Oslo". I would assume I would do this by using the
QSqlRelationalTableModel.setFilter(); command, but how do I format the
filter argument? I tried just using the where clause from my select
statement without the where, but this didn't work-details below.
If I was executing the SELECT statement myself directly on the
database, I would use a select statement something roughly like the
following (forgive any syntaxtal errors, as I don't have this specific
database to try on):
SELECT employee.id, employee.name, city.name AS City, country.name AS
Country from employee INNER JOIN city ON employee.city=city.id INNER
JOIN country ON employee.country=country.id WHERE city.name='Oslo'
and this works perfectly (substituting the proper table and column
names for my database). According to the setFilter() documentation:
"The filter is a SQL WHERE clause without the keyword WHERE"
so I would assume I need to do
relationalTableModel.setFilter("city.name=\'Oslo\'"); or the like,
assuming I got the proper escaping of the quotes and everything.
However, when I try that, I get an error stating:
ERROR: invalid reference to FROM-clause entry for table "locationlist"
LINE 1: ...ment"="relTblAl_4"."id") AND (inactive=false AND
(locationli...
^
HINT: Perhaps you meant to reference the table alias "relTblAl_2".
QPSQL: Unable to create query
where "locationlist" in this case is equivalent to "city" in the
example, i.e. the name of the table containing the foreign key. The
reference to relTblAl_2 also makes no sense, as no such table or
column exists in my database. What am I doing wrong, and how can I get
this to work? Thanks.
-----------------------------------------------
Israel Brewster
Computer Support Technician
Frontier Flying Service Inc.
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7250 x293
-----------------------------------------------
--
[ signature omitted ]
Message 2 in thread
Anyone have any ideas on this? I did find that if I filter based on
the original column, rather than the related column, it works- but
this requires a extra query to determine what the value of the
original column should be, which to a large extent defeats the
purpose. I'm trying to reduce separate queries as much as possible.
Thanks!
-----------------------------------------------
Israel Brewster
Computer Support Technician
Frontier Flying Service Inc.
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7250 x293
-----------------------------------------------
On Mar 7, 2008, at 10:54 AM, Israel Brewster wrote:
> I need to filter the select of a QSqlRealtionalTableModel based on
> one of the relation columns. For example, say I have the example
> table from http://doc.trolltech.com/4.3/
> qsqlrelationaltablemodel.html set up, but now I want to filter the
> table to only select records where "City" is equal to "Oslo". I
> would assume I would do this by using the
> QSqlRelationalTableModel.setFilter(); command, but how do I format
> the filter argument? I tried just using the where clause from my
> select statement without the where, but this didn't work-details
> below.
>
> If I was executing the SELECT statement myself directly on the
> database, I would use a select statement something roughly like the
> following (forgive any syntaxtal errors, as I don't have this
> specific database to try on):
>
> SELECT employee.id, employee.name, city.name AS City, country.name
> AS Country from employee INNER JOIN city ON employee.city=city.id
> INNER JOIN country ON employee.country=country.id WHERE
> city.name='Oslo'
>
> and this works perfectly (substituting the proper table and column
> names for my database). According to the setFilter() documentation:
>
> "The filter is a SQL WHERE clause without the keyword WHERE"
>
> so I would assume I need to do
> relationalTableModel.setFilter("city.name=\'Oslo\'"); or the like,
> assuming I got the proper escaping of the quotes and everything.
> However, when I try that, I get an error stating:
>
> ERROR: invalid reference to FROM-clause entry for table
> "locationlist"
> LINE 1: ...ment"="relTblAl_4"."id") AND (inactive=false AND
> (locationli...
> ^
> HINT: Perhaps you meant to reference the table alias "relTblAl_2".
> QPSQL: Unable to create query
>
> where "locationlist" in this case is equivalent to "city" in the
> example, i.e. the name of the table containing the foreign key. The
> reference to relTblAl_2 also makes no sense, as no such table or
> column exists in my database. What am I doing wrong, and how can I
> get this to work? Thanks.
>
> -----------------------------------------------
> Israel Brewster
> Computer Support Technician
> Frontier Flying Service Inc.
> 5245 Airport Industrial Rd
> Fairbanks, AK 99709
> (907) 450-7250 x293
> -----------------------------------------------
>
>
> --
> To unsubscribe - send a mail to qt-interest-request@xxxxxxxxxxxxx
> with "unsubscribe" in the subject or the body.
> List archive and information: http://lists.trolltech.com/qt-interest/
--
[ signature omitted ]
Message 3 in thread
So should I take it from the total lack of response that no one knows
how to filter based on a relational column? Perhaps I should submit
this as a bug report or feature enhancement request? Thanks anyway!
-----------------------------------------------
Israel Brewster
Computer Support Technician
Frontier Flying Service Inc.
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7250 x293
-----------------------------------------------
On Mar 12, 2008, at 7:58 AM, Israel Brewster wrote:
> Anyone have any ideas on this? I did find that if I filter based on
> the original column, rather than the related column, it works- but
> this requires a extra query to determine what the value of the
> original column should be, which to a large extent defeats the
> purpose. I'm trying to reduce separate queries as much as possible.
> Thanks!
>
> -----------------------------------------------
> Israel Brewster
> Computer Support Technician
> Frontier Flying Service Inc.
> 5245 Airport Industrial Rd
> Fairbanks, AK 99709
> (907) 450-7250 x293
> -----------------------------------------------
>
>
> On Mar 7, 2008, at 10:54 AM, Israel Brewster wrote:
>
>> I need to filter the select of a QSqlRealtionalTableModel based on
>> one of the relation columns. For example, say I have the example
>> table from http://doc.trolltech.com/4.3/
>> qsqlrelationaltablemodel.html set up, but now I want to filter the
>> table to only select records where "City" is equal to "Oslo". I
>> would assume I would do this by using the
>> QSqlRelationalTableModel.setFilter(); command, but how do I format
>> the filter argument? I tried just using the where clause from my
>> select statement without the where, but this didn't work-details
>> below.
>>
>> If I was executing the SELECT statement myself directly on the
>> database, I would use a select statement something roughly like the
>> following (forgive any syntaxtal errors, as I don't have this
>> specific database to try on):
>>
>> SELECT employee.id, employee.name, city.name AS City, country.name
>> AS Country from employee INNER JOIN city ON employee.city=city.id
>> INNER JOIN country ON employee.country=country.id WHERE
>> city.name='Oslo'
>>
>> and this works perfectly (substituting the proper table and column
>> names for my database). According to the setFilter() documentation:
>>
>> "The filter is a SQL WHERE clause without the keyword WHERE"
>>
>> so I would assume I need to do
>> relationalTableModel.setFilter("city.name=\'Oslo\'"); or the like,
>> assuming I got the proper escaping of the quotes and everything.
>> However, when I try that, I get an error stating:
>>
>> ERROR: invalid reference to FROM-clause entry for table
>> "locationlist"
>> LINE 1: ...ment"="relTblAl_4"."id") AND (inactive=false AND
>> (locationli...
>> ^
>> HINT: Perhaps you meant to reference the table alias "relTblAl_2".
>> QPSQL: Unable to create query
>>
>> where "locationlist" in this case is equivalent to "city" in the
>> example, i.e. the name of the table containing the foreign key. The
>> reference to relTblAl_2 also makes no sense, as no such table or
>> column exists in my database. What am I doing wrong, and how can I
>> get this to work? Thanks.
>>
>> -----------------------------------------------
>> Israel Brewster
>> Computer Support Technician
>> Frontier Flying Service Inc.
>> 5245 Airport Industrial Rd
>> Fairbanks, AK 99709
>> (907) 450-7250 x293
>> -----------------------------------------------
>>
>>
>> --
>> To unsubscribe - send a mail to qt-interest-request@xxxxxxxxxxxxx
>> with "unsubscribe" in the subject or the body.
>> List archive and information: http://lists.trolltech.com/qt-interest/
>
> --
> To unsubscribe - send a mail to qt-interest-request@xxxxxxxxxxxxx
> with "unsubscribe" in the subject or the body.
> List archive and information: http://lists.trolltech.com/qt-interest/
--
[ signature omitted ]
Message 4 in thread
On Monday 24 March 2008 18:54:16 Israel Brewster wrote:
> So should I take it from the total lack of response that no one knows
> how to filter based on a relational column? Perhaps I should submit
> this as a bug report or feature enhancement request? Thanks anyway!
I havn't tried this yet, but I think you can actually get the query string
itself and have a look at it. That should be helpful in debugging it. If I
remember incorrectly, postgresql should be able to log the queries, too.
--
[ signature omitted ]
Message 5 in thread
On Mar 24, 2008, at 11:43 AM, Esben Mose Hansen wrote:
> On Monday 24 March 2008 18:54:16 Israel Brewster wrote:
>> So should I take it from the total lack of response that no one knows
>> how to filter based on a relational column? Perhaps I should submit
>> this as a bug report or feature enhancement request? Thanks anyway!
>
> I havn't tried this yet, but I think you can actually get the query
> string
> itself and have a look at it. That should be helpful in debugging
> it. If I
> remember incorrectly, postgresql should be able to log the queries,
> too.
Looks like you're right. Qt is generating this monstrosity of a query:
SELECT "equipment"."id","equipment"."machinename","relTblAl_2"."name"
AS locationlist_name,"relTblAl_3"."name" AS
categorylist_name,"relTblAl_4"."name" AS
departmentlist_name
,"equipment
"."warranty
","equipment
"."manufacturer
","equipment
"."serial
","equipment
"."macwan
","equipment
"."maclan
","equipment
"."macwlan
","equipment
"."description
","equipment
"."notes
","equipment
"."model
","equipment"."dop","equipment"."vendor","equipment"."inactive" FROM
"equipment","locationlist" "relTblAl_2","categorylist"
"relTblAl_3","departmentlist" "relTblAl_4" WHERE
("equipment"."location"="relTblAl_2"."id" AND
"equipment"."category"="relTblAl_3"."id" AND
"equipment"."department"="relTblAl_4"."id") AND (inactive=false) AND
(locationlist.name='FAI')
where "relTblAl_2"."name" AS locationlist_name is the column I want to
query on. At question here is that "relTblAl_2". If I change
locationlist.name in the filter to relTblAl_2.name (at least when
executing directly in postgresql) it works. The problem is, I don't
really know where that relTblAl_2 is coming from. I mean, obviously it
is some identifier that Qt has chosen to give the column for some
obscure reason, but I don't know that I can trust it to be the same
every time it is run. Especially considering that it appears to be
either random or position based (second relational column set up?
second column selected? Second entry in the FROM statement?) So
judging by this, you CAN'T reliably filter on a relational column
using Qt and a QSqlRelationalTableModel, since should the query
change, chances are this identifier will change, breaking the code.
Unless I am still missing something. Thanks for the response!
-----------------------------------------------
Israel Brewster
Computer Support Technician
Frontier Flying Service Inc.
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7250 x293
-----------------------------------------------
>
>
> --
> regards, Esben
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
>
> --
> To unsubscribe - send a mail to qt-interest-request@xxxxxxxxxxxxx
> with "unsubscribe" in the subject or the body.
> List archive and information: http://lists.trolltech.com/qt-interest/
--
[ signature omitted ]
Message 6 in thread
On Monday March 24 2008 22:50:09 Israel Brewster wrote:
> Looks like you're right. Qt is generating this monstrosity of a query:
>
> SELECT "equipment"."id","equipment"."machinename","relTblAl_2"."name"
> AS locationlist_name,"relTblAl_3"."name" AS
> categorylist_name,"relTblAl_4"."name" AS
> departmentlist_name
> ,"equipment
> "."warranty
> ","equipment
> "."manufacturer
> ","equipment
> "."serial
> ","equipment
> "."macwan
> ","equipment
> "."maclan
> ","equipment
> "."macwlan
> ","equipment
> "."description
> ","equipment
> "."notes
> ","equipment
> "."model
> ","equipment"."dop","equipment"."vendor","equipment"."inactive" FROM
> "equipment","locationlist" "relTblAl_2","categorylist"
> "relTblAl_3","departmentlist" "relTblAl_4" WHERE
> ("equipment"."location"="relTblAl_2"."id" AND
> "equipment"."category"="relTblAl_3"."id" AND
> "equipment"."department"="relTblAl_4"."id") AND (inactive=false) AND
> (locationlist.name='FAI')
>
> where "relTblAl_2"."name" AS locationlist_name is the column I want to
> query on. At question here is that "relTblAl_2". If I change
Indeed. It is in the from clause, so it should be some sort of table or view
in postgres, unless QT is playing with temporary tables and so on. Have you
tried a simple \d in psql to check if there is a table of said description in
your postgres database?
I don't understand the rest of what you are writing. If the query works in
psql. directly, the relTblAl_2 must be known to postgresql in some way, as it
isn't aliaset in any way. Keep looking! If you take a look at the QT source,
you can actually see how it pieces the query together (ah, open source :) )
and find out where it gets the relTblAl_2 from.
--
[ signature omitted ]
Message 7 in thread
On Mar 24, 2008, at 11:48 PM, Esben Mose Hansen wrote:
> On Monday March 24 2008 22:50:09 Israel Brewster wrote:
>
>> Looks like you're right. Qt is generating this monstrosity of a
>> query:
>
>>
>> SELECT "equipment"."id","equipment"."machinename","relTblAl_2"."name"
>> AS locationlist_name,"relTblAl_3"."name" AS
>> categorylist_name,"relTblAl_4"."name" AS
>> departmentlist_name
>> ,"equipment
>> "."warranty
>> ","equipment
>> "."manufacturer
>> ","equipment
>> "."serial
>> ","equipment
>> "."macwan
>> ","equipment
>> "."maclan
>> ","equipment
>> "."macwlan
>> ","equipment
>> "."description
>> ","equipment
>> "."notes
>> ","equipment
>> "."model
>> ","equipment"."dop","equipment"."vendor","equipment"."inactive" FROM
>> "equipment","locationlist" "relTblAl_2","categorylist"
>> "relTblAl_3","departmentlist" "relTblAl_4" WHERE
>> ("equipment"."location"="relTblAl_2"."id" AND
>> "equipment"."category"="relTblAl_3"."id" AND
>> "equipment"."department"="relTblAl_4"."id") AND (inactive=false) AND
>> (locationlist.name='FAI')
>>
>> where "relTblAl_2"."name" AS locationlist_name is the column I want
>> to
>> query on. At question here is that "relTblAl_2". If I change
>
> Indeed. It is in the from clause, so it should be some sort of table
> or view
> in postgres, unless QT is playing with temporary tables and so on.
> Have you
> tried a simple \d in psql to check if there is a table of said
> description in
> your postgres database?
There isn't. For one thing, no such table shows up with a \d:
equipInven=# \d
List of relations
Schema | Name | Type | Owner
--------+-----------------------+----------+-------
public | categorylist | table | admin
public | categorylist_id_seq | sequence | admin
public | db_sys | table | admin
public | departmentlist | table | admin
public | departmentlist_id_seq | sequence | admin
public | equipment | table | admin
public | equipment_id_seq | sequence | admin
public | installs | table | admin
public | locationlist | table | admin
public | locationlist_id_seq | sequence | admin
public | report | table | admin
public | report_report_id_seq | sequence | admin
public | scategorylist | table | admin
public | scategorylist_id_seq | sequence | admin
public | servicehistory | table | admin
public | servicehistory_id_seq | sequence | admin
public | services_id_seq | sequence | admin
public | serviceslist | table | admin
public | software | table | admin
public | software_id_seq | sequence | admin
public | users | table | admin
public | users_id_seq | sequence | admin
(22 rows)
For another, I created the database and tables myself from scratch,
and there is no way I would use such a meaningless title :)
> I don't understand the rest of what you are writing. If the query
> works in
> psql. directly, the relTblAl_2 must be known to postgresql in some
> way, as it
> isn't aliaset in any way.
Actually, it is. Look at the FROM clause again, specifically:
"locationlist" "relTblAl_2"
Note that there is no comma between those two, which means (if I
remember my SQL correctly) an implied AS, i.e FROM locationlist AS
relTblAl_2.
> Keep looking! If you take a look at the QT source,
> you can actually see how it pieces the query together (ah, open
> source :) )
> and find out where it gets the relTblAl_2 from.
Ok, yeah. I was hoping to avoid having to go to that much trouble. I
mean, I would hope this would just work, considering that I am trying
to use the data models exactly as intended. Or so I thought :P Oh
well, I guess I'll just have to dig up some time to go exploring.
Thanks for the tips!
-----------------------------------------------
Israel Brewster
Computer Support Technician
Frontier Flying Service Inc.
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7250 x293
-----------------------------------------------
>
>
> --
> regards, Esben
--
[ signature omitted ]
