Perform Subqueries

A very powerful technique in SQL is the use of subqueries for asking more complex questions about the data. This example shows how easy it is to generate correlated and uncorrelated subqueries by nesting EntityFunction.

Register an example database with a rich table structure.

Suppose one wanted to look at products that are close to the maximum product price. One might be tempted to look at the maximum product price first by performing an aggregation to then use the result in a query.

The problem with this approach is that whenever you perform two queries, another user of the database might be changing the product prices without warning and your results would be compromised.

A common solution to this problem is to perform these supporting computations inline so that they happen in a single transaction.

The same expression now appears in the body of the EntityFunction expression, so it is guaranteed to be evaluated by the database.

Another common pattern used in SQL is what is commonly referred to as correlated subqueries. In functional programming, this would be equivalent to having nested pure functions where the inner ones depend on the parameters of the outer ones. Construct such an example step by step.

The question you are trying to answer is, What products have the most products within a $15 range of their price? First you need to construct a FilteredEntityClass expression that represents the products with a price within $15 of a value price.

Then you need to count those. You can do that by using the special property "EntityCount".

Now you can add this as a new property to the class of products.

Since you fixed the value of price to 100, this query will actually return the same value of 28 for all entities in "products".

If you want it to return different values, you need to have the value of price depend on the slot of the outer EntityFunction.

As you can see, you now have two nested EntityFunction expressions, and the body of the innermost one contains both parameters.

You can verify that you get different results for each product.

Now get a richer result by using this newly created property to sort the results.