Greetings,
I would really love to hear about your broad knowledge about application design on this one!
I'm new to Phalcon, and now building my first MySQL & RESTful-API based application with it, but encountering some challenges along the way.
First, about my application design, the concept is as follows: An API as a core for the application, wrapped by a "UI shell" of pages and views that utilize it. The API is supposed to be composed of a set of Phalcon models that represent the DB and business logic, and over them, a component that acts as a layer that makes those models accessible as "HTTP services" - generally by translating requests to model names, and the HTTP verb to the appropriate model action (e.g: GET => $account->find()/findFirst(), PUT => $account->update([params]), etc.).
I was sure that the Phalcon models are gonna rid me of having to write most of my SQL, however, soon I came across some pretty common scenarios that the models couldn't handle the way I would expect:
You have entities like messages for example, and you wish to query them using a column of some other, related entity (like the FIRST NAME of the user that owns those messages). A model can't do this in a single operation.
I want to show a list of messages, each attached with the details of the user that sent it. In Phalcon, the first thought that comes to mind is taking advantage of the model relations feature, but thinking further I came to realize that will perform a full query for every message rendered, which is a disaster performance-wise, rather than retrieving them all together with their user details in some single, JOINed query.
I want to show a list of users, each with a total messages count. Found no other way to achieve that rather than a full query that includes a COUNT() field & GROUP BY or a sub-query.
I tried to look up such use cases and others, for most of them there hasn't seemed to be any elegant solution.
The Point:
What I want to achieve is a API-based architecture that makes sense, scalable and easily customizable to real world scenarios (being able to handle obvious situations like demonstrated above).
What would you do in Phalcon to handle the problems I encounter?
What do you think about the design concept I took? Is it somewhat standard and makes sense?
Most importantly, how would you design a full & flexible API without repeating cumbersome SQL queries everywhere, if at all? Do you have any references or examples of known companies' approach on this?
That's a big question and any help will be huge! Thanks! Dor.
