CakePHP 4 and using SQL Materialized views in PostGreSQL


I just hit an interesting wall. We are in the process of developing a pretty reasonable sized application that has a ton of historical data. The plan is to use materialized views and do nightly/weekly refreshes as applicable to help reduce the impact on the server for long date range reports.

I know that in Cake 2, I could just define a new view by creating the appropriate model and the running with it.

However, with Cake 4, I am getting an error when I try to use it. Specifically the error that gets raised is:
“Cannot describe v_12month_summary. It has 0 columns.”

Is there a way to use materialized, or even regular views, with CakePHP 4?


Not sure if it will help, but many times I use the PDO instance directly for certain queries, see Need help to create Reports