More Things I Should Have Already Figured Out Years Ago

So I used to read data into an array of arrays like so:

sub get_threads($self) {
    $self->pg->db->query(<<~'END_SQL')->arrays()
        SELECT thread_id,
               TO_CHAR(thread_date, 'Dy Mon DD HH:MI:SS AM TZ YYYY'),
               thread_author,
               thread_title,
               thread_body
          FROM threads
         WHERE NOT hidden_status
         ORDER BY thread_date DESC;
       END_SQL
}

Then I'd plop that data into templates like so:

<% for my $thread (@$threads) { =%>
  <article class="thread">
    <h3 class="title"><%= @$thread[3] %></h3>
    <h4 class="date"><%= @$thread[1] %></h4>
    <h5 class="author"><%= @$thread[2] %></h5>
    <p class="body"><%= @$thread[0] %></p>
  </article>
<% } =%>

This is already pretty cool but I kept losing track of what index number went to which data field. Then I saw that there is a hashes() method in Mojo::Pg::Results and thought... What if we used 100% of the brain? (Edit: Formatting turned out bad for this one)

sub get_threads($self) {
    $self->pg->db->query(<<~'END_SQL')->hashes()
        SELECT thread_id                                             AS id,
               TO_CHAR(thread_date, 'Dy Mon DD HH:MI:SS AM TZ YYYY') AS date,
               thread_author                                         AS author,
               thread_title                                          AS title,
               thread_body                                           AS body
          FROM threads
         WHERE NOT hidden_status
         ORDER BY thread_date DESC;
       END_SQL
}

By using SQL to assign an alias to the column names my templates now look much cleaner:

<% for my $thread (@$threads) { =%>
  <article class="thread">
    <h3 class="title"><%= %$thread{'title'} %></h3>
    <h4 class="date"><%= %$thread{'date'} %></h4>
    <h5 class="author"><%= %$thread{'author'} %></h5>
    <p class="body"><%= %$thread{'body'} %></p>
  </article>
<% } =%>

Readability becomes more and more important as my memory gets worse and worse... I need to learn POD so if I'm a boi of my word there may be a blog post on that in the future. Knowing me... Years in the future.

#perl #mojolicious #sql