Is there an "Only Without" way to join aggregates

For my use case, I have an entity "Program" and an entity "ProgramForStudent". The entity "Program for student" has a foreign key for "Program". I have a Screen that currently lists all the Programs. But, I want only the programs that are NOT already selected by the student to be displayed (so none of the Programs in ProgramForStudent). 

My first idea was to add a filter - I wanted to do something like !GetProgramsForStudent.List.Contains(Program.Id). But after researching the list functions I couldn't find one that I could use instead of Contains. 

I also thought about joining the entities, but I can't think of a way to use the join in a way that only the Programs WITHOUT ProgramForStudent will be filtered. 

Is there a way to do this that I'm missing.

Sorry if the solution is right under my nose. I tried researching this but I couldn't think of a way to implement it, but maybe I'm missing something that's obvious. Thank you in advance!

Hi Jana,

Outsystems provides multiple ways here i am mentioning my way to grab your result with aggregrate.

As per our requirement, we have two entities and we have to find out the lists of programs who are not selected by any students.

1. Add 'With or Without' Join. 

2. Add a filter which you are using in a List_Filter action. It will give you the same result & reduce the work.

Please use this link as a reference.

https://success.outsystems.com/Documentation/11/Reference/OutSystems_Language/Data/Handling_Data/Queries/Supported_Join_Types

Hope this may help you.


Thanks

Hi Jana,

You can do this with multiple ways I will mention two of them:

- you can create aggregate and sources will be Program and ProgramForStudent  with relation "with or without" then filter returned list using "ListFilter" action with condition "ProgramForStudent .Id = NullIdentifier()" so filtered list will contains only programs not taken by students


- Second option you can use advanced SQL using SQL or Oracle syntax based on your database engine


The answer from @Sameer Dhulia below should be the best practice way, due to:

- the performance of doing the filtering directly in the database, as is not the case of iterating and filtering with the FilterList
- the simplicity of using an Aggregate vs Advanced SQL

If you have any questions I will be happy to answer 

Where do you place the client action shown in the first picture? Is it "onInitialize" for my screen?

Hi Jana,

Don't use "OnInitialize" to fetch screen data but you can use either "Fetch Data from Database" or "Fetch Data From Other Sources"

- For using "Fetch Data from Database" you will need to make its fetch on start and add on after fetch action to filter data returned from database to get only programs not taken by any student



- If you will use "Fetch Data from Other Sources" make sure its fetch property "At Start". "Fetch Data from Other Sources" should have at least one output which will be list of programs that you can use in 




Please don't do list filters for something that can and should be done on the database.

That filter condition can be applied directly on the Aggregate, which will give you way more performance. 

Hi Jana,

Outsystems provides multiple ways here i am mentioning my way to grab your result with aggregrate.

As per our requirement, we have two entities and we have to find out the lists of programs who are not selected by any students.

1. Add 'With or Without' Join. 

2. Add a filter which you are using in a List_Filter action. It will give you the same result & reduce the work.

Please use this link as a reference.

https://success.outsystems.com/Documentation/11/Reference/OutSystems_Language/Data/Handling_Data/Queries/Supported_Join_Types

Hope this may help you.


Thanks

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.