Query Builder Confusion

Classic Servoy
1

Hi
Firstly I am new to Query Builder so apologies for what could be a super obvious issue.

I have a relatively simple SQL statement that I am trying to work into QB to lock my table data based on user access rights. The SQL clip works as in it returns the correct results but getting this from here to the tableFilterParam seems incredibly involved. Is there a better method ? I somehow imagined you would be able to do something like databaseManager.addTableFilterParam(qry) where qry is my working query ??

SQL Clip

SELECT ORG_Group_Org.ORG_ID
FROM USER_Group_Access
JOIN ORG_Group_Org ON USER_Group_Access.group_id = ORG_Group_Org.Group_id
WHERE USER_Group_Access.user_name = 'client'

Query Builder

var qry = datasources.db.dc.org_group_org.createSelect();
qry.where.add(qry.joins.user_group_access_to_or_group.columns.user_name.eq(scopes.svySecurity.active_user.user_name));	
	
var fs = datasources.db.dc.org_group_org.getFoundSet();
fs.loadRecords(qry);

var arr = []
for(var i=0;i<=fs.getSize();i++) {
	fs.setSelectedIndex(i)
	arr.push(fs.org_id)
}
	
var success = databaseManager.addTableFilterParam('dc','org','org_id','in',arr)
application.output(success)
2

Hi, Gordon.

It seems to me that you should add a result clause to your query, something like:

qry.result.add( qry.joins.user_group_access_to_or_group.columns.org_id )

and change the call to:

var success = databaseManager.addTableFilterParam( qry )

and, get rid of the array part. Caveat: I am supplying this code from memory, so it may not be exact. I hope this helps (and works)!

3

Thank you I will try this it makes more sense than my attempt