CMDBuild supports the usage of SQL queries inside the “filter” options if the type is a REFERENCE.
Given:
- A lookup table named “Software Type” [{Code: DBMS}, {Code: App}]
- A class Application [{Code: MySQL, Type: DBMS}, {Code: PostgreSQL, Type: DBMS}, {Code: Tomcat, Type: App}]; “Type” references the lookup table “Software Type”
- A class ApplicationRelease [{Code: “MySQL 5.0”, Application: MySQL}, {Code: “PostgreSQL 9.x”, Application “PostgreSQL”}, {Code: “Tomcat 7.0”, Application “Tomcat”}]; “Application” is a foreign key to the Application class.
- A class DbmsInstance with a reference/foreign key to ApplicationRelease
Target:
- By adding/modifying a DbmsInstance, only ApplicationReleases shall be shown which are of type “DBMS”.
How to solve this by using a subselect:
from ApplicationRelease where Id (/( SELECT "Id" FROM "ApplicationRelease" WHERE "Application" IN (SELECT "Id" FROM "Application" WHERE "Type" = (SELECT "Id" FROM "LookUp" WHERE "Code"='DBMS') ) )/)
Please note, that every referenced column must be quoted with double quotes. Every value of WHERE clause must be quoted using single quotes only!
The (/(…)/) construct introduces plain SQL.