EDITED: Hi folks - I have a number of tables that are not completely normalised so using them in a DBTreeView bean is difficult. I figured I could use a dataset to supply only the discinct ‘complex’ names in my table, rataher than the foundSet which shows all repeated data, something like this:
function onLoad()
{
//Set Bindings
var tree_complex = elements.bean_461.createBinding('tci_test', 'component_detail')
//Set Relations
//Set Data Providers
//Set Node Names
tree_complex.setTextDataprovider('cd_complex') // company is a field in the clients table
var query = "SELECT DISTINCT component_detail.cd_complex FROM component_detail"
var ds = databaseManager.getDataSetByQuery('tci_test',query,null,-1)
application.output(fs)
ds.find()
ds.search(true, true)
elements.bean_461.addRoots(fs);
}
Fails on the ds.search as there is no dataset search functionality. Tried doing this also to no avail:
var ds = databaseManager.getDataSetByQuery('tci_test',query,null,-1)
var fs = databaseManager.getFoundSet(ds)
fs.find()
fs.search(true, true)
and having discovered that controller.loadRecords() requires the PKID in the column SELECT (and therefore DISTINCT is not an option) realise I’ve run out of options to get distinct values to show in my TreeView without creating more tables and link values.
I’ve obviously missed the concept here, so any advice on how to manage this without resorting to full normalisation would be welcome.
Assuming your PK is named cd_id then you can get unique values using the following SQL.
It uses a self-join on cd_complex and only returns the first matched rows.
var _sQuery = "SELECT a.cd_id \
FROM component_detail a \
JOIN component_detail b ON a.cd_complex=b.cd_complex \
WHERE (CASE WHEN a.cd_id=b.cd_id THEN 1 ELSE 0 END) = 1"
// Load the foundset using the query
controller.loadRecords(_sQuery);
WOW Robert - excellent thanks Bud - you are a SQL Wizard too! But unfortunately that does still not result in Distinct cd_complex, it actually returns all of the cd_complex rows? I ran this in Navicat too and its returns all rows unfortunately.
Kahuna:
WOW Robert - excellent thanks Bud - you are a SQL Wizard too! But unfortunately that does still not result in Distinct cd_complex, it actually returns all of the cd_complex rows? I ran this in Navicat too and its returns all rows unfortunately.
I see what you mean. However it’s not returning all rows, but doesn’t filter them completely either. It seems it’s not selecting and joining them in the same order every time. Will have to look into this some more.
I found a syntax that does filter all double cd_complex values.
SELECT a.cd_id
FROM component_detail a
WHERE a.cd_id = (SELECT b.cd_id FROM component_detail b WHERE b.cd_complex=a.cd_complex ORDER BY b.cd_id ASC LIMIT 1)
Of course if you use Sybase or MSSQL then you can’t use the LIMIT syntax. You then need to use the following syntax:
SELECT a.cd_id
FROM component_detail a
WHERE a.cd_id = (SELECT TOP 1 b.cd_id FROM component_detail b WHERE b.cd_complex=a.cd_complex ORDER BY b.cd_id ASC)
But I believe you are using MySQL so you will be fine with the first query.
Also make sure you have an index on the cd_complex column or else this query will perform horribly.
The first one works well in Navicat Robert thanks - but I really need the app to stay Db agnostic so thought the second one may work in MySQL too but appears to have a SQL error - which I’m assuming is TOP?
I know for sure the app will be installed against MSSQL and MySQL in the next month hence the reasoning! I’m sure I can apply both based on the backend used - but the challenge is mainly that this first one works fine in Navicat but errors on:
The query takes 37 seconds to run against 12k records so its probably not the right way for us to go with this anyway Robert (that’s with an index on cd_id and cd_complex) but I suspect we’ll come accross this problem further down the line anyways with other less than optimal tables so thanks for helping me get the concepts sorted.
Any idea why this might fail in Servoy where all I’m now doing is this?:
var _sQuery = "SELECT a.cd_id
FROM component_detail a
WHERE a.cd_id = (SELECT b.cd_id FROM component_detail b WHERE b.cd_complex=a.cd_complex ORDER BY b.cd_id ASC LIMIT 1)"
// Load the foundset using the query
controller.loadRecords(_sQuery);
Kahuna:
The first one works well in Navicat Robert thanks - but I really need the app to stay Db agnostic so thought the second one may work in MySQL too but appears to have a SQL error - which I’m assuming is TOP?
Correct. TOP and LIMIT do the same thing but not every brand supports both of them. I believe TOP is actually a Sybase ‘invention’ and Microsoft is using it because they based MSSQL on Sybase code.
You can however check what brand of database a connection uses via databaseManager.getDatabaseProductName(connectionName) and use the appropriate SQL.
Kahuna:
I know for sure the app will be installed against MSSQL and MySQL in the next month hence the reasoning! I’m sure I can apply both based on the backend used - but the challenge is mainly that this first one works fine in Navicat but errors on:
That’s odd. Try adding using the real tablename instead of the alias ‘a’. (see below)
Kahuna:
The query takes 37 seconds to run against 12k records so its probably not the right way for us to go with this anyway Robert (that’s with an index on cd_id and cd_complex) but I suspect we’ll come accross this problem further down the line anyways with other less than optimal tables so thanks for helping me get the concepts sorted.
I tested it on a table with 27+K records (in PostgreSQL) and without an index is took many many seconds (I didn’t let it run the whole way). With an index on it it took 300ms running on my MacBook Pro.
So this makes me wonder if your index is being used at all. Try reindexing (or recreating) the index and see how it performs then.
Kahuna:
Any idea why this might fail in Servoy where all I’m now doing is this?:
var _sQuery = "SELECT a.cd_id
FROM component_detail a
WHERE a.cd_id = (SELECT b.cd_id FROM component_detail b WHERE b.cd_complex=a.cd_complex ORDER BY b.cd_id ASC LIMIT 1)"
// Load the foundset using the query
controller.loadRecords(_sQuery);
I haven’t tested this but try rewriting it this way:
var _sQuery = "SELECT component_detail.cd_id
FROM component_detail
WHERE component_detail.cd_id = (SELECT b.cd_id FROM component_detail b WHERE b.cd_complex= component_detail.cd_complex ORDER BY b.cd_id ASC LIMIT 1)"
// Load the foundset using the query
controller.loadRecords(_sQuery);