ORM: Select Exists

[a.slyzhko]

Hello EspoCRM team,

I’d like to request the ability to generate a top-level SELECT EXISTS(...) query via the ORM. Currently, you can use WHERE EXISTS(…) within a query, but there’s no way to build a statement that directly returns a Boolean flag, for example:

SELECT EXISTS (
  SELECT 1
  FROM nomenclature n
  WHERE n.id = nc.nomenclature_id
) AS hasNomenclature
FROM nomenclature_characteristic nc
WHERE nc.deleted = 0;

A dedicated ORM method would allow us to:

1. Return true/false directly from the database without fetching full recordsets.
2. Simplify checks for related-data existence and could be more performant in many scenarios.

Thank you for considering this feature!

[yuri]

It boils down to supporting sub-queries in expressions. It's still not implemented because it's a pandora-box like feature. Currently, expressions are represented internally as strings. To support SELECT queries as expressions we would need to serialize them. Another issue, is access control for expressions defined by users.

You can define a custom function converter to build a needed expression for your particular case. https://docs.espocrm.com/development/metadata/app-orm/