DEV Community: Masroor Hussain

Sort records based on multiple columns' values in SQL

Masroor Hussain — Wed, 22 Sep 2021 09:38:36 +0000

Use Case description:

Scenario:

We have multiple columns(in one or more tables), these columns are mutually exclusive i.e only one of these columns can have a non-null value at a given time and others will be null.

Table Schema Description

We have a tasks table
Table attributes are: id, task_owner, task_type
Only one of the attributes(task_owner, task_type can have a non-null value at a given time).

Our Goal:

Our goal is to sort the records based on the values of all these columns i.e The two columns task_owner and task_type should behave as single data source, and we should be able to sort them based on the values present in this data source.

Tasks table

Expected output

Note the ordering of the records, the records are sorted based on the values of the two columns task_owner and task_type.

Solution

The solution is a simple one, we're going to use a generated/computed column which we'll use as a data source for sorting purposes.
The COALESCE operator, returns the first non-null value from its arguments.

Solution SQL

SELECT *, COALESCE(tasks.task_owner, tasks.task_type) AS sorting_col
FROM tasks
ORDER BY sorting_col;

SQL Script for Testing

You can run & test this at PG-SQL, it lets you create tables and run SQL queries on them.

Create Table

CREATE TABLE tasks (id int, task_owner varchar(255), task_type varchar(255));

Populate Table with data

INSERT INTO tasks (id, task_owner, task_type) VALUES
(1, 'Masroor', null),
(2, null, 'Manager'),
(3, 'Alex', null),
(4, 'Yuri', null),
(5, null, 'Supervisor'),
(6, 'David', null),
(7, 'Brendon', null);

Unzip an archive and attach images to Active Record model objects

Masroor Hussain — Sun, 04 Apr 2021 13:50:24 +0000

Recently I worked on a project where I had to upload a zip archive of images and then attach those images to Active Record model objects on the Rails server.

Note: This example uses Active Storage to attach images to Active Record objects.

Here is how I got it all working

Install Gem

Include this gem

gem 'rubyzip'

then do

bundle install

Unzipping and file attachment

We suppose an Active Record model named Product, which has an Active Storage attribute called product_image

class OrderItem < ApplicationRecord
  has_one_attached :product_image
end

The controller which renders a zip upload form in the action zip_upload_form (view template code is not provided here) and the form gets posted to the zip_file action, which then processes the zip file by calling the process_zip_file method. It's better to put such code in a service but for the sake of this tutorial let's just put in the controller.

require 'zip'

class UnzipController < ApplicationController

  # GET
  def zip_upload_form
    # this action renders form for zip file upload
  end

  # POST
  def zip_file
    # the form is posted to this action
    # this action processes the uploaded zip file

    # params[:zip] is the uploaded zip file    
    if params[:zip]
      process_zip_file(params[:zip])
    else
      redirect_to zip_upload_form_path
    end
  end

  private

  def process_zip_file(tmpfile_zip)
    Zip::File.open(tmpfile_zip.path) do |zipfile|

      # iterating through all the entries found in the zip file
      zipfile.each do |entry|
        if entry.file?
          puts "#{entry.name} is a file!"
          attach_file_to_model_obj(entry)

        elsif entry.directory?
          puts "#{entry.name} is a directory!"

        elsif entry.symlink?
          puts "#{entry.name} is a symlink!"
        end

      end
    end
  end

  def attach_file_to_model_obj(entry)
    # first we create a temp file from the file found in the zip archive
    # then attach the temp file to model obj

    Tempfile.open(["my-filename", ".#{put_extension_here}"]) do |tmp|
      # this line creates the temp file
      entry.extract(tmp.path) { true }

      # attaching the temp image file to active record object
      Product.first
             .product_image
             .attach(io: File.open(tmp.path), filename: "my-filename.#{put_extension_here}")
    end
  end

end

Cover Image by JOHN TOWNER on Unsplash

Enum dropdown with readable values in rails

Masroor Hussain — Sat, 19 Dec 2020 00:53:28 +0000

For the sake of quick documentation of a common use case.
If you have a model, which has an enum attribute and you want to have a select tag(dropdown) with titleized values(no ugly underscores) of the enum and you also want to store the data in its original(enum form).
Here's a quick example

# app/models/source.rb

class Vendor < ApplicationRecord
  # in the db schema, vendors table has payment_type column with integer datatype(the datatype can be different, no issues)
  enum payment_type: [:one_time :recurring :not_known]
end

Then in your view file, where you want to display the dropdown for that enum attribute

<%= form_with(model: @vendor, local: true) do |form| %>

  <%= form.label :payment_type %>
  <%= form.select :payment_type, options_for_select(Vendor.payment_types.map {|key, value| [key.titleize, Vendor.payment_types.key(value)]}, @vendor.payment_type) %>

  <%= form.submit %>
<% end %>

That's all.
With this done, your dropdown will display three options with the text
One Time, Recurring, and Not Known
while when you submit the form the submitted value of payment_type will be
one_time or recurring or not_known
depending on which option you selected from the dropdown.

