IN clause in Aggregates

By Alexandre Scheurkogel on 5 Nov 2015

I've run into this a few times.

I would like to have the ability to do a simple query with an IN clause, without having to rely on advanced queries.

It should accept a list of <myTable> Identifier.

Today we do ( from an aggregate perspective):

   SELECT * FROM {TASKS} WHERE id = 1 or id = 2 or id = 3

But it would be really awesome if we could do 

  SELECT * FROM {Tasks} WHERE id in (1,2,3) 

This allows us to do some pretty cool stuff with Lists setc ( eg you can now shim some of the more advanced sql functionality without writing advanced queries).

An example would be:

    myList = SELECT id from People WHERE OrganisationId = 2
    SELECT * FROM {Tasks} WHERE personId = myList

Which would be 2 actions, and somewhat faster/cleaner than the current way  
  • Writing an advanced query ( frowned up by my companies' best practices guide - and I've also found advanced queries tend to be annoying to manage )
  • Looping through myList and putting the outputs into another list, which is then passed around (this also has a few nasties - lots of DB hits (albeit small ones), and refreshing this with paging is a pain

Justin James11 Nov 2015
This has been requested a zillion times... the search lookup to find it is too generic to discover it... OutSystems, we really need this one!

Kilian Hekhuis16 Nov 2015
Indeed. Should be a no-brainer to implement.
mariap8 Feb

Any news on this? It would be tremendously useful...

I'm really surprised how such a state of the art platform lacks this sort of basic things.

Sridhar Sivaprakasam25 Apr (4 weeks ago)

The challenge starts from the Entity here.

1. Entity one to many relation is not treated as the list here. We should compare the list with the value with list taken from the entity.

2. Compare operator: If we need to iterate a list to compare, it need iterator specific operator which ends up in time taking loops. It would be like combination of '=' and Index with little logic.

Would Hashing method helps in equating the values?