Developing Web Apps | Exercise 6.6 - Data queries and widget 2

Exercise 6.6 - Data queries and Widget 2

Movies List Filter Workings

Please help me comprehend the workings of the movie filter set by the movie genre combo box and DVD checkbox.

First, I understand that an aggregate in outsystems is basically a database query, and results of which is store in a .list variable. And, the aggregate Filter seems to be a WHERE clause in the query.

In the exercise, the filter text added in the aggregate to filter out the movies is thus:

  1. For movie genre:
    MovieGenreId = NullIdentifier() or Movie.GenreId = MovieGenreId 

  2. For ‘on DVD’
    IsAvailableOnDVD = False or Movie.IsAvailableOnDVD = True 

If the aggregate in outsystems is a db query.. then how is that its possible to query the db using a local page variable and thereby filter the query output .list ?

i.e. how can MovieGenreId = NullIdentifier() work in the query?.. and likewise how can IsAvailableOnDVD = False work in the query?

Will someone please help unravel the links between all these and help me see how this works?

Hi David,

firstly you can check the SQL generated by an aggregate by opening the Executed SQL property:


Then click to convert. It will create a SQL based on your aggregate and you will understand how it is done.

Regards

Thank you Jose.

Yet, I see no "Executed SQL" in my aggregate properties. See my screenshot.

NoexecutedSQL.png

You need to open the aggregate and let data be displayed to have that property visible.

mvp_badge
MVP

Hi David,

The local variables that are used in an aggregate are evaluated before the aggregate executes, and passed to the database engine as inputs for the pre-generated SQL.

This means that when the database server executes the query it already knows those values so it can calculate the outcome of something like MovieGenreId = NullIdentifier() or sAvailableOnDVD = False even before the query starts.

Hope this helps.

Thanks Jorge for the explanation; yet, I desire to objectively see the logic of what's really going on. Because I see the filter to be a WHERE clause, I still can't see where and how the MovieGenreId = NullIdentifier()  affects the query.

I understand that the objective is to get the movies with a certain MoviGenreId input parameter gotten through the combobox input, or if there is no input, then get all movies.

I was wondering if instead of adding MovieGenreId = NullIdentifier()  in the aggregate filter, was it possible to instead assign a null value to the "all genres" option in the combo box under the special list options? (see attached image).

Obviously this would not work because the entry there is a mere text 'null' that I entered, but just did so for me to covey the idea to you, so to hear from you if there was any other way to add a null value in the special list value option.

... because by doing so, it much clear to me what value goes into the db query and its where clause. But by simply adding MovieGenreId = NullIdentifier() in the filter, I still can't see what's really going on .. many missing links that I can't connect.

Hi David,

1) please experiment with your aggregate preview, by once leaving the test value empty, and once populate it with a valid genre id.  Compare both executed SQL, you will see that in the SQL, the platform only adds the where clause when necessary.  This is what happens in preview, but I guess something similar would go on in the generated code.  So depending on the values of all parameters, a different SQL is executed.

2) Special List is just a way of defining an alternate (set of) value(s), which choice also goes into an alternate variable (if even defined), the special variable.  This means that when choosing a special list value, the 'normal' variable will have en empty value (which for identifiers is the nullidentifier)  So I'm not sure what you would want to do with the special value ??

Dorine

mvp_badge
MVP

I believe Dorine already said it all, but to reinforce it... your problem seems to be related to understanding how combo boxes work. In particular that:

  • When you select an option from the Special List it will store the corresponding value in the Special Variable (if one is defined) and assign the default value to the Variable (according to its data type, which for Identifiers is NullIdentifier())
  • When you select an option that comes from the Source Entity/Source Record List it will store the Id of the selected record in the Variable and, if the Special Variable is defined, assign the default value to it (according to its data type).

So for Filter MovieGenreId = NullIdentifier() or Movie.GenreId = MovieGenreId, when we are comparing our Variable to NullIdentifier() we are basically asking "has a value from the Special List been selected" and, if so, ignore the remaining condition that would compare to a specific value (because the filter will already be true).

These variables (MovieGenreId and IsAvailableOnDVD) should be treated the same way as a SQL tool's input parameters are (barring any optimizations the platform may be able to do to the Aggregate).

Hope this helps!

I deleted the part from the MovieGenereId filter "or  or MovieGenreId = NullIdentifier()" and it still worked.

