Queries

Introduction

To Code provides by default a comprehensive overview of the CRUD operations for each entity within our Data Model. CRUD stands for Create, Read, Update, and Delete, which are the four basic operations for managing data.

Also To Code provides the ability to define a Query based the defined entities and relations by definining the criteria, linked entities and expressions.

A query is a prepared definition that you can save and reuse. In other words, a query is a set of read statements that can be executed as a single unit. Query help in performing operations such as data retrieval, data manipulation, and complex calculations while providing a way to encapsulate logic within the Data Model.

Syntax

It uses a join to relate posts with their associated tags, and filters results based on dynamically provided tag IDs.

LogicalId

{
  "LogicalId": "GetPostsByTag",
}

• Description: the LogicalId is unique, represent the name of defined Queries.

Description

{
  "Description": "GetPostsByTag",
}

• Description: the field serve as descriptors for the query, useful for referencing and understanding the purpose of the query.

SelectFromEntityTypeLogicalId

{
    "SelectFromEntityTypeLogicalId": "Post",
}

• Description: This specifies that the primary entity being queried is the root entity.

PropertySet

{
    "PropertySet": {
        "PropertyCollection": null,
        "AllProperties": true
    },
}

• Description: Indicates that all properties should be retrieved. No specific subset list is provided.

• Another option when PropertyCollection is not null as following:

{
    "PropertySet": {
        "PropertyCollection": 
        {
            {
                PropertyLogicalId = "Title"
            },
            {
                PropertyLogicalId = "Content"
            }
        }
        "AllProperties": false
    },
}

• Description: can be specific which properties to be selected.

Distinct

{
    "Distinct": true
}

• Description: Ensures that the query returns unique records, eliminating duplicate root entities in the results.

LinkEntityCollection

{
    {
      "LinkFromEntityLogicalId": "Post",
      "LinkToEntityLogicalId": "Tag",
      "RelationshipLogicalId": "Post_Tags",
      "JoinProperties": null,
      "ToEntityAlias": "PostTags",
      "JoinOperator": "Inner",
      "PropertySet": {
        "PropertyCollection": null,
        "AllProperties": true
      },
      "LinkCriteria": {
        "ConditionExpressions": [
          {
            "Parameters": [
              {
                "Value": "@TagId"
              }
            ],
            "Aggregate": "None",
            "PropertyLogicalId": "Id",
            "ConditionOperator": "In",
            "Dynamic": true,
            "Alias": null,
            "AllowNull": false,
            "LogicalOperator": "And"
          }
        ],
        "FilterExpressions": null,
        "ReturnSingleRecord": false,
        "LogicalOperator": "And",
        "Criteria": null
      },
      "LinkEntities": null,
      "OrderCollection": null,
      "GroupBy": null,
      "Criteria": null,
      "ReturnSingleRecord": false
    }
 
}

This section describes how related entities are used within the query.

• LinkFromEntityLogicalId

  • Description: Denotes the primary entity from which the join originates.

• LinkToEntityLogicalId

  • Description: Specifies the entity to be joined with, which is "Tag" in this case.

• RelationshipLogicalId

  • Description: The logical identifier that describes the relationship between "Post" and "Tag" entities.

• JoinOperator

  • Description: An join operation ensures that only posts having at least one related tag (matching the criteria) are returned, can be the following values:
    • Inner
    • LeftOuter
    • RightOuter

• ToEntityAlias

  • Description: An alias for the second entity within the context of linking, used to clearly reference this link.

PropertySet in LinkEntity

{
    "PropertySet": {
        "PropertyCollection": null,
        "AllProperties": true
    },
}

• Description: Indicates that all properties of the second entity should be retrieved during the join.
• Refer to PropertySet section.

LinkCriteria

{
    LinkCriteria": {
        "ConditionExpressions": [
          {
            "Parameters": [
              {
                "Value": "@TagId"
              }
            ],
            "Aggregate": "None",
            "PropertyLogicalId": "Id",
            "ConditionOperator": "In",
            "Dynamic": true,
            "Alias": null,
            "AllowNull": false,
            "LogicalOperator": "And"
          }
        ],
        "FilterExpressions": null,
        "LogicalOperator": "And",
      }
}

