If Else in SQL

Today I hit an interesting problem and when creating a report for an internal billing system. The system itself automatically bills each customer every year and we have two types of customers. One that pays with a credit card through Stripe, and another type that we invoice and their billing departments pay.

Because invoices take longer for us to be paid we always create the invoice 30 days before it’s actually due and for Stripe customers,​ we do it 7 days early. This way if anything goes wrong like a declined credit card it gives them extra time to fix it before becoming delinquent.

Of course, the side effect to this is it causes our stored “next billing date” to be 7 or 30 days off depending on the type of customer, and we keep the original date in case the customer ever decides to switch from one to the other.

With that setup out of the way, I needed to create a list of customers ordered by when they would be billed. Like all things, there are many ways to solve this but for this, I decided to do it directly through SQL. What I wanted to happen was if an organization is a Stripe customer then creating a “real_bill_date” set to 7 days before it’s due, otherwise create a “real_bill_date” to 30 days prior.

Here is the query I was able to create:


SELECT
  CASE
    WHEN
      organizations.stripe_active = \'1\'
        THEN
          DATE_SUB(next_bill_date,INTERVAL 7 DAY)
    ELSE
      DATE_SUB(next_bill_date,INTERVAL 30 DAY)
  END AS real_bill_date,
  organizations.*
FROM
  organizations 
WHERE 
  subscription_active = 1
ORDER BY 
  real_bill_date ASC

This uses SQL WHEN/ELSE clauses to determine what should be happening. Basically translating into “if Stripe is active, then subtract 7 days”, “else subtract 30 days”.

This is useful for the next time you think you have to do a query and then do a query looping each to get a calculated field. Yes, that typically works but is process intensive and can lead to timeouts. Doing it directly through SQL is usually much faster.

PHP: Underscores for Private

Jeffrey Way released a new Laracasts Snippet, and in it, he mentioned a tweet from me:

In the podcast, he talked about how PHP felt more “libertarian” in the early days. The underscore represented that this should be protected or private and even though this is a recommendation from the creator of the code, you are an adult, and if you want to do something with it, then you accept the risk.

The language and community have since switched the stance, and now everyone wants to restrict it with the feeling that if it’s code you are writing, then you know best how it should be used.

I’m not sure one way is better than the other, but as I mentioned in the tweet, I miss the old days.

Find Your Top 10 Shell Commands

Ever wonder what shell commands you use most often? Copy and paste the following into your CLI and see your top 10.

history | awk '{print $2}' | sort |uniq -c | sort -rn | head -10

Here is a list of mine from my dev machine:

  • 574 git
  • 461 cd
  • 422 vim
  • 314 ls
  • 202 gss
  • 109 exit
  • 97 php
  • 68 sudo
  • 64 ag
  • 41 ..

I think it’s fun to see what you use most often. Also, as a bonus, if you are a PhpStorm user go to help -> productivity guide to see what features of the IDE you use most often.

Building Laracon Online

This will be the third year we’ve been running Laracon Online and I have learned a lot about selling tickets in the process. Based on this tweet I was reminded of how we’ve reworked the order system over these three years and I wanted to share with you the progression.

The first year we used a third party service to sell tickets. For the percentage they would take we assumed it would be a decent deal for us since we wouldn’t have to build out any of the infrastructure. However, I was swamped with support that year. I had hundreds of people entering their email addresses wrong and other weird stuff. It was a nightmare.

From this point we decided we would build our own system for the next year and I tried my best to build the simplest system possible. You hit the /pay route, added all the emails you wanted to buy tickets for, entered your account info, and pay. Behind the scenes we would email all the people and tell them to reset their password to create an account. It was simple and worked great.

Now that we had all this data for this year I was brainstorming on how I could make it even easier for people to purchase and I came up with the idea of just pre-filling the checkout system with all the people you purchased tickets for last year. So if you work at a company buying tickets for 20 people all you have to do is confirm they all are the same and hit buy.

Thinking through all this took some time but the implementation was really straightforward and only took me a night of hacking.

As developers we want to focus on the code and think about how to design apps that are easy for us, but our focus should always be on the end user. Even if it adds complexity or makes our app harder to manage later. The customer is the priority and making their life easy is why we are here.

Everything is always a trade off, just make sure you are prioritizing what is important.

Set your Terminal to Red for Production

Making mistakes in production is one thing that always scares me. This problem multiplies if you use a lot of terminal tabs but a quick and easy solution is to make production red so it’s immediately noticeable where you are logged into.

For the image above I’m using Termius on iOS which supports this and allows you to set it on a folder level and then any ssh connections under the group inherits the color.

It’s a really simple way to help distinguish which connection you are logged into and can be a life saver. This can done in other terminals too, just do a web search to find instructions for your app of choice.

Refactoring For Prose

I’ve worked with a lot of legacy applications and one thing that I have seen many times is small things that are confusing. For example, I recently came across some code that looked like this:

if ($foo) {


    $noAction = true;


} else {


    $noAction = false;


}

That is pretty straightforward and easy to grasp. But what comes next is where the problem lies:

if (! $noAction) {


    //...


}

If we read this as a sentence it says, “if not no action” and most of us struggle figuring out what the double negative translates too. It’s not something that we encounter very often in English so when we see it our brains turn to mush.

From my experience things like this are common in older procedural code because you are defining the variable so far away from the use that it either morphed into the current style over time, or the variable is used in several different contexts and this was the path of least resistance when it was originally coded.

While you are coding try to think through things like you are writing prose. In this example, maybe something as simple as renaming the variable to $doAction would be sufficient.

if ($foo) {


    $doAction = false;


} else {


    $doAction = true;


}




if($doAction) {


    //... 


}

In my opinion reading “if do action” is much clearer and easy to grasp.

Sleep on it

The old saying, “sleep on it”, meaning spend some time tonight thinking about the question and have an answer tomorrow.  It’s a common phrase in our language but how often do you literally put aside time to actually think about it?

Having a busy life outside of work with family, hobbies, dinner, everything else it’s really hard to set aside time to just sit and think. I’ve had to start using the time just before bed, mornings, or go for a walk alone.

Yesterday I was working on a new integration an app we recently launched and I had to work on adding a new API endpoint. It was all really straightforward.  However, last night as I was reviewing my day before bed I started thinking about the way I solved the problem and realized I may have made a mistake because I forgot about a constraint from the service that’s going to be reading it.  While trying to solve that in my head I realized I solved the entire problem wrong and I should redo my work.

What I do each night is review my day like I was a star in a TV show and I’m following myself around as the cameraman with the goal of looking at my decisions from a different standpoint. This is what helps me attempt to remove my biases, but of course, that doesn’t always work. It is pretty good at allowing me to see mistakes though, and that’s why I do it.

I know many programmers spend their after work hours coding and being on the computer but I implore you to take a break. Get away from the screen and you’ll actually do better work.