Query Builder Confusion

Questions, tips and tricks and techniques for scripting in Servoy

Query Builder Confusion

Postby Gordon McLean » Mon Dec 16, 2019 8:35 pm

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)
Gordon McLean
Clickdigital.com
Gordon McLean
 
Posts: 253
Joined: Wed Aug 03, 2005 12:24 pm
Location: UK

Re: Query Builder Confusion

Postby kwpsd » Tue Dec 17, 2019 11:34 pm

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)!
Kim W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 687
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA

Re: Query Builder Confusion

Postby Gordon McLean » Wed Dec 18, 2019 12:09 am

Thank you I will try this it makes more sense than my attempt
Gordon McLean
Clickdigital.com
Gordon McLean
 
Posts: 253
Joined: Wed Aug 03, 2005 12:24 pm
Location: UK


Return to Methods

Who is online

Users browsing this forum: No registered users and 7 guests