Raw-SQL views

A raw-SQL view is the escape hatch in the view subsystem: a view whose data source is an admin/package-authored SELECT statement instead of the single-entity engine. Use it for cross-entity reporting and master-detail child grids that the entity-driven engine can't express (arbitrary joins, _lng companions, aggregates).

A view is raw when syntec_view.sql is non-null (and syntec_entity_id is null). The engine wraps the authored SQL as an inner subquery and layers paging, filtering, sort, global search, and the optional count on top — all parameterised — so the inner SQL is never parsed or interpolated (RawViewQuery). The SQL is trusted (it is the source of truth for what it exposes); only the parameters layered on top are made injection-safe. Who may open the view is still gated by syntec_view_role.

Declaring columns

A raw view's columns live in syntec_view_column like any view, except each row carries its type in data_type (varchar, integer, boolean, decimal, reference, text) instead of pointing at a syntec_field. The col_key is the SQL output alias; labels come from syntec_view_column_lng. Only declared aliases are selectable, filterable, and sortable — anything else is dropped, so filters and sort are safe even though the inner SQL is arbitrary.

System placeholders

The engine binds a small fixed vocabulary of named placeholders only when they appear in the SQL — bound as real parameters, never interpolated:

Using a raw-SQL view as a form subview

A raw-SQL view can back a master-detail child grid in a form, scoped to the record being edited — the One equivalent of Syntec Core's stored-query lists with :guid.

Scope with :parent_guid. Unlike an entity view (which scopes via parent_field — the engine resolves the parent guid to its row id and adds a parent_field = id filter), a raw view scopes itself in its own SQL: join to the parent table and compare the guid string.

SELECT c.firstname, c.lastname
FROM   syntec_data_child_test c
JOIN   syntec_data_column_test p ON c.syntec_data_column_test_id = p.id
WHERE  p.guid = :parent_guid AND c.status = :active

Gotcha: raw views bind the parent guid (a uuid string), not an integer id. Your WHERE must compare against a guid column (p.guid = :parent_guid), reached by joining to the parent — not against an id.

Wire it to the form. Add an entity_component to the form and point it at the raw view with a source_ref property (= the view cid) — the same component that embeds an entity child grid:

syntec_form_component        component_type = 'entity_component'
syntec_form_component_property  key = 'source_ref'  value = '<raw-view-cid>'

No admin or SDK change is needed: the embedded grid always sends the parent record's parentGuid in the query body, and RecordApi::queryView() forwards it into RawViewQuery, which binds :parent_guid. For a top-level view or an unsaved parent the guid is '', so p.guid = '' matches no rows and the subview is correctly empty (never an error).

Read-only. A raw view has no entity and no open-form, so it is a reporting grid — there is no row create/edit/delete through it.

Worked example (the local demo package)

The data_child_test_sql raw view above is wired as a Raw SQL subview tab on data_column_test_form (component entity_component, source_ref = data_child_test_sql). Opening a Data-column-test record lists exactly that record's children, scoped by :parent_guid. It ships in the local (demo) package — provision it with syntec:package:import local.