Servoy Forum

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.

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;

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

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.