Question

How to retrieve distinct / unique records using a retrieve action

3

How can I retrieve distinct records using a retrieve action. These records have a different id but an attribute has the same value. Example: ID Key attribute 1 1001 2 1001 89 1001 3 1002 4 1002 I only need 2 records. 1001 and 1002

asked 2012-05-29

Theo Swart

4 answers

Simon Black · Answer 1 · 2012-05-29

You can use OQL to perform this action:

SELECT DISTINCT(entityID.keyid) FROM entity as entityID

This is a similar question: https://forum.mendix.com/questions/3360/Count%20Distinct

Also here is some useful documentation on OQL https://world.mendix.com/display/refguide4/OQL

Ronald Catersels · Answer 2 · 2012-05-29

I reread your question a couple of times, but I am still not clear what you want to achieve. Do you only want to retrieve all the records with 1001 and 1002? Or do you want a list of all the values of the attribute so that each value appears only once?

For the first you can use a xpath. The second you could use a list operation for it. Retrieve all the objects so you have a list and then do a list operation (union) on itself. You then have the same list but now distinct.

Chris de Gelder · Answer 3 · 2012-05-29

Hi Theo,

Mendix is not SQL, you 'll have to reflect these choices in the model. 1001, 1002 can be a in separate entity with an association to your entity. Retrieve them with myEntity/myEntity_KeyEntity/KeyEntity = 1001 .

If you don't want to change the model retrieve full list, order by key, make a key variable. Iterate over list, when the key does not equal the key var add that entity to your result list.

Sebastiaan van den Broek · Answer 4 · 2012-05-29

If you don't care which instance of the object you're retrieving as long as some attribute has some value, just do a retrieve in a microflow and select 'first object only'. Then use an xpath constraint, constraining on the value of the attribute that you want, i.e. [Attribute = '1001']