Joomla sql Form Field Type
The sql form field type provides a drop down list of entries obtained by running a query on the Joomla database. The sql field extends list field type.
XML Field Definition
<field
name="title"
type="sql"
label="Select article"
query="SELECT id AS value, title FROM #__content" />
An AS clause has been used in this example because the _content table does not have a column called 'value'. Alternatively, you can use a key_field attribute to define the column to be used instead of 'value':
<field
name="title"
type="sql"
default="10"
label="Select an article"
query="SELECT id, title FROM #__content"
key_field="id"
value_field="title"
multiple="true" />
Additional Parameters
query: It is the SQL query which will provide the data for the dropdown list. The query must return two columns; one called 'value' (unless overridden by the key_field attribute) which will hold the values of the list items; the other called the same as the value of the name attribute (unless overridden by the value_field attribute) containing the text to be shown in the dropdown list.
key_field: It is the name of the column that will contain values for the options in the fropdown.
value_field: It is the name of the column that will contain text to be shown to the user in the dropdown list.
translate: It will translate the output of the value_field if set to true. It defaults to false.
SQL Parameters
Instead of the query attribute, you can also provide SQL parameters to build the query.
sql_select: It is the SELECT clause of the SQL statement. Only one such clause is permitted.
sql_from: It is the FROM clause of the SQL statement.
sql_where: It is the WHERE clause of the SQL statement. Only one such clause is permitted.
sql_order: It is the ORDER BY clause of the SQL statement.
sql_join: It is the LEFT JOIN clause of the SQL statement. Only one such clause is permitted.
sql_group: It is the GROUP BY clause of the SQL statement.
sql_filter: It filters the list by the value of another field. A field name or a comma-separated list of field names can be given. The field names must correspond to column names in the database table being queried.
sql_default_{FIELD_NAME}: It is the default value used by the sql_filter attribute when the value of the {FIELD_NAME} filter has not been set.