MySQL query A special field type for organizing more complex relationships between entities. Rukovoditel - Free Project Management Software

MySQL query New feature in 2.3

A special field type for organizing more complex relationships between entities.

The field type "MySQL query" makes it possible to build a query to an entity (table) according to a given condition. The query is built automatically, you only need to specify the parameters by which the data will be sampled.

Field settings

Select from entity
Specify the entity to which the MySQL query will be built.

Select field
Specify the field to be selected in the query. For example: [12], where 12 is the id of the numeric field. You can select any field types and use MySQL functions.
Note: the query must return only one value.

Examples of standard queries
Maximum field value: MAX ([12])
The sum of all values in the column: SUM ([12])
Total number of entries: COUNT (*)
A query with the condition: IF ([12]> 100, 'Yes', 'No')

Condition
Using the field IDs, specify the conditions for what should be selected in the query.

Examples of standard conditions
Comparison of one field: [50] = [12].
Comparison across several fields: [50] = [12] AND [51] = [13]
Example of a MySQL condition IN: [50] IN (10,20,30)
Condition by field type "Entity": [423]=e.id
[423] - field type Entity linked to Entity A
e.id - ID of item from Entity A

Internal fields
In the condition you can apply internal fields. If the field is used without a prefix, the value is taken from the entity specified in the "Select from entity" field. If you add the prefix e. (for example, e.id), the value is taken from the current entity in the main query.
id - record identifier
parent_item_id - parent record id
date_added - the date the record was added (Unix Timestamp)
created_by - identifier of the user who created the record