Using Subselects with CQL in CMDBuilds “filter” type properties

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.