Reuse Javascript code in Rails 6

Masroor Hussain — Tue, 15 Dec 2020 18:58:43 +0000

DRY(Don't Repeat Yourself) is a basic principle in programming. This article provides a very trivial but useful way for rails developers to reuse their javascript code in multiple files.
The usual folder structure in a rails 6 app looks like

-app/
 |-assets/
 |-channels/
 |-controllers/
 |-helpers/
 |-javascript/
   |-packs/
     |-application.js
 |-...(and other folders)

If you have multiple javascript files in the app/javascript/ folder and you want to reuse the code between these files

-app/
 |-javascript/
   |-packs/
     |-application.js
     |-first.js
     |-second.js

Consider

// app/javascript/packs/first.js

const calculateArea = (params_here) => {
  // calculate and return area
}

const calculateVolume = (params_here) => {
  // calculate and return volume
}

now if you want to use the function calculateArea and calculateVolume in your second.js you can export these functions from first.js and then import them in second.js.

// app/javascript/packs/first.js

const calculateArea = (params_here) => {
  // calculate and return area
}

const calculateVolume = (params_here) => {
  // calculate and return volume
}

export { calculateArea, calculateVolume };

and import them for re-use

// app/javascript/packs/second.js

import { calculateArea, calculateVolume } from './first';
// now you can use the imported consts/variables/functions in this file

Bonus

To include your custom javascript code into application.js pack, considering you've app/javascript/packs/first.js and app/javascript/packs/second.js, do this

// app/javascript/packs/application.js

import 'packs/first'
import 'packs/second'

Cover Image credits Clem Onojeghuo on Unsplash

Send emails with a Bootstrap template using Gmail

Masroor Hussain — Wed, 09 Dec 2020 20:20:57 +0000

In this tutorial, we are going to use Rails ActionMailer and Gmail to send emails with bootstrap styling. This tutorial has 2 parts, first is setting up the ActionMailer and Gmail and the second part is setting up bootstrap email template.

Set up Mailer

Generate a mailer

rails g mailer sample_mailer

This will generate a ruby app/mailers/sample_mailer.rb file and a directory app/views/sample_mailer for SampleMailer's view files.
The app/mailers/sample_mailer.rb file looks like

class SampleMailer < ApplicationMailer
  default from: "from@example.com"
end

Change the from@example.com with the Gmail account email which you want to use for sending emails, and then write a method that will construct the email. We've named the method welcome_email(which will construct a very dull welcome email for the sake of this tutorial).

class SampleMailer < ActionMailer::Base
  default from: "your_email_here@gmail.com"

  def welcome_email(welcome_message, name, email_to, subject)
    @message = welcome_message
    @name = name
    make_bootstrap_mail(to: email_to, subject: subject)
  end
end

Gmail configuration

Write the following config in your /config/environments/production.rb or /config/environments/development.rb (whichever you're going to use for sending the emails, of course you can use both; in that case put the config in both the files)

config.action_mailer.delivery_method = :smtp
# SMTP settings for gmail
config.action_mailer.smtp_settings = {
  address: 'smtp.gmail.com',
  port: 587,
  user_name: ENV['GMAIL_USERNAME'],
  password: ENV['GMAIL_PASSWORD'],
  authentication: 'plain',
  enable_starttls_auto: true
}

Gmail username and password are sensitive data so we have stored them in the environment variables, for the rails development environment, you can set up environment variables by using the Dotenv gem or Figaro gem. Don't forget to restart server after setting up environment variables.

Enable Gmail's Less secure apps setting

Go to this settings link and enable less secure apps. Without enabling this setting Gmail won't let you send emails from your app.

Set up Bootstrap email template

Include the bootstrap-email gem

Add this to your Gemfile

gem 'bootstrap-email'

and then do

bundle install

Create bootstrap layout file

Create a file /app/views/layouts/bootstrap-mailer.html.erb and paste the following HTML into it. This file will act as a container file for the mailer views that you're going to render.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <%= stylesheet_link_tag "application-mailer", media: "all" %>
  </head>
  <body class="bg-light">
    <%= yield %>
  </body>
</html>

Tell mailer to use the Bootstrap email layout

Specifying this in the ApplicationMailer class configures the layout option for all the descendants of the ApplicationMailer class

# app/mailers/application_mailer.rb

class ApplicationMailer < ActionMailer::Base
  layout 'bootstrap-mailer'
end

Create stylesheet for bootstrap email template

Create a new stylesheet /app/assets/stylesheets/application-mailer.scss and import bootstrap-email. This is where your custom styles and overrides that you want to be inlined should live.

// /app/assets/stylesheets/application-mailer.scss

@import 'bootstrap-email';

Precompiling the stylesheet

Add this line in /config/initializers/asset.rb to compile your new SASS file.

Rails.application.config.assets.precompile += %w( application-mailer.scss )

Create the view file

Create the view file /app/views/sample_mailer/welcome_email.html.erb. Any instance variables you had in the welcome_email method in the file app/views/products_mailer/sample_email.html.erb can be used in this view file.
Let's use this as an example view file for sending the email.

<div class="card">
  <div class="card-header">
    Welcome
  </div>
  <div class="card-body">
    <h5 class="card-title">Welcome <%= name %></h5>
    <p class="card-text"><%= welcome_message %></p>
    <a href="#" class="btn btn-primary">Platform</a>
  </div>
</div>

And finally, send the email

To send the email, you need to do this in your application

welcome_message = "Hello your're welcome to our platform!"
name = current_user.name
email_to = current_user.email
subject = 'Welcome Email'

SampleMailer.welcome_email(welcome_message, name, email_to, subject).deliver if email_to

References

The resources used for creating this tutorial are listed below

Cover Image credits: Mathyas Kurmann on Unsplash

Filter records in a many to many relationship in rails

Masroor Hussain — Tue, 08 Dec 2020 21:55:32 +0000

We might find ourselves in a situation where we have a many-to-many relationship between two models and we want to filter the records of these models.
Let's say we have Product and Tag models, with a join model called ProductsTag, the names of the tables being products, tags and products_tags respectively.

Table   |-products-|   |----products_tags----|   |-tags-|
columns |    id    |   | product_id , tag_id |   |  id  |

If we want to filter out products having some specific tags, then we will be executing a query like this

    SELECT *
      FROM products
INNER JOIN products_tags
        ON products.id = products_tags.product_id
INNER JOIN tags
        ON tags.id = products_tags.tag_id
     WHERE tags.id IN (comma separated tag ids here)

Explanation:
We join the products table with the join table products_tags and then join its result with the tags table, then we filter the result of these two joins by using the where clause.
There might be other ways of doing this, but here I'm going to demonstrate this using scopes.

# Product.rb
scope :filter_by_tag, ->(tag_ids_arr) { joins(:tags).where( tags: { id: tag_ids_arr } ) }

Now when you want to perform this filtering, do

tag_ids = [1,2,3,4,5]
result = Product.filter_by_tag tag_ids

We can do a bit more advanced filtering using scopes. Suppose our schema is

Table   |--products--|   |----products_tags----|   |--tags--|
columns |id, category|   | product_id , tag_id |   |id, name|

and now what we want to get is that; We want All the products of specific categories which have tags with some specific name.
Translating it to raw SQL

    SELECT *
      FROM products
INNER JOIN products_tags
        ON products.id = products_tags.product_id
INNER JOIN tags
        ON tags.id = products_tags.tag_id
     WHERE products.category IN (comma separated category values here) AND tags.name = 'some_name'

Our scope for this will become

# Product.rb
scope :custom_filtering, ->(categories_arr, tag_name) { joins(:tags).where(products: { category: categories_arr }, tags: { name: tag_name }) }

To use this filter

categories = ['Electronics', 'Health']
tag_name = 'XYZ'
result = Product.custom_filtering categories, tag_name

Cover Image credits: Sajad Nori on Unsplash

Use a particular column as a foreign key in rails

Masroor Hussain — Sun, 29 Nov 2020 18:41:44 +0000

In rails, to add a foreign key, we usually do

rails generate migration AddReferenceToXX mm:references

what this does is that it adds a foreign key to the XX table, and uses the mm model's attribute id as the referenced column. However, this id is not what we'd always want to use as a foreign key, what if we want to use some other column as a foreign key, let's say the column email? In this article, we're going to learn exactly that thing.

Suppose we have two models named GmailInbox and GmailThread, their relationship being that GmailInbox has many GmailThreads and a GmailThread belongs to only one GmailInbox, so it's a One-to-Many relationship.

We need a way to connect the GmailThread to a GmailInbox based on the email (because a Gmail account is identified based on the email)
GmailInbox: id, email
where email attribute will have a uniqueness constraint (i.e no duplicates allowed in this column since it is going to be a referenced column for a foreign key).
GmailThread: id, thread_id, inbox_email
we will use inbox_email attribute as a foreign key to refer to the Gmail inbox it belongs to.

Firstly, we will generate a migration, using

rails generate migration add_foreign_key_to_gmail_threads

Then we need to add uniqueness constraint on the column of the referenced table, after that, we'll add the foreign key to the gmail_threads table.

our migration file will be

def change
  add_index :gmail_inboxes, :email, unique: true
  add_foreign_key :gmail_threads, :gmail_inboxes, column: :inbox_email, primary_key: :email
end

the syntax explanation is

add_index :table_name, :column_name, unique: true

add_foreign_key :from_table, :to_table, column: :foreign_key_column, primary_key: :referenced_column

The last thing we need to do is to put the foreign key information in our models.

class GmailThread < ApplicationRecord
  belongs_to :gmail_inbox, foreign_key: 'inbox_email', primary_key: 'email', optional: true
end

and do the same in GmailInbox model

class GmailInbox < ApplicationRecord
  has_many :gmail_threads, foreign_key: 'inbox_email', primary_key: 'email'
end

and that's it.