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:

    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 

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!

  • Would appreciate insight from downvoters as to what seems to be wrong with this question! – Jumhyn Apr 11 '20 at 16:21

It's worth nothing that SQL is not "unstructured". In fact, the flat structure of the columns is a key part of the overall structure which allows queries to be specified and executed efficiently and flexibly.

That said, it does at times seem inconvenient that there is no concept of column groups, when a large number of columns are concerned.

You can solve this in two ways, either by using a system of prefixes as you have, which is pretty standard, or by consolidating multiple columns into one (for example, with Json, Xml, or other approaches - all of which will tend to increase the overall amount of code written).

20 columns is not however a huge amount in the scheme of things, and I can't see any significant problem with your current approach.

  • Thanks, @Steve. Yeah, I realize there’s a ton of structure to the language and DB files, I really just meant to emphasize that the query results were always a “flattened” view of the structured data. Glad to know that the prefix-based method is not totally misguided. Do you have any thoughts about solving the PHP side of the issue? I.e., can you provide any suggestions about how to reduce the repetition of property names/keys and susceptibility to making typos in one of the many repeated strings? – Jumhyn Apr 11 '20 at 16:58
  • @Jumhyn, I'm not a PHP user myself, but the amount of repetition does not seem inordinate - it's par for the course when importing tabular data to be having to map names and types across, and as you say, mapping the flattened structure to a hierarchical one. The only thing I might suggest, if it is possible in PHP (it certainly is in SQL), is to make better use of visual alignment - typos in lists of names are often more obvious this way. – Steve Apr 11 '20 at 19:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.