Data Source - From Database View

Overview

The data source from view queries data from other data tables by setting an SQL query statement, where you can use data processing functions provided by the database such as aggregate functions. Informat automatically creates a database view from the query statement. The columns returned by the query statement should correspond one-to-one with the field identifiers of the data table, and an additional varchar type unique identifier named id needs to be returned.

Views can be used to implement data statistics and other functions conveniently. It should be noted that if the query process takes a long time, you can enable the data source's Save as Materialized View feature. When enabled, Informat will create a materialized view.

Although accessing the data stored in a materialized view is often faster than directly accessing the underlying tables or accessing through a view, the data is not always up-to-date. If you need to update the data in the materialized view, you need to actively call the informat.table.refreshDataSource() method.

Simple Query Example

Suppose there is a data table User Information (identifier: userInfo) in the system with the following structure:

Field Identifier	Name
id	Primary Key
name	Name
countryName	Country

Now we need to display the summary of the number of people in each country. We should create a data table named "Employee Country Summary" with the identifier "countryStat" from a database view. The fields should be configured as follows:

Field Platform ID	Field Identifier	Name	Type
lsgfpix9hltgp	countryName	Country Name	Single Text
m6dlm2bci30p0	count	Number of People	Integer

Identifier Mode

SQL query statement is as follows:

select 
    count(name) as count,
    country_name
    from user_info
    group by country_name;

Notes

• Table Name

  Must follow the underscore notation naming convention, which converts uppercase letters in the table identifier to underscores.

• Field Name

  Must follow the underscore notation naming convention, which converts uppercase letters in the field identifier to underscores.

Native SQL Mode

select 
    count(name) as m6dlm2bci30p0,
    country_name as lsgfpix9hltgp
    from v_{appId}_user_info
    group by country_name;

Notes

• appid: Replace the appId in v_{appId}_user_info
• Return fields are the IDs of the fields in this table, not field identifiers

Related List Field Query Example

Suppose there is a data table Country Information (identifier: countryInfo) in the system with the following structure:

Field Identifier	Name
id	Primary Key
name	Country Name

The User Information table in the above example adds a new field Traveled Country List of type Related List, associated with the Country Information table:

Field Identifier	Name
id	Primary Key
name	Name
countryName	Country
traveledCountryList	Traveled Country List

Now we need to display the summary of each country by the number of travelers. We should create a data table named "Country Travel Summary" with the identifier "countryTraveledStat" from a database view. The fields should be configured as follows:

Field ID	Field Identifier	Name	Type
wpzqcrrnu89xt	countryName	Country Name	Single Text
n9a96iazmqp0h	count	Number of Travelers	Integer

Identifier Mode

SQL query statement is as follows:

select 
    b.name  as country_name,
    count(1) as count
    from user_info$traveled_country_list a
    inner join country_info b on a. relation_id=b.id
    group by b.name;

Usage Instructions

The user_info$traveled_country_list table is the adjacency list of the Traveled Country List related list field in the User Information table. The query format for the adjacency list: table identifier$field identifier (also needs to convert uppercase letters to underscores).

The structure of the adjacency list is as follows:

Field	Type	Description
id	int	Main table record ID
relation_id	varchar	Sub-table record ID
index	int	Sorting
create_time	date	Creation Time

Notes

The columns returned by the SQL query must include all fields defined in the Data Table Field Settings. When the source data table or fields that the view depends on are deleted or changed, you need to first delete the references in the view definition SQL and then modify the source data table definition. Otherwise, the application release will fail.

Native SQL Mode

SQL query statement is as follows:

select 
    b.name  as wpzqcrrnu89xt,
    count(1) as n9a96iazmqp0h
    from z_zj7sihq5gkqm1_ezfm44e23eg35_wbwhrqd7f1g5v a
    inner join v_{appId}_country_info b on a. relation_id=b.id
    group by b.name;

Usage Instructions

• Return fields are the IDs of the fields in this table, not identifiers
• The z_zj7sihq5gkqm1_ezfm44e23eg35_wbwhrqd7f1g5v table is the actual table name of the adjacency list of the traveledCountryList related list field in the User Information table. Format: z_{appid}{tableId}
• tableId refers to the ID of the User Information table, not the identifier
• fieldId refers to the ID of the traveledCountryList related list field in the User Information table, not the identifier

The structure of the adjacency list is as follows:

Field	Type	Description
id	int	Main table record ID
relation_id	varchar	Sub-table record ID
index	int	Sorting
create_time	date	Creation Time