• ConditionExpressions:

  • Parameters: to be define the values compared with selected PropertyLogicalId.
  • Aggregate: indicate the defined function in SQL, can be:
    • Count: Calculates the number of elements in a dataset or list.
    • Sum: Adds up all the numerical values in a dataset or list.
    • Sub: Subtracts one numerical value from another or subtracts multiple values from an initial value.
    • Mlp: Typically refers to multiplication, multiplying two or more values.
    • Div: Divides one numerical value by another.
    • Avg: Computes the average (mean) of a set of numbers.
    • Min: Finds the minimum value in a dataset or list.
    • Max: Finds the maximum value in a dataset or list.
    • Day: Extracts or works with the day from a date.
    • Month: Extracts or works with the month from a date.
    • Year: Extracts or works with the year from a date.
    • Concat: Concatenates elements, typically strings, end-to-end.
    • ConcatWithComma: Joins elements using a comma as the separator.
    • ConcatWithSpace: Joins elements using a space as the separator.
    • ConcatWithDashe: Joins elements using a dash (hyphen) as the separator.
    • ConcatWithSlashe: Joins elements using a slash as the separator.
  • PropertyLogicalId: defined the selected Property of Entity to be compared.
  • ConditionOperator: indicate the defined condition operator in SQL, can be:
    • In: Checks if a value exists within a set of values.
    • BeginsWith: Checks if a string starts with a specified substring.
    • Between: Checks if a value falls within a certain range (inclusive).
    • Contains: Checks if a string contains a specific substring.
    • DoesNotBeginWith: Checks if a string does not start with a specified substring.
    • DoesNotContain: Checks if a string does not contain a specific substring.
    • DoesNotEndWith: Checks if a string does not end with a specified substring.
    • EndsWith: Checks if a string ends with a specified substring.
    • Equal: Checks if two values are equal.
    • GreaterEqual: Checks if a value is greater than or equal to another value.
    • GreaterThan: Checks if a value is greater than another value.
    • LessEqual: Checks if a value is less than or equal to another value.
    • LessThan: Checks if a value is less than another value.
    • Like: Checks if a string matches a specified pattern (often using wildcards).
    • NotBetween: Checks if a value is not within a certain range.
    • NotEqual: Checks if two values are not equal.
    • NotIn: Checks if a value does not exist within a set of values.
    • NotLike: Checks if a string does not match a specified pattern.
    • NotNull: Checks if a value is not null.
    • Null: Checks if a value is null.
  • Dynamic: define a boolean related to the parameter to be static value or variable.
  • AllowNull: a boolean value that checking to include the nullable values in comparison operation or not.
  • LogicalOperator: define the logical operator between the parameter (And, Or).
  • FilterExpressions: nested conditions (ConditionExpressions, FilterExpressions, LogicalOperator).

• Description: This criterion specifies the read statements that the query should be included.

OrderCollection

{
    OrderCollection:
    [
        {
            OrderType: `Ascending`,
            Property: 
            {
                EntityLogicalId = "Post",
                PropertyLogicalId = "Id"
            }
        }
    ]
}

• Description: indicate the properties to be used to sort the returned results depend on selected direction.

GroupBy

{
    GroupBy:
    [
        {
            OrderType: `Ascending`,
            Property: 
            {
                EntityLogicalId = "Post",
                PropertyLogicalId = "Id"
            }
        }
    ]
}

• Description: define the properties to be used to arrange the returned results into groups.

ReturnSingleRecord

{
    ReturnSingleRecord: false
}

• Description: indicate if query return multi record with pagination or it is a simple query that return one item only.

Criteria

{
    Criteria:
    {
        "ConditionExpressions": [
          {
            "Parameters": [
              {
                "Value": "@TagId"
              }
            ],
            "Aggregate": "None",
            "PropertyLogicalId": "Id",
            "ConditionOperator": "In",
            "Dynamic": true,
            "Alias": null,
            "AllowNull": false,
            "LogicalOperator": "And"
          }
        ],
        "FilterExpressions": null,
        "LogicalOperator": "And",
    }    
}

• Description: indicate epxression used to filter data.
• Refer to LinkCriteria section.

Expressions

{
     Expressions: [
        {
            AdditionalExpression:null,
            Alias:"PostCount",
            ExpressedProperties: [
                {
                    EntityLogicalId: "Id",
                    PropertyLogicalId = "Post"
                }
            ] ,
            ExpressionFunction: `Count`
        }
    ]
},

Expressions:

• Alias: define the alias name of the property.

• ExpressedProperties: Array of selected Entity Property.

  • EntityLogicalId: indicate the target entity.
  • PropertyLogicalId: indicate the property of target entity.

