Forem: Will Pickeral

Computed columns with the find_by_sql method in a Rails application

Will Pickeral — Sat, 25 Feb 2023 21:14:09 +0000

Introduction

Ruby is a beautiful expressive programming language. Like many other programming languages, there are many ways to accomplish the same task. In this article, I will share how I used the find_by_sql method in Rails to write a SQL query to replace a less efficient way of generating computed values. First, I will share the business case, my original solution, and finally, my updated solution. Let's dive in!

The Business Case

The business requirements state that we must sort customer expenses in descending order by the date the expense record is created. The expense records are displayed on the customer's dashboard. We can do something like this to get the results:

current_user.expenses.where(created_at: Date.current.all_month).order(created_at: :desc)

The Problem

After diving into the code, we learn the customer can update the date for a given expense in the customer dashboard. However, the created_at date field does not change when the customer updates the expense record. Instead, a back_date field is updated with the new date.

We need to ensure that our sorted expenses are sorted by created_at unless the back_date field is truthy. If the back_date is available, we need to sort the expenses using the back_date. Although this specific example is fictional, it is similar to a problem I faced while building a side project.

Solution #1

# app/models/expense.rb

  scope :back_dated, -> { where.not(back_date: nil) }
  scope :not_back_dated, -> { where(back_date: nil) }

  def self.w_computed_date   
back_dated_w_computed_date.concat(not_back_dated_w_computed_date).map(&:symbolize_keys).sort_by(&:date).reverse
  end

  def self.back_dated_w_computed_date
    back_dated.map { |e| e.as_json.merge({ date: e[:back_date] }) }
  end

  def self.not_back_dated_w_computed_date
    not_back_dated.map { |e| e.as_json.merge({ date: e[:created_at] }) }
  end

# app/controllers/expenses_controller.rb

def index
  @expenses = current_user.expenses.w_computed_date
end

This works, but it can cause some performance issues on our server. Let's solve this with a better solution.

Solution #2

ActiveRecord provides the find_by_sql method to write custom SQL. Before, we implement this method, let's make sure we can get the correct results with SQL.

We could do something like this:

SELECT *,
       coalesce(back_date, created_at) as date
FROM expenses
WHERE user_id = 1
ORDER BY date DESC

We select all expenses for the user. If the expense is back-dated, we return the back_date as date.If the expense is not back-dated, we return created_at as date .This is great, but our business requirements state we need to sort the expenses for a given date range. This gives us all the expense records. If you are not very familiar with writing SQL code, like I was when I faced this problem, you may try to solve the problem like this:

SELECT *,
       coalesce(back_date, created_at) as date
FROM expenses
WHERE user_id = 1;
  AND date >= '2023-01-01'
  AND date <= '2023-01-31'

This will not work. The SQL code is not executed in the order that it reads.

SELECT * 
FROM expenses
WHERE ...

The SQL is executed as:

FROM expenses
WHERE...
SELECT *

So in the case of our query, we cannot filter by date because the computed date is only available in the execution context after the WHERE clause is read. Thankfully, there is a way to gain more control over the execution of the SQL using sub-queries.

SELECT *
FROM (SELECT *,
             COALESCE(back_date, created_at) as date
      FROM expenses) AS sub_query
WHERE user_id = 1
  AND date >= '2023-02-01'
  AND date <= '2023-02-28'
ORDER BY date DESC;

By wrapping the initial query in parenthesis, we tell the database to read this query first. Then, any values in the subquery will be available in the parent query, which includes date.

Now, let's convert the SQL to something that Rails understands.

# app/models/expense.rb

  def self.w_computed_date
    select('* , COALESCE(back_date, created_at) as date').order(date: :desc)
  end

  def self.for_month(date)
    query = <<-SQL
        SELECT * 
        FROM (:subquery) as w_computed_date
        WHERE date >= :date_start
        AND date <= :date_end
    SQL

    placeholders =  {
      subquery: w_computed_date,
      date_start: date.beginning_of_month,
      date_end: date.end_of_month
    }

    find_by_sql [query, placeholders]
  end

The find_by_sql method allows you to use placeholders as symbols and then pass a hash as the second argument, including the placeholders' values. Placeholders increase the readability of our query and are preferable to using string interpolation.

Also, you may have noticed that we did not add WHERE user_id = 1 to the SQL string. We don't need to since the user table has a one-to-many association with the expenses table, and we call this method on an instance of the user object.

We can use our new method in the controller to send the expenses with the computed date to the front end.

# app/controllers/expenses_controller.rb

def index
  @date = params['date']
  @expenses = current_user.expenses.w_computed_date 
end

Conclusion

In this article, I share my business case, the problem, my original solution, and a better solution. The final solution allowed me to solve our problem with less code, requiring fewer tests and being easier to maintain. I learned so much while working on this problem, and I hope it helps you somewhere during your programming journey. Thanks for reading.

Building a contact form with Next.js and Nodemailer

Will Pickeral — Wed, 31 Aug 2022 19:49:43 +0000

Introduction

There are many packages available to help you implement email functionality on your website or app. I used the popular nodemailer library, and it paired really nicely with Next.js api routes.

I will start by describing the contact form component. Next, I will discuss how I implemented the API endpoint to handle email requests. Finally, I will close by integrating the form with the endpoint.

To get the most out of this article, you should be familiar with Javascript, React, and Next.js. Let's get start by reviewing the contact form component below.

import useContactForm from '../hooks/useContactForm';

