I’m getting ready to hire a developer, and have an architecture question.
I’m setting up a Drupal 8 site (D9 soon) with a lot of charts. The charting software requires data in JSON format. The data types are basic — just Booleans, dates, and integers. There are no fields with multiple values.
I plan to use Webform to collect data from users, then run the charts off the Webform Submissions table. I’d like to use SQL to query the table, then use some PHP to convert to JSON.
I know Webform can export to JSON format, but think it would be easier to just query the Webform Submissions table using SQL.
I could run the charts through the Drupal DB API. But since the data just gets saved once and not updated after that, I don’t think I need the Drupal DB API, which would allow the data to be updated.
With Views, I think I’d need custom programming for granular control. SQL seems like it has tighter control of queries.
I realize I could have problems with forward compatibility if the structure of the Webform Submissions table is changed.
— Is my analysis correct? Does the above approach seem reasonable?
— Any reasons to not run off of the Webform Submissions table? Scaling issues? Security problems?
— Advantages and disadvantages of using SQL instead of the Drupal DB API?
— Is there doc. or other discussion I should read? Alternate approaches to consider?