Using QBSelect to return unique values in a column

Questions, tips and tricks and techniques for scripting in Servoy

Using QBSelect to return unique values in a column

Postby joe26 » Tue Oct 27, 2015 12:16 am

I'm attempting to return unique location names, but I seem to misunderstand the whole add column and distinct keyword options...

Servoy 7.1

/** @type {QBSelect<db:/stsservoy/idfiles>} */
var q = databaseManager.createSelect('db:/stsservoy/idfiles');
q.result.add(q.columns.id_location);
q.result.distinct = true;
q.where.add(
q.and
.add(q.columns.marks_id.isin(idlist))
);
var resultQ = databaseManager.getFoundSet(q);

I anticipated that I would ONLY get the selected column, id_location, and only a set of single-value rows with unique values for each row.

Upon execution, I get the entire column nameset, and all records that match the idlist array for column marks_id.
I'd rather not use cycles to go through and find values unique to a single column.

Hints? (Actually, looking for answers to both. One, limit the returned foundset to the columns that are specifically added. And, two, getting these unique columns...

thanks,
--Joe.
joe26
 
Posts: 172
Joined: Wed Jun 19, 2013 10:30 pm

Re: Using QBSelect to return unique values in a column RESOL

Postby joe26 » Thu Oct 29, 2015 7:49 pm

hmmm... okay.

There is a difference if a dataset or foundset requested. Probably early on, the above sufficed to get me the data I'd want, but I never worried the extra columns,
so it was my go-to solution.

The intent is a global method to fill out a valuelist global method for locations on a form.

For those poor stiffs who try to make use of sparse information on the wiki, here's the update. It works, but is hardly definitive.

/** @type {QBSelect<db:/stsservoy/idfiles>} */
var q = databaseManager.createSelect('db:/stsservoy/idfiles');
q.result.add(q.columns.id_location);
q.result.distinct = true; // this has no bearing when requesting getDataSetByQuery(..) When removed, returns dataset of all and repeated table cell information. Otherwise, distinct values only.
q.where.add(
q.and
.add(q.columns.delete_flag.isNull)
.add(q.columns.marks_id.isin(idlist))
); // idlist is an array of marks_id values. I cut out the isNull line in the previous post, but it was a part of the .and clause.
var resultQ = databaseManager.getFoundSet(q); // returns a foundset with all columns
var resultTest = databaseManager.getDataSetByQuery(q,-1); //return the dataset for use in the valuelist
//return databaseManager.convertToDataSet(resultQ,['shop_order']); // This DID provide a list, but refused to yield distinct values.
return resultTest;
joe26
 
Posts: 172
Joined: Wed Jun 19, 2013 10:30 pm

Re: Using QBSelect to return unique values in a column

Postby rgansevles » Fri Oct 30, 2015 11:08 am

Joe,

When you use queries with foundsets it effectively translates to 'select * from tab where pk in (query)' (pseudo code).
Even if you remove duplicates from your query, it may still return duplicates because multiple records in tab may match.
Also, the foundset contains all columns, that is why you see more then just the requested column.

On the other hand, databaseManager.getDataSetByQuery(q,-1) just executes the query including distinct and returns only the columns requested.

Hope this helps,

Rob
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1927
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL

Re: Using QBSelect to return unique values in a column

Postby joe26 » Fri Oct 30, 2015 10:31 pm

Thank You, Rob!

I was only guessing, and it took a few iterations to see the difference. Sticking to old patterns isn't productive either, but never had to worry about the additional columns, having attempted a 'distinct' query before. Useful in other ways, as long as it is an intended result from the call which could disappear in later Servoy versions. Particularly, easing the QBSelect add statement if one is just going to parse the foundset anyway. Memory footprint may be larger, however.

--Joe.
joe26
 
Posts: 172
Joined: Wed Jun 19, 2013 10:30 pm


Return to Methods

Who is online

Users browsing this forum: No registered users and 6 guests

cron