Populating Scaffolding Select Lists Using SQL Queries
Overview
Populating a Scaffolding List Data macro with items from a database table is most easily done using the DB Options macro. The limitation is that it assumes you want all items from a single table. If that is not the case, there are two solutions:
Option 1: Create a Database View
If you have ownership of the underlying database, you can create a custom view to expose only the data you wish to appear in the list. This will put the logic on the backend and allow you to use the DB Options macro.
Option 2: Dynamically Populate Options
DB Options is just shorthand for generating List Option macros from database results. You can use the example below to set up a report within a List Data macro. The example shows how the query can both use any SQL syntax and also inject dynamic content.
{list-data:name=user}
{report-list:injected=true}
{sql-reporter:ds=myDB}
select u.user_id, u.first_name || ' ' || u.last_name user_name
from user u
join group g on u.group_id = g.id
where g.name = '%page:title%'
{sql-reporter}
{report-body:injected=true}
{list-option:value=%row:user_id%|label=%row:user_name%}
{list-option}
{report-body}
{report-list}
{list-data}