• English

      • SQL Collection

      Collection: SQLCommunity Edition+

      Introduction

      The SQL collection provides a powerful method for retrieving data using SQL queries. By extracting data fields through SQL queries and configuring the associated field metadata, users can utilize these fields as though they were working with a standard table. This feature is particularly beneficial for scenarios involving complex join queries, statistical analysis, and more.

      User Manual

      Creating a New SQL Collection

      1. Enter your SQL query in the provided input box and click Execute. The system will analyze the query to determine the tables and fields involved, automatically extracting the relevant field metadata from the source tables.
      1. If the system's analysis of the source tables and fields is incorrect, you can manually select the appropriate tables and fields to ensure the correct metadata is used. Start by selecting the source table, then choose the corresponding fields in the field source section below.
      1. For fields that do not have a direct source, the system will infer the field type based on the data type. If this inference is incorrect, you can manually select the proper field type.
      1. As you configure each field, you can preview its display in the preview area, allowing you to see the immediate impact of your settings.
      1. After you have completed the configuration and confirmed that everything is correct, click the Confirm button below the SQL input box to finalize the submission.

      Editing

      1. If you need to modify the SQL query, click the Edit button to directly alter the SQL statement and reconfigure the fields as needed.

      2. To adjust the field metadata, use the Configure Fields option, which allows you to update the field settings just as you would for a regular table.

      Synchronization

      If the SQL query remains unchanged but the underlying database table structure has been modified, you can synchronize and reconfigure the fields by selecting Configure Fields - Sync from Database.

      SQL Collection vs. Linked Database Views

      Template TypeBest Suited ForImplementation MethodSupport for CRUD Operations
      SQLSimple models, lightweight use cases
      Limited interaction with the database
      Avoiding maintenance of views
      Prefer UI-driven operations      		SQL subqueryNot Supported
      Connect to database viewComplex models
      Requires database interaction
      Data modification needed
      Requires stronger and more stable database support      		Database viewPartially Supported
      Warning

      When using SQL collection, be sure to select tables that are manageable within NocoBase. Using tables from the same database that are not connected to NocoBase may lead to inaccurate SQL query parsing. If this is a concern, consider creating and linking to a view.