• ExpressionFunction: indicate the defined function in SQL, can be:

  • Count: Calculates the number of elements in a dataset or list.
  • Sum: Adds up all the numerical values in a dataset or list.
  • Sub: Subtracts one numerical value from another or subtracts multiple values from an initial value.
  • Mlp: Typically refers to multiplication, multiplying two or more values.
  • Div: Divides one numerical value by another.
  • Avg: Computes the average (mean) of a set of numbers.
  • Min: Finds the minimum value in a dataset or list.
  • Max: Finds the maximum value in a dataset or list.
  • Day: Extracts or works with the day from a date.
  • Month: Extracts or works with the month from a date.
  • Year: Extracts or works with the year from a date.
  • Concat: Concatenates elements, typically strings, end-to-end.
  • ConcatWithComma: Joins elements using a comma as the separator.
  • ConcatWithSpace: Joins elements using a space as the separator.
  • ConcatWithDashe: Joins elements using a dash (hyphen) as the separator.
  • ConcatWithSlashe: Joins elements using a slash as the separator.

• AdditionalExpression: nested expression (Alias, ExpressedProperties, ExpressionFunction, AdditionalExpression).

• Description: indicate epxression used to filter data.

Example

This document describes the setup and functionality of the retrieve query GetPostsByTag configuration used for data retrieval from an entity identified as Post.

{
  "LogicalId": "GetPostsByTag",
  "Description": "GetPostsByTag",
  "SelectFromEntityTypeLogicalId": "Post",
  "PropertySet": {
    "PropertyCollection": null,
    "AllProperties": true
  },
  "Expressions": null,
  "OrderCollection": null,
  "GroupBy": null,
  "Distinct": true,
  "ReturnSingleRecord": null,
  "Criteria": null,
  "LinkEntityCollection": [
    {
      "LinkFromEntityLogicalId": "Post",
      "LinkToEntityLogicalId": "Tag",
      "RelationshipLogicalId": "Post_Tags",
      "JoinProperties": null,
      "ToEntityAlias": "PostTags",
      "JoinOperator": "Inner",
      "PropertySet": {
        "PropertyCollection": null,
        "AllProperties": true
      },
      "LinkCriteria": {
        "ConditionExpressions": [
          {
            "Parameters": [
              {
                "Value": "@TagId"
              }
            ],
            "Aggregate": "None",
            "PropertyLogicalId": "Id",
            "ConditionOperator": "In",
            "Dynamic": true,
            "Alias": null,
            "AllowNull": false,
            "LogicalOperator": "And"
          }
        ],
        "FilterExpressions": null,
        "LogicalOperator": "And"
      },
      "LinkEntities": null,
      "OrderCollection": null,
      "GroupBy": null
    }
  ],
  "ExtensionsData": null
}

The JSON structure describes a query that targets Post entity with join Tag entity.

Output

To Code convert the above json to be SQL stored procedure.

 
Create PROCEDURE [data].[usp_GetPostsByTag]
	@PageNumber INT,
	@PageSize INT,
	@TagId INT
AS
	IF @PageNumber >= 1
	BEGIN
		SET @PageNumber= @PageNumber -1
	END
	ELSE
	BEGIN
	SET @PageNumber= 0
	END
	DECLARE @Curentpage INT = (@PageNumber + 1)
	DECLARE @offset INT=(@PageNumber * @PageSize)
	DECLARE @Total INT=(
		SELECT COUNT(*) FROM
			[data].[Post] AS Post
		JOIN
			[data].[Post_Tags] ON [Post].[Tags] = [Post_Tags].[PostTags]
		JOIN
			 [data].[Tag] AS [PostTags] ON [Post_Tags].[TagId] = [PostTags].[Id]
 
		WHERE
			([PostTags].[Id] IN (@TagId)))
 
	SELECT DISTINCT * FROM ( 
		SELECT 
			@Total 'TotalCount',
			@Curentpage 'Page',
			@PageSize 'PageSize',
			[Post].[Id] 'Id',
			[Post].[Title] 'Title',
			[Post].[Content] 'Content',
			[Post].[AuthorId] 'AuthorId',
			[Post].[Tags] 'Tags',
			[PostTags].[Id] 'PostTagsId',
			[PostTags].[Name] 'PostTagsName'
		FROM
			[data].[Post] AS Post
		JOIN
			[data].[Post_Tags] ON [Post].[Tags] = [Post_Tags].[PostTags]
		JOIN
			 [data].[Tag] AS [PostTags] ON [Post_Tags].[TagId] = [PostTags].[Id]
 
		WHERE
			([PostTags].[Id] IN (@TagId))
		ORDER BY 
			[Post].[Id]
		OFFSET @offset ROWS FETCH NEXT @PageSize ROWS ONLY) q