Hello and welcome to data queries with OutSystems, where we're going to
cover the ways you can retrieve data from a database and look into what is
the format that this data is made available to your code. Now, there's two
ways, two different statements, whereby you can retrieve data from the database. It's
the Aggregates and the SQL queries. The Aggregates are the preferred way to
retrieve data from the database, as they require no SQL knowledge on your part
they also scale very well with the data, as you develop your data, as you increase
for example, the number of attributes in an entity and it also has the advantage
that, since you will be working with the UI, the compiler can look at the context
whereby this query's being used and optimize the actual compiled code for
you. Then there's the SQL queries: now you should leave these for more advanced
scenarios, where you want to write something very, very specific and you
want to access certain database-specific features, likewise, this is the way to go
if you need to write a statement, a SQL statement that is not the query.
So for example, an insert, update and delete... and we will have a slide on that. Now
regardless of which of the flavors of queries you select, the output of a
query is actually similar, it will be made up of a list, which is the result
set of the query, plus a count, which is an integer that indicates the number of
records that query's returning. Furthermore, it's important for you to
know, that looking at the right-hand side of the slide, that inside the list you have
several runtime properties, that appear here as green squares, the most
important of which is the Current. Now, the Current is one atom of the type that
the query is outputting, so in this case the query's clearly fetching clients, so
the atom will be a client with its attributes. Now immediately after a query
is performed, this Current will be pointing at the very first record in the
result set, should the query not return any results, this "client"
will have the default values for each of its attributes. The most important
thing to note though, is if you access this Current inside an iteration and
you can see a For Each node at the very bottom of the slide, this Current
will actually be moving across the list that was returned. So accessing the
Current, inside an iteration, is the way for a developer, for example, to process
every single record. The other properties are less important and you can look them
up in the platform documentation. Let's look then, in more detail, at the
Aggregate statement. The Aggregates are very convenient because they allow you
to visually design your query and test it, even before publishing your
module, you set an Aggregate up, by configuring it in four tabs, which is the
sources, the filters, the sorting and the test values.
Let's look at the sources first, now you can have a very simple query that only
deals with one particular entity but Aggregates support you selecting more than
one entity, you can see here on the screen shot in this slide, that we have
two entities, a client and a product, that are related somehow via a join. Let's
look then at some of the flavors, well actually the three types of flavors, that
you can create joins inside Aggregates and for that purpose, we're going to be
working with two different tables, two different entities. So you have the
issues entity on the left-hand side of your screen and engineers entity on the
right-hand side of the screen and you can see that, issues are handled by
specific engineers. Let's see how we can retrieve information, using these two
entities and the join types. If you opt to have a join of the kind "Only With", in
this particular case, we're retrieving issues only with engineers, effectively
you'll only get rows, whereby the issue has been assigned to an engineer: if the
issue is
unassigned, this will not be in the result set and this is the equivalent to
a SQL Inner Join. If you opt for a "With or Without" kind of join, then you
will get all the rows from the left entity, even if there is no matching in
the right entity, so over here we can see on the left-hand side issues, regardless
of them having an engineer or not and on the right-hand side
we're seeing the query the other way around: we're actually fetching the
engineers, even if they have no issues associated with them. So in a sense this
is the equivalent to a SQL Left Join. Finally, you have the most permissive
join, the "With", in this case, you will return all rows from both entities even
when there isn't a match and you can see that conveyed in the screenshot on the
right-hand side. Now, on the output of your Aggregates, you will get all of the
entities that make up the sources for it, so if you have the product and the
client, you'll have product and client, if you desire to have one or more extra
attributes that are computed, you can create ad-hoc attributes, and over here
you see a few screenshots that tell you how you could calculate the total price
of a particular product, having in mind, the amount that you're actually
purchasing. These extra ad-hoc attributes will show up, in the output of the query
next to the entities that are the sources of the query. It's important to
note that when you're designing an Aggregate, you have the preview always on
your screen and you're able to hide and show columns on that preview.
Please note that this will not influence the actual output, this is just for
preview purposes only.
The actual output is then upon compilation, optimized to the actual
columns that the remainder of your code uses from the output of this Aggregate
and this is a very handy and very powerful feature in the OutSystems
platform, that is exclusive to the Aggregates. One last point relating to the
sources and therefore the outputs of the queries, is the
capability to aggregate the values, so you're actually able to use aggregate
functions, like sum, average, maximum, minimum and count on your results set.
Please note that as soon as you start to Aggregate functions, as it would be the
case if you were to use a group by on a SQL, only the aggregated attributes
will appear in the output record of the query, so in this example, for the result
of this particular query, this particular Aggregate, will just have one column
which is count and you can see that, because it's highlighted in blue. Moving
swiftly on to filters, this is very simple, this is effectively the "Where"
clauses, where you're able to filter the results set having in mind, for example,
other variables that are in the scope where this query is being executed. So
right here, what we're seeing is: we're retrieving clients, that have an ID
that's the same of some variable on the outside, called InputClientId. For
preview purposes, these inputs, that you can use in filter, they show up in the
test values tab, allowing you, at design time, to try out what is the
result set of this Aggregate depending on input values that you put into these
test values. In closing, all that remains to be talked about, is the sorting. You have in
the Aggregates interface a dedicated tab for that and you can select as many
attributes as you want, the order is obviously important, it can decide if it's
ascending or descending. If you particularly need a dynamic sorting, you
specify to the query a text that dictates what column it should sort by.
Please note that the syntax of this text is very specific and it's highlighted on
the left-hand side of the slide.
Let's look now at SQL queries, also sometimes called Advanced queries. So
what do SQL queries look like? As you can see the interface for SQL queries
is much simpler and it's basically these three panels. The way to go about writing
a SQL queries is: you specify
which input parameters this query has access, you then specify what is the
record format that should be in the output of it and you can select entities
and structures, combine them to create this output structure and then it's up
to you to write the SQL of the query. The editor provides some guidance, by
doing some SQL syntax checking and highlighting of colors, as you type in
your SQL.
So to recap, unlike Aggregates, SQL queries are actually black boxes, so they
don't see anything on the outside that you don't pass in, as an explicit
parameter, you need to specify the types of these parameters, you also need to
specify the entities (and structures) that make up the output and in the SQL
panel, you can write freeform SQL statements, having in mind that you need
to place your entities inside curly brackets, your attributes inside square
brackets and any parameters preceded by the "@" character. The second tab allows
you to specify input values, to test your query, similarly to the Aggregates. On the
third tab, test output, when you perform the test you will be able to see the
rows that were returned and finally on the fourth tab, if you so interested, you
can look at the actual SQL code that was generated by the compiler. One final
usage for SQL statements, is to write non query SQL statements, so if you
need to do a delete or an update or an insert and you do not want to use an
entity action, you will need to use a SQL statement. And even though these
statements don't normally produce any output, you'll still need to specify some
dummy output entity or structure. In closing, let's just look at how you can
convey multiple entities or structures together into a single record.
Here's a slide that you might remember from the data modeling lesson, that just
shows and recaps, how the output of a query looks
like, once you use it in future statements, so you have the queries, got
an output, which is a list and it has the Current in this particular case, which is
only got a customer inside, so a single record, however most queries actually
return more than one entity, when for example, you perform a join, as we
mentioned. In this case the Query.List will have, side-by-side, the entities that
the query has returned, and any extra columns in case you created ad-hoc columns. In the
example shown, you can see that we're creating an output variable of the
action GetAllMoviesInCinemas, based on the return type of GetMoviesWithCinemas.
Notice how the data type picker, suggests the outputs of queries that
exist, within this scope, to create a type, that is a combination of Cinema, Movie
and MovieSession. The key point that I want to make here, is that, we're not
limited to queries to have access to lists of records, including of multi
entity records. You can create variables and you can specify a complex type for
those variables. In fact, if rather than taking a suggestion, you select Data Type
Editor, you will see this dialog and you can add whatever entity, whatever
structure or whatever individual basic type, to make up the record format that
this particular variable has. In this particular case, I added an extra text
attribute, that I called ExtraTextAttribute to the output that was already
in the AllMoviesInCinemas variable. And that's basically it for data queries.
See you guys in the next lesson!