Moreover, after deleting I still saw the option "NullIdentifier()" in test values for the variable MovieGenreId and when selected I saw the executed SQL contained "WHERE ([ENMOVIE].[GENREID] IS NULL)

i.e. the statement "or  or MovieGenreId = NullIdentifier()" in the filer seems not necessary, and makes the working logic clear to my mind.

See images below.

Hi David,

the query 'where genre = NULL' returns movies that don't have a genre, that is not the same as returning all genres.  

Can you show your entity data view with all the movies and their genres ?  Because your demo only makes sense to me if most of your movies don't have a genre yet.

Dorine

Hi Dorine!

First, thank you much for your effort to help.

:-) Yes.. most of my movies have no genre! and the one that has a genre is not showing.

Here...


Now then... that brings me back to my mind boggling question - how does the statement in the filter ".. or or MovieGenreId = NullIdentifier()" really work then?

Per your earlier answers and those of others, I understand that the aggregate statement is executed prior to the SQL query which generates the SQL query to be executed.

But there is much ambiguity in my mind between the "or MovieGenreId = NullIdentifier()" and the generated SQL query...

- I understand that "or MovieGenreId = NullIdentifier()" is for when there is no value in the variable MovieGenreId which is so when the special list option "all movies" is selected.

- When there is no value in the MovieGenreId, then how does the SQL query get generated to return movies with all genres?

- Logically analysing - I can't see how adding "or MovieGenreId = NullIdentifier()" to the filter statement returns all movies. i.e. I assume that when the value in the variable MovieGenreId is empty, then there is no input into the filter for Movie.GenreId and therefore outsystems automatically generates the appropriate SQL query to return movies with any genre... but this needs to be assumed and not clearly seen and understood in the development environment...

... if not for the given instructions in this exercise lesson, how would I logically understand and use outsystems aggregate tool to return all movies in this app?.. or achieve a similar functional outcome in another app?

*** FYI - I have much less experience in code-based app development, and little to no knowledge on standard industry practices and abstractions in code-based app development. My foundations in computer science are in logic circuits and computer systems architecture (which I am still learning and not an expert), which is why I always strive to see visible connections between all moving parts, and which is why I was attracted to outsystems because most moving parts and their connectors can be visualised...

... therefore please excuse me if my curiosity & questions bother you.

mvp_badge
MVP

Hi David,

There's no ambiguity/assumption.

If you have the Filter:

MovieGenreId = NullIdentifier() or Movie.GenreId = MovieGenreId

and your variable MovieGenreId has the value NullIdentifier(), then MovieGenreId = NullIdentifier() evaluates to True and you will end up with:

True or Movie.GenreId = MovieGenreId

which is always True (because True or anything = True, you can read more about the logical disjunction operator or on Wikipedia, including a useful truth table)

If you always wanted to return all movies regardless of their genre, then you simply don't add the filter.

Hope this helps clarify!

David,

Curiosity is your most valuable talent in this industry, so that's fine.

When you use a low code abstraction, you are basically leaving some of the work to be done behind the scenes for you.  

So there's 2 questions here : 

1. does it logically make sense ?

the answer is yes, see reply by Jorge.  So to be able to use aggregates there's not much more you need to know.

2. oke but how does it all work, i can't make a mental image of what actual sql will be executed ?

that's just down to curiosity, you don't need to know this to successfully use aggregates, but as i said earlier, just run your query in Service Studio with and without a value for LC_MovieGenreId, and look at the resulting sql being run, you will notice that in one case, the platform adds a where clause for your genre to the sql and in the other it doesn't.

Dorine 

That should suffice, thank you all. I will go through all your answers couple of times and then could summarise and post later.

A few questions still:

  1. For the IsAvailableOnDVD checkbox filter:

    The checkbox is bound to a boolean variable IsAvailableOnDVD.

    in the aggregate filter the statement is 'Movie.IsAvailableOnDVD = IsAvailableOnDVD or IsAvailableOnDVD = False'

    What does 'or IsAvailableOnDVD = False' mean?
    - IsAvailableOnDVD has no value?,
    OR
    - IsAvailableOnDVD has value 'False'?

    *** (If IsAvailableOnDVD has value 'False', then 'Movie.IsAvailableOnDVD = False would be the criteria by which the movies list would be returned, i.e. movies that are not available on DVD)

  2. Special list Option 1 Value 1 = -1

    Why -1? Why not any other value? (There is not special variable and I guess the value in Option 1 Value 1 would not be stored anywhere, for which cause any other value could be used too?)
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.