Hi,
Servoy can’t make a distinct with a search on related records.
So I tried to write my own SQl query:
SELECT DISTINCT client.clientid from client,consultation where consultation.date_consultation >= ‘2006-04-04 00:00:00.0’ and consultation.idf_client = client.clientid order by client.clientid
When I execute it under PostgreSQL it works fine, but under servoy, it return some duplicate record.
Screenshot under servoy (With duplicate records):
Kinds regards,
And you are using that query with controller.loadRecords(query)?
What table is your form based on?
My form is base in ‘client’ table.
I use this method to load the query:
controller.loadRecords(query,‘?’)
What Servoy version do you use? 3.0 or 3.1?
Also do you have an additional sort going on somewhere on the related data?
I work on Servoy Version 3.1.4-build 408 and PostgreSQL 8.1
The form on the screenshot have a initial sort by “nom” (name of custumers) but no sort on the related data
I have done some testing on 3.1.4 with PostgreSQL 8.2.3 (both on Mac OS X) and distinct works as advertised.
So maybe something else is going on.
Are you using the JDBC driver for PostgreSQL 8.1 ? Or an older one ?
You can get a matching version via this link:
http://jdbc.postgresql.org/download.html
Hope this helps.
Hmm…never mind. I can reproduce it when searching 2 relations deep.
I can also reproduce it in Servoy 2.2.7.
It seems DISTINCT is simply ignored or something.
We use select distinct all over the place and have never seen this problem !?
The distinct is removed by servoy when some kind of ordering (other than on the pk) is applied.
Not all databases support ‘select distinct PK order by OTHERFIELD’ so we have to remove the distinct in that case.
Rob
But the example above selects and orders the PK…
Additionally, using distinct with a form by query is not allowed (although it may work sometimes), see the code sample of controller.loadRecords:
//4) to load records in to the form based on a query (also known as ‘Form by query’)
//controller.loadRecords(sqlstring,parameters);
//limitations/requirements for sqlstring are:
//-must start with ‘select’
//-must contain ‘from’ and ‘order by’ keywords
//-the ‘from’ must be a comma separated list of table names
//-must at least select from the table used in Servoy Form
//-cannot contain ‘group by’ or ‘having’
//-all columns must be fully qualified like ‘orders.order_id’
//-the selected columns must be the (Servoy Form) table primarykey columns (alphabetically ordered like ‘select a_id, b_id,c_id …’)
//-can contain ‘?’ which are replaced with values from the array supplied to parameters argument
Servoy release 3.5 will have some improvements on custom queries, including a fix for your problem, see http://forum.servoy.com/viewtopic.php?t … c&start=15
Rob
Yes, postgreSQL don’t allow to us DISTINCT on pk and ORDER BY on other filed.
But in my case, I use the same field (Pk)
I tried to create a new form without order with only the number total of record and this method:
var query = “SELECT DISTINCT client.clientid from client,consultation where consultation.date_consultation >= ‘2006-04-04 00:00:00.0’”
query += " and consultation.idf_client = client.clientid order by client.client"
controller.loadRecords(query,‘?’)
I have the same problem.
chgog:
order by client.client
I guess you mean “order by client.clientid”
yes sorry I mean clientid
I find a solution but it’s not the better way I think.
controller.find()
…
controller.search()// Sort the foundset on Pk
controller.sort(“clientid asc”)
var query = databaseManager.getSQL(foundset)
var param = databaseManager.getSQLParameters(foundset)
query = utils.stringReplace(query,‘select’, ‘select distinct’)
param.reverse()
while(query.search(“?”)>0){
query = query.replace(“?”,“'”+param.pop()+“'”)
}
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, 1000000);
controller.loadRecords(dataset)
I’ve a new problem : the founset load is limit to 1000 records with
controller.loadRecords(dataset) but not with controller.loadRecords(query)
controller.loadRecords(query) => remove my “distinct” and don’t limit the result
controller.loadRecords(dataset) => don’t remove my “distinct” but limit the result to 1000