sql

Actions and views based on SQL

$3

Use this view to create HTML code views of the results of arbitrary SQL queries

1. Create a view of this type.
2. Set output type = Table or JSON to begin with
3. Start by creating your SQL query with no qualifiers (which will come from the URL query state). The preview table should update as you type
4. When you are happy with the SQL query, switch to output type = HTML
5. Create your HTML code, use {{ rows }} to access rows. For instance if your query is an aggregation with a single row result (e.g. SELECT COUNT(*) FROM...), access this with {{ rows[0].count }}, for example

{{ rows[0].count }}

. You can also loop, e.g. {{# for(const row of rows) { }}
6. When you are happy with both you are SQL and HTML code, Think about whether you need any parameters from the state. List these comma-separated, in order and use in the SQL code as $1, $2 etc. Example SQL code: select * from _sc_config where key = $1;

$3

This action allows you to run arbitrary SQL. You specify values from the row that needs to be included in the query using positional parameters $1, $2 etc.

$3

This will give you a Saltcorn "virtual table" based on an SQL
query and specifying result fields (these will be guessed from the query
result, but you need to check and assign a primary key).

Normally you don't need to worry about the where clause when this is
filtered by one of the columns in a view. Your query will be parsed,
and the appropriate where clause will be inserted before the final query
is run.

There are some very specific cases in which you need to include information
about the user in the query in a way that cannot be done in the normal way by state filtering. In this case, you can use string interpolation to include information about the user. For instance:

``
... where baz in (select id from zubs where user_zub = {{ user.myzub }} ) ...
``

User row values used in this way are automatically escaped by the
sqlstring to prevent SQL
injection.