If the user checks the checkbox only the movies available on DVD (the movies that have the column IsAvailableOnDVD as true) will appear. Otherwise, all movies are displayed whether or not are available on dvd. So the condition for this is:
"IsAvailableOnDVD = False or Movie.IsAvailableOnDVD = True"
But I'm not understanding how this part of the condition "or Movie.IsAvailableOnDVD = True" works to displays all movies whether or not they are available. Do you know how this works?
Another example of a condition, in this case, to show only the movies that are of the selected movie genre otherwise show all movies:
MovieGenreId = NullIdentifier() or Movie.GenreId = MovieGenreId
And this condition seems intuitive because if the MovieGenreId is null the user didn't select any genre so it shows all movies, but if the variable MovieGenreId has a value it should display only the movie genres associated with the selected genre. However, the condition related to the checkbox I'm not understanding. Taking into consideration this MovieGenreId condition, do you know why condition related to the checkbox is not something like:
IsAvailableOnDVD = NullIdentifier() or Movie.IsAvailableOnDVD = IsAvailableOnDVD
Maybe I'm not understanding correctly how this pattern works to show the results taking in account the value of a variable, if the variable has a value, otherwise show all records.
Hi OsCar_
I call this a "Condition Switch"
So, you have two conditions put together with an OR condition.This means that it is enough to ONE of them to be TRUE to the result of the filter to also be true.
FALSE OR FALSE = FALSETRUE OR FALSE = TRUEFALSE OR TRUE = TRUETRUE OR TRUE = TRUE
But one of the conditions is not being used against an entity attribute in the aggregate:
MovieGenreId = NullIdentifier()
So, the result will be the same for all the lines in the aggregate. If what is stored in MovieGenreId IS NullIdentifier, the result of this condition will be TRUE, and the other condition result does not mater. SO, for all lines, the end result of the filter will be true, and all lines will enter the result (unless you have other filters of course).
But if you have an movie genre identifier inside this variable (ex: Drama), than the result of this condition will be FALSE for all lines, and if a line enter the result or not will depend on the OTHER condition:
Movie.GenreId = MovieGenreId
That will put in results only the lines where the genre is the same as the one stored in the variable MovieGenreId.
Hope this helps.
Cheers.
Eduardo Jauch wrote:
Hi,
I have a doubt about SQL that is related to this post.
Sometimes is necessary to have some sort of ifs in queries, however, there are no ifs in SQL right? So it seems that we should handle these cases with logic operators but I'm not understanding properly how this works, how these logic operators can be used as ifs.
How that works is like you explained in the answer, or what you explained in the answer is only valid for the aggregates and SQL works in a different way?
Thanks, Eduardo Jauch & Kilian HekhuisIndeed an explanatory & helpful answer for all of us here...
Hi Oscar,
There's two type of Ifs in OutSystems: the If Widget, and the If as part of an Expression. Both are structured similarly: there's a condition that tells when the If should return the True part, and what to return if the condition fails (the False part). For queries, there's only Filters: you specify what data to return. A Filter also takes the form of an Expression, the difference being it typically uses data from the database to test against. There is never a "False" part in a Filter: data that's not filtered by the condition is not retrieved from the database (and rightly so).
Note that logic operators are not "used as ifs": both a Filter and an If have an Expression that tells when to apply the filter / when to return the True part.
Thanks, but for example, in the query below there is this part:
Where ({UserStatus}.[Id] in (@UserCheckedStatus) or @ReturnAll = 1)
That seems that work like an if and an else and I'm not understanding properly how this works. But it seems like an if and else, that is if the UserStatus.Id(s) are in the UserCheckedStatus variable we should return only that users otherwise we should return all users.
Query:
SELECT {UserStatus}.[Label], Count({User}.Id)
FROM {User} JOIN {UserStatus} on {User}.[UserStatusId] = {UserStatus}.[Id]
and {User}.[RegistrationDate] >= @StartDate and {User}.[RegistrationDate] < @EndDate
group by {UserStatus}.[Label]
Hi OsCaR_,
This is not how SQL works.
The Where clause is itself like an IF.
Its conditions are evaluated for each row returned from the FROM and JOINs clauses. The final result of its conditions will define if the row stays or not in the result.
This means that if put something in the form (...) AND (...), both sides of the AND must return true to the WHERE clause to return true to the current row (and the row to stay in results), while if you have (...) OR (...), if any side is true, the final result of the WHERE to the current row will be true and the row stays in the results.
I recommend you to study SQL as this is something OutSystems uses to access databases, but it is an entirely different "language".
Cheers
Thanks for the detailed explanation! But so the aggregate filters also work like in SQL?
For example, in the Users screen, there is a combo box where the user can select "active" or "inactive" or "all status" to return only that users. The combo box is a special list with special variable SelectedStatus and with these properties:
Value 1: -1
Option 1: - All Status -
Value 2: 1Option 2: Active
Value 3: 0
Option 3: Inactive
For this to work I have this filter in the aggregate
User.Status = IntegerToBoolean(SelectedStatus) or SelectedStatus = -1
So if the User.Status is "1" the first part of the condition is true and the second part is false, TRUE or False = TRUE, so this current row stays in the resulting output.
But also If the User.Status is "-1" the first part is False but the other part is "True", False or True = True, so the current row also stays in the output result.
So it seems like this that the result is always the same, that is, the current row is always stored in the output result. Or if one of the conditions is true the other is ignored? For example if the User.Status = IntegerToBoolean(SelectedStatus) is True the SelectedStatus = -1 is ignored for the current row?
Hello OsCaR_
a) An aggregate is only a visual tool for you to fetch data from a database. In the back, what you configure will become an SQL query. So, yes, it works pretty much the same as the SQL tool, with the exception that the system can "optimize" the aggregate, while it can't do the same with the SQL tool.
b) Lates take a look into the table below with possible values of status (in the row) and the external variable (-1, 0, 1)
So, your filter will work as expected.You can think in the "or SelectedStatus = -1" part of the filter as a switch. It turns on and off the other part of the filter "User.Status = IntegerToBoolean(SelectedStatus)".
So, if the SelectedStatus is -1, then the "SelectedStatus = -1" condition will be true for all the rows (the value of SelectedStatus variable does not depend on anything in the row). As it is in one of the sides of a (...) OR (...) expression, and (True / False) OR True will always be true, you get what you want that is all rows in the set will be present in the result.
When the SelectedStatus is DIFFERENT from -1, that part of the condition will be False for all the rows (for the same reason stated above, its value is not dependent on the row), and now, the expression will be (True / False) OR False, and so, the first part of the expression now determines if the row will stay or not in the results.
If the value in the SelectedStatus is 1, it will be converted to True, and now the "User.Status = True" will return true only if the status is true. For lines whose status is False, the result will be false, and the result of the expression will be given by "False OR False", that is False (and the row does not enter). When the SelectedStatus is 0, the result will be the opposite and only rows with Status False will stay in the results.
Hipe this helps.