const ContactForm = () => {

  const {values, handleChange} = useContactForm();

  const handleSubmit = (e) => {
    e.preventDefault()
  }

  return (
      <form onSubmit={handleSubmit}>
          <label htmlFor='email'>Email</label>
          <input
              required
              id='email'
              value={values.email}
              onChange={handleChange}
              type='email'
          />

          <label htmlFor='subject'>Subject</label>
          <input
              required
              id='subject'
              value={values.subject}
              onChange={handleChange}
              type='text'
          />
          <label htmlFor='message'>Message</label>
          <textarea
              required
              value={values.message}
              onChange={handleChange}
              id='message'
              rows={8}
          />
        <button type='submit' value='Submit'>Send</button>
      </form>
  );
};

export default ContactForm;

The form component

The form component is relatively straightforward, but let's break down what's happening here.

First, we import a custom hook called useContactForm (more on that later). Then, we destructure values and handleChange. The values object represents the current state of the form inputs and has the following properties and initial state.

const values = {
    email: '',
    subject: '',
    message: '',
}

We also get a function called handleChange, which will handle the form onChange events.

Working our way down in the function body, we have a handleSubmit function to handle the form onSubmit events.
Currently, the only thing it does is prevent the default form submission behavior. We will add more logic to handle the form data later. For now, let's take a closer look at our useContactForm custom hook.

import {useState} from 'react';

const useContactForm = () => {
  const [values, setValues] = useState({
    email: '',
    subject: '',
    message: '',
  });

  const handleChange = (e) => {
    setValues(prevState => {
      return {
        ...prevState,
        [e.target.id]: e.target.value,
      };
    });
  };

  return {values, handleChange};
};

export default useContactForm;

As mentioned earlier, the initial state of the values object contains properties defined by the id defined on the form
input, each with an initial value of an empty string.

The handleChange function updates the state by including all the elements from the current state using spread syntax. Finally, we use e.target.id to update the target element's state dynamically.

There are certainly other ways to keep state for a form. The most common alternative that comes to mind is using one useState hook for each form input. The useContactForm hook allows us to accomplish the same goal by using less code.

Before moving on to the API, I created a function using axios that we will use to
send requests to the API.

import axios from 'axios';

const sendEmail = async (email, subject, message) => {
  return axios({
    method: 'post',
    url: '/api/send-mail',
    data: {
      email: email,
      subject: subject,
      message: message,
    },
  });
};

export default sendEmail;

We will come back to this when we wire up the form with the API.

The API

I created the API endpoint with the built-in Next.js Api Routes. I started by creating the following directory
in my Next.js project.

/blog/pages/api

I named the endpoint send-mail.js. So the url for the endpoint will be:

http://localhost:3000/api/send-mail

Let's add some code to our endpoint.

const nodemailer = require('nodemailer');

export default function handler(req, res) {

  const message = {
    from: req.body.email,
    to: process.env.GMAIL_EMAIL_ADDRESS,
    subject: req.body.subject,
    text: req.body.message,
    html: `<p>${req.body.message}</p>`,
  };

  let transporter = nodemailer.createTransport({
    service: 'gmail',
    auth: {
      user: process.env.GMAIL_EMAIL_ADDRESS,
      pass: process.env.GMAIL_APP_PASSWORD,
    },
  });

  if (req.method === 'POST') {
    transporter.sendMail(message, (err, info) => {

      if (err) {
        res.status(404).json({
            error: `Connection refused at ${err.address}`
        });
      } else {
        res.status(250).json({
            success: `Message delivered to ${info.accepted}`
        });
      }
    });
  }
}

Ok, a lot is going on here. First, I included the nodemailer library. Next, we have the function handler, which is the request handler, with the req and res parameters. Check out the API Routes docs if you are unfamiliar
with the req and res objects.

Next, we create the nodemailer message configuration object. I used Gmail as my
provider, but there are a few caveats that are discussed here (using Gmail).
The excellent news is that nodemailer supports many alternatives. The nodemailer docs are fantastic. I encourage you to review the docs if you are unfamiliar with the library.

We create the transporter object to send the mail data. The transporter function accepts the message data and an optional callback. The callback gives us access to the error and info objects which I use to handle the response.

That's it for the API. Now, let's wire up the contact form.

Connecting the form to the api

Let's add the rest of the code to our handleSubmit function.

import useContactForm from '../hooks/useContactForm';
import sendEmail from '../lib/sendEmail';
import {useState} from 'react';

const ContactForm = () => {

  const {values, handleChange} = useContactForm();
  const [responseMessage, setResponseMessage] = useState(
      {isSuccessful: false, message: ''});

  const handleSubmit = async (e) => {
    e.preventDefault();
    try {
      const req = await sendEmail(values.email, values.subject, values.message);
      if (req.status === 250) {
        setResponseMessage(
            {isSuccessful: true, message: 'Thank you for your message.'});
      }
    } catch (e) {
      console.log(e);
      setResponseMessage({
        isSuccessful: false,
        message: 'Oops something went wrong. Please try again.',
      });
    }
  };

  return (
      <form onSubmit={handleSubmit}>
          <label htmlFor='email'>Email</label>
          <input
              required
              id='email'
              value={values.email}
              onChange={handleChange}
              type='email'
          />

          <label htmlFor='subject'>Subject</label>
          <input
              required
              id='subject'
              value={values.subject}
              onChange={handleChange}
              type='text'
          />
          <label htmlFor='message'>Message</label>
          <textarea
              required
              value={values.message}
              onChange={handleChange}
              id='message'
              rows={8}
          />
        <button type='submit' value='Submit'>Send</button>
      </form>
  );
};

export default ContactForm;

The handleSubmit function is converted to an async function. We use the sendEmail function we created to await the response from the new API endpoint. The responseMessage state is updated with the response.

Conclusion

Now that the responseMessage state has changed, we can use it to update the UI. Usually, this includes displaying an alert to the user with the status of the message.