Relational Database Query¶

Emakin employs a proprietary query model for interacting with relational databases, prioritizing simplicity and security. This model, while not SQL, provides a user-friendly interface accessible to both business users and developers. Emakin internally translates this query model into SQL for database execution. Complex SQL concepts such as joins, grouping, and having clauses are handled automatically by the Emakin system based on the provided query model.

Query Expressions¶

Query expressions uniquely identify table fields. They follow the format Field, Relation.Field, or Relation.Relation.Field, avoiding ambiguity.

For example, Customers.Name refers to the Name field within the Customers table. Query expressions are used in both the Columns and Criteria sections of the query model.

Considering a Customers table, the following expressions illustrate how related data can be accessed:

• Name -> Customers.Name
• Orders.Amount -> Orders.Amount
• Orders.Product.Name -> Products.Name
• Orders.Product.Category.Name -> Categories.Name

Expression Functions¶

Beyond basic field references, the query model supports function calls for enhanced data manipulation and aggregation.

Mathematical Functions¶

Column expressions can incorporate mathematical operations:

• Addition: A + B
• Subtraction: A - B
• Multiplication: A * B
• Division: A / B

Note that the interpretation of these mathematical functions is directly translated into SQL, and results might vary slightly depending on the underlying database system.

Value Functions¶

• Now(): Returns the current date and time.

Date and Time Functions¶

• DatePart(part, date): Extracts a specified part from a date value. part can be one of: 'Year', 'Quarter', 'Month', 'Week', 'Day', 'Hour', 'Minute', 'Second'. Example: DatePart('Year', Orders.OrderDate)

• DateDiff(part, date1, date2): Calculates the difference between two dates. part uses the same values as DatePart. Example: DateDiff('Day', Orders.OrderDate, Orders.ShipDate)

• DateTrunc(part, date): Truncates a date value to the specified part. part uses the same values as DatePart. Example: DateTrunc('Month', Orders.OrderDate)

Aggregate Functions¶

Aggregate functions operate on multiple values, returning a single result:

• Count(): Counts the number of rows.
• Sum(): Calculates the sum of values.
• SumDistinct(): Calculates the sum of distinct values.
• Avg(): Calculates the average of values.
• AvgDistinct(): Calculates the average of distinct values.
• Min(): Returns the minimum value.
• Max(): Returns the maximum value.
• StdDev(): Calculates the standard deviation.

Query Model Representation¶

The Emakin query model can be represented using XML or JSON. The form query designer utilizes XML, while scripting functions such as [$Database.GetData](../../../scripting/reference/$Database/GetData.md) employ JSON.

Example (XML):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

<Query>
    <Parameters>
        <TargetSchema>CRM</TargetSchema>
        <TargetTable>Orders</TargetTable>
    </Parameters>
    <Columns>
        <Column Expression="Name"/>
        <Column Expression="Orders.Amount"/>
        <Column Expression="Orders.Product.Name"/>
    </Columns>
    <Where Condition="And">
        <Criteria>
            <Criteria Expression="Orders.Product.Name" Comparison="Equals" Condition="And">
                <Value>Apple</Value>
            </Criteria>
        </Criteria>
    </Where>
    <Order>
        <Order Expression="Orders.Product.Name" Type="Ascending"/>
    </Order>
</Query>

Example (JSON):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

{
    "Parameters": {
        "TargetSchema": "CRM",
        "TargetTable": "Orders"
    },
    "Columns": [
        {
            "Expression": "Name"
        },
        {
            "Expression": "Orders.Amount"
        },
        {
            "Expression": "Orders.Product.Name"
        }
    ],
    "Where": {
        "Condition": "And",
        "Criteria": [
            {
                "Expression": "Orders.Product.Name",
                "Comparison": "Equals",
                "Condition": "And",
                "Value": "Apple"
            }
        ]
    },
    "Order": [
        {
            "Expression": "Orders.Product.Name",
            "Type": "Ascending"
        }
    ]
}

These examples demonstrate how to construct a query to retrieve data from the Orders table within the CRM schema, filtering for orders with products named "Apple," and ordering the results by product name.