Screen shot 2017 09 30 at 9.57.03 pm

How I Use Postgres Array Fields


How to store and query array data in ActiveRecord and Postgres

Updated on January 3, 2018

I use the Array feature in Toofr's Postgres database to store notification settings for each user.

I did a lot of digging around to find the best way to do it. Some people suggest a related table approach but that seemed overkill to me. First of all, this feature would not be used very often. Secondly, this data didn't seem complex. It just felt like a list. I'm a user and I want to receive X, Y, and Z notifications. That X, Y, and Z preference should just sit on the User table.

With that part settled, the big question was is this a Hash or is it an Array? I settled on Array again because of simplicity. I can define what X, Y, and Z mean elsewhere in the model. The keys are preset, defined, and static. I think Hashes should be used for arbitrary data when you can't predict what the contents will be. In this case, I know exactly what they'll be.

Let's get to work! I started by running a migration.

class AddNotificationsToUsers < ActiveRecord::Migration[5.0]
  def change
    add_column :users, :notifications, :text, default: [1,2], array: true
  end
end

I have three kinds of notifications (see the model below) and figured the first two would apply to every user so I made those values included by default.

I then made an index since I would be searching by these values. The gin index is faster but takes more space. Since I don't have that many users (< 50,000) I chose this one.

class AddIndexToUsersNotifications < ActiveRecord::Migration[5.0]
  def change
    add_index  :users, :notifications, using: 'gin'
  end
end

To handle the form requests, rather than mess with the devise controllers, I made a separate one and called it Notifications. The bounce method handles a callback from Mailgun and unsubscribes the user from all notifications if their email bounces. I want to stay in Mailgun's good graces!

# app/controllers/notifications_controller.rb

class NotificationsController < ApplicationController
  before_action :authenticate_user!, except: [:bounce]
  protect_from_forgery except: [:bounce]

  def edit
    @user = current_user
  end

  def update
    @user = current_user
    respond_to do |format|
      if @user.update(user_params)
        format.html { redirect_to edit_notification_path(@user), notice: 'Your notifications are updated!' }
        format.json { render :show, status: :ok, location: @user }
      else
        format.html { render :edit }
        format.json { render json: @user.errors, status: :unprocessable_entity }
      end
    end
  end

  def bounce
    user = User.find_by_email params[:recipient]
    if user
      puts user.update(notifications: [])
    else
      puts 'user not found'
    end
    head :ok
  end

  private

  def user_params
    params.require(:user).permit(notifications: [])
  end
end

Here's the form that posts to the controller. I use Simple Form to generate checkboxes from the constant Hash I defined on the User model. This gives each checkbox value of the ID and labels it with a friendly name. To load the checked state, I found that f.object.notifications does the trick.

# app/views/notifications/edit.html.erb

          <%= simple_form_for(@user, url: notification_path(@user), html: { method: :put }) do |f| %>

            <h4>Update your notification preferences</h4>
            <% if notice %><p class="notice"><%= notice %></p><% end %>
            <% if alert %><p class="alert"><%= alert %></p><% end %>
            <div class="form-group clearfix">
              <%= f.input :notifications, as: :check_boxes, collection: User::NOTIFICATIONS.collect { |f| [ f[:name], f[:id] ] }, class: "form-control col-md-6", id: "notifications", checked: f.object.notifications %>
              <%= f.error :notifications %>
            </div>


            <%= f.submit "Update", class: "btn btn-primary", tabindex: "6" %>
          <% end %>

These are the edits I made to the User model.

# app/models/user.rb

  scope :gets_notification, -> (id) { where('? = ANY (notifications)', id.to_s) }

  NOTIFICATIONS = [
    { id: 1, name: 'Company updates and newsletters' },
    { id: 2, name: 'New marketplace lists for sale' },
    { id: 3, name: 'New marketplace list requests' }
  ].freeze

      def gets_notification(id)
    notifications.include? id.to_s
  end

The scope works on the class, so User.gets_notification(1) will find every user with '1' in their notifications array. I can also check on a single user by using User.first.gets_notification(1) to see if that individual has a '1' in their array. Not sure if it's Ruby kosher to have an instance and a class method with the same name but hey, it works for me!

And there you have it. That's how I used Postgres Arrays to run the notification preferences in Toofr.

More Find Emails Articles >>