Recently I've been working on a project to teach myself PHP and SQL, and as the project has gotten more complex I've been wondering what the idiomatic approaches are for creating the backend models from SQL query results.
For instance, I am building out a feature that lets users of my service invite each other to events. I want to have an endpoint that will fetch all of a user's received invites, which is easy and efficient enough to fetch in a single query. An Invite consists of a sent time, a sending user, a receiving user, and an event (and an event has a "created-by" user). Writing out a query for this ends up looking like:
SELECT
invite.sent_time AS invite_sent_time
event.id AS invite_event_id,
event.name AS invite_event_name,
event.start_time AS invite_event_start_time,
event.end_time AS invite_event_end_time,
owner.user_id AS invite_event_owner_id,
owner.user_name AS invite_event_owner_name,
owner.user_email AS invite_event_owner_email,
owner.user_friend_since AS invite_event_owner_friend_since,
sent_by.user_id AS invite_sent_by_id,
sent_by.user_name AS invite_sent_by_name,
sent_by.user_email AS invite_sent_by_email,
sent_by.user_friend_since AS invite_sent_by_friend_since,
sent_to.user_id AS invite_sent_to_id,
sent_to.user_name AS invite_sent_to_name,
sent_to.user_email AS invite_sent_to_email,
sent_to.user_friend_since AS invite_sent_to_friend_since
FROM invite
INNER JOIN ...
That is, the query ends up containing a column for every nested property in the model that I'll ultimately be creating.
The resulting rows are then transformed into the actual PHP objects via constructors that look like (simplified for the sake of clarity):
class Invite {
private function __construct(array $arr, string $prefix = '') {
$this->sent_time = new DateTime($arr[$prefix . 'sent_time']);
$this->event = new Event($arr, $prefix . 'event_');
$this->sent_by = new User($arr, $prefix . 'sent_by_');
$this->sent_to = new User($arr, $prefix . 'sent_to_');
}
}
class Event {
private function __construct(array $arr, string $prefix = '') {
$this->id = $arr[$prefix . 'id'];
$this->owner = new User($arr, $prefix . 'owner_');
$this->start_time = new DateTime($arr[$prefix . 'start_time']);
$this->end_time = new DateTime($arr[$prefix . 'end_time']);
$this->name = $arr[$prefix . 'name'];
}
}
While this works well enough, there's a number of things I don't love about this solution. It involves a good amount of repetition since I have to repeat the PHP property names across the SQL query and as the keys in the associative array, and it also relies on me typing the strings correctly each time. I'm also wary of having the query blow up in size as my objects get more and more complex.
So, my questions are: is building the query in this way appropriate? If I've verified via EXPLAIN that the queries are running in a relatively efficient manner, is getting all the info in a single result the right way to do things, or would I be better served returning, say, just the sent_by_id and then querying the users separately before returning the response? If getting everything in a single query is the correct approach, what are the best practices/patterns for turning those query results into the application models?
I would much appreciate everyone's thoughts on this, or any resources for backend application design that someone could recommend!
