Page 1 of 1

Query Builder Confusion

PostPosted: Mon Dec 16, 2019 8:35 pm
by Gordon McLean
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
Code: Select all
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
Code: Select all
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)

Re: Query Builder Confusion

PostPosted: Tue Dec 17, 2019 11:34 pm
by kwpsd
Hi, Gordon.

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

Code: Select all
qry.result.add( qry.joins.user_group_access_to_or_group.columns.org_id )


and change the call to:

Code: Select all
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)!

Re: Query Builder Confusion

PostPosted: Wed Dec 18, 2019 12:09 am
by Gordon McLean
Thank you I will try this it makes more sense than my attempt