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.

I am asking you for a donation...

You liked the content or this article has helped and reduced the amount of time you have struggled with this issue? Please donate a few bucks so I can keep going with my troubleshooting adventure :-)

Categories: Databases