Custom Database Views

[SoBeGuy]

I need to create a view in MySQL that can be accessed as a readonly entity in Espo. The view definition is extremely complex and cannot be created using Espo's filters. Is there any built-in functionality for this? If not, what would be the best approach? I imagine I could create an entity in Espo containing the same fields as the view, then replace the entity's table in the database with a view of the same name. From Espo's perspective, it would look like a regular table but the records would be populated dynamically. Is this correct, or is there a better way to accomplish this?

[dimyy]

It's not good idea replacing tables, bicose any rebuild corrupt this construction. May be you should look to selectBySql / createFromSql ? It ssems more applicable

[SoBeGuy]

dimyy, selectBySql sounds like exactly what I'm looking for. However, I can't find any information about this in the documentation. Can you point me in the right direction?

[yuri]

You can use SqlExecutor https://github.com/espocrm/espocrm/b...nager.php#L423. The source code is documented and type hinted, it can be used w/o a separate documentation.

[yuri]

You will get PDOStatement. Then you can run fetch or fetchAll on it. You will get array data (not Entity).

[yuri]

You can also create a collection from an SQL: https://github.com/espocrm/espocrm/b...actory.php#L58

All column names in an SQL result should correspond to entity attribute names.

IMPORTANT: Beware of a possible SQL injection vulnerability. Better to avoid using raw SQLs at all.

[SoBeGuy]

yuri Thank you!