IN clause in Aggregates

By Alexandre Scheurkogel on 5 Nov 2015
Hi,

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!

J.Ja
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.

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?

Nuno Fernandes19 Sep 2010
This commonly used operator forces the use of advanced queries, thus lowering the "build to change". But, on the other hand, simple queries should remain "simple"...

I propose keeping simplicity of simple queries by having the "IN" SQL operator available referencing a previous query output field (thus, "IN" the values of field X returned by that query.

Example of such an expression (for a simple query condition):

SIMPLE_QUERY_ENTITY.EntityB_ID IN PreviousQuery.ENTITY_B.ID

Of course, ideally, the usual ".List.Current." part of the record list expression should be omitted since they would affect readability.

Merged from 'Allow "IN" SQL operator in simple queries' (idea created on 2010-09-19 12:30:03 by Nuno Fernandes), on 2017-06-16 08:04:54 by Goncalo Borrega
Allow it in the Aggragates too!

Merged from 'Allow "IN" SQL operator in simple queries' (idea created on 2010-09-19 12:30:03 by Nuno Fernandes), on 2017-06-16 08:04:54 by Goncalo Borrega
Ravi V29 Mar

It would be much better if we had the ability to type "IN" keyword in the aggregate filters.

For example if I want to filter on multiple selection filter values, there is no option other than going for writing our own advanced queries for which we need to manually write our own queries for sorting, filtering, creating structures and so many other things which is going to take lot of development time.

Expecting something like this in Filter condition in Aggregate query 

Status.id in (1,2,3)



Merged from 'Aggregate query should accept "IN" keyword' (idea created on 2017-03-29 17:37:31 by Ravi V), on 2017-04-01 19:54:06 by Pedro Cardoso

Merged from 'Allow "IN" SQL operator in simple queries' (idea created on 2010-09-19 12:30:03 by Nuno Fernandes), on 2017-06-16 08:04:54 by Goncalo Borrega

An alternative for that, in order to keep using the Aggregate instead of a SQL node is using the Index function, to filter for Ids within a specific string keyword.


Not a SQL IN, but the result is the same.



Merged from 'Aggregate query should accept "IN" keyword' (idea created on 2017-03-29 17:37:31 by Ravi V), on 2017-04-01 19:54:06 by Pedro Cardoso

Merged from 'Allow "IN" SQL operator in simple queries' (idea created on 2010-09-19 12:30:03 by Nuno Fernandes), on 2017-06-16 08:04:54 by Goncalo Borrega
J.30 Mar

similar to http://www.outsystems.com/ideas/2618/aggregate-filters-include-the-ability-to-use-in-function




Merged from 'Aggregate query should accept "IN" keyword' (idea created on 2017-03-29 17:37:31 by Ravi V), on 2017-04-01 19:54:06 by Pedro Cardoso

Merged from 'Allow "IN" SQL operator in simple queries' (idea created on 2010-09-19 12:30:03 by Nuno Fernandes), on 2017-06-16 08:04:54 by Goncalo Borrega
Daniel Asserati24 Aug 2016

This may have already been raised as an idea, but it would be very useful to add the ability to use the "IN" function in aggregate filters.

I have switched some of my filters in an application to be multi-selects rather than single selects and this has caused all of my aggregates to have to be re-written as Advanced SQL queries.



Merged from 'Aggregate Filters - Include the ability to use "IN" function' (idea created on 2016-08-24 14:39:51 by Daniel Asserati), on 2017-03-31 08:09:14 by Pedro Cardoso

Merged from 'Aggregate query should accept "IN" keyword' (idea created on 2017-03-29 17:37:31 by Ravi V), on 2017-04-01 19:54:06 by Pedro Cardoso

Merged from 'Allow "IN" SQL operator in simple queries' (idea created on 2010-09-19 12:30:03 by Nuno Fernandes), on 2017-06-16 08:04:54 by Goncalo Borrega
Miguel Domingues25 Aug 2016

Hi,

I have a similar use case. Instead of multi-selects I have a set of checkboxes (categories, tags) that are dynamic and stored in an entity.

Each item/product has one category/tag associated, so (un)ticking each checkbox should hide/show the items/products based on the ticked checkboxes.


The "IN" function would simplify the logic for this.



Merged from 'Aggregate Filters - Include the ability to use "IN" function' (idea created on 2016-08-24 14:39:51 by Daniel Asserati), on 2017-03-31 08:09:14 by Pedro Cardoso

Merged from 'Aggregate query should accept "IN" keyword' (idea created on 2017-03-29 17:37:31 by Ravi V), on 2017-04-01 19:54:06 by Pedro Cardoso

Merged from 'Allow "IN" SQL operator in simple queries' (idea created on 2010-09-19 12:30:03 by Nuno Fernandes), on 2017-06-16 08:04:54 by Goncalo Borrega
Justin James26 Aug 2016

Been raised like a million times already, but it's hard to find in search because the keywords show up in so many things. :)

J.Ja



Merged from 'Aggregate Filters - Include the ability to use "IN" function' (idea created on 2016-08-24 14:39:51 by Daniel Asserati), on 2017-03-31 08:09:14 by Pedro Cardoso

Merged from 'Aggregate query should accept "IN" keyword' (idea created on 2017-03-29 17:37:31 by Ravi V), on 2017-04-01 19:54:06 by Pedro Cardoso

Merged from 'Allow "IN" SQL operator in simple queries' (idea created on 2010-09-19 12:30:03 by Nuno Fernandes), on 2017-06-16 08:04:54 by Goncalo Borrega

And this one could also be merged I think.

http://www.outsystems.com/ideas/496/allow-in-sql-operator-in-simple-queries


Thoughts?



Merged from 'Aggregate query should accept "IN" keyword' (idea created on 2017-03-29 17:37:31 by Ravi V), on 2017-04-01 19:54:06 by Pedro Cardoso

Merged from 'Allow "IN" SQL operator in simple queries' (idea created on 2010-09-19 12:30:03 by Nuno Fernandes), on 2017-06-16 08:04:54 by Goncalo Borrega
Ravi V31 Mar

Yes we should merge this one too.

I agree there are a number of outsystems users wanting this feature to be included in aggregate queries. There are so many threads regarding this subject. 

I am wondering what would be the response from outsystems team development on adding this feature in future releases.

This features will ease a lot whenever there is multiple selection on filter. Most of my business users will always ask for multiple selection whenever they see a filter.



Merged from 'Aggregate query should accept "IN" keyword' (idea created on 2017-03-29 17:37:31 by Ravi V), on 2017-04-01 19:54:06 by Pedro Cardoso

Merged from 'Allow "IN" SQL operator in simple queries' (idea created on 2010-09-19 12:30:03 by Nuno Fernandes), on 2017-06-16 08:04:55 by Goncalo Borrega
Ravi V31 Mar

Thanks Luis for the alternative though :) 



Merged from 'Aggregate query should accept "IN" keyword' (idea created on 2017-03-29 17:37:31 by Ravi V), on 2017-04-01 19:54:06 by Pedro Cardoso

Merged from 'Allow "IN" SQL operator in simple queries' (idea created on 2010-09-19 12:30:03 by Nuno Fernandes), on 2017-06-16 08:04:55 by Goncalo Borrega

Merged!



Merged from 'Allow "IN" SQL operator in simple queries' (idea created on 2010-09-19 12:30:03 by Nuno Fernandes), on 2017-06-16 08:04:55 by Goncalo Borrega