Ruby Programmer, not the robot jewellery kind.

Apr 4, 2016

Custom Selects in MySQL

The Why

We had a situation with a project recently where we were trying to display tens of thousands of rows of data. It needed to be displayed in the browser, it needed to be fast, and it needed to be formatted nicely (not just a data dump).

As such, the dates had to be readable, first and last names were to be concatenated to one cell and boolean values should return ‘True’ or ‘Approved’ rather than 1.

Using Ruby/Rails for this was slow. We were calling the database multiple times from within the view to query the models, and doing that per row was taking us past the 30 second timeout limit.

So we decided to populate all of the table’s data in the controller, doing a single call to the database, creating an array of arrays from this and just outputting this row by row, cell by cell in the view.

And it worked! Times were brought down from over 30 seconds per report to sub-second renders.

The How

In Ruby, we built a MySQL query string up, passed it to MySQL through an ActiveRecord::Base connection to get back a Mysql2::Result, and just looped through this in the view.

sql_string = %Q( SELECT DISTINCT

~interesting selects here~

FROM xxxx

WHERE yyyy

GROUP BY zzzz)

@results = ActiveRecord::Base.connection.execute(sql_string)

The interesting part of this was actually finding ways to format the data into exactly what you wanted, within MySQL.

Concatenating fields

CONCAT | CONCAT_WS

SELECT concat(“Frank“, “ “, “Smith“) AS fullname
-> 'Frank Smith'

SELECT concat(u.first_name, “ “, u.second_name) AS fullname
-> 'Bob Smith'

SELECT concat(“Peter“, NULL, u.second_name) AS fullname
-> NULL

SELECT concat(12) AS fullname
-> '12'

SELECT concat_ws(“ “, u.first_name, u.second_name) AS fullname
-> 'Bob Smith'

SELECT concat_ws(NULL, u.first_name, u.second_name) AS fullname
-> NULL

SELECT concat_ws(“ “, u.first_name, NULL, u.second_name) AS fullname
-> 'Bob Smith'

This returns the string result of the concatenated arguments. However, if any of the arguments are NULL, the entire result will be NULL.

Concat_ws stands for ‘Concatenate With Separator’, where the first argument will be used as the separator for the rest of the arguments. Unlike ‘concat’, ‘concat_ws’ will only return NULL if the first argument is NULL, any following NULL arguments are simply ignored.

GROUP_CONCAT

SELECT group_concat(DISTINCT concat(f.name, ‘ (‘, f.type ‘ )’) ORDER BY f.name DESC, SEPARATOR ', ‘) AS food_groups
-> 'Apple (fruit), Carrot (vegetable), Orange (fruit)'

SELECT group_concat(DISTINCT concat(fr.first_name, ' ', fr.second_name) SEPARATOR ', ') AS friends
-> 'John Doe, Jane Doe'

SELECT group_concat(DISTINCT concat(fr.first_name, ' ', fr.second_name) SEPARATOR '----') AS friends
-> 'John Doe----Jane Doe'

SELECT group_concat(DISTINCT fr.first_name) AS friends
-> 'John,Jane'

A group_concat lets you grab the data from a JOIN table and display the results in a single cell, separated however you want. Calling DISTINCT tells the database to only return unique items, and passing a SEPARATOR formats the result into a more readable state, otherwise it will return a comma separated list but with no space between the results.

Coalescing fields

COALESCE | IFNULL

SELECT COALESCE(NULL, 1);
-> 1

SELECT COALESCE(NULL, NULL, NULL);
-> NULL

SELECT COALESCE(u.updated_at, u.created_at, 0)
-> 2013-03-17 11:30:57

SELECT IFNULL(u.num_friends, 0)
-> 12

SELECT IFNULL(u.first_name, 'No First Name Set')
-> 'No First Name Set'

Coalesce returns the first non-null value found in a list, or null if no non-null value is found. I found this useful in the latter case in order to ensure I was always dealing with an integer value. IFNULL is similar to coalesce but will only take one value, checking if it’s null and, if so, falling back to the given default value.

Date/Time fields

DATE_FORMAT | DAYNAME | MONTHNAME

SELECT DATE_FORMAT(u.sign_in_date, “%d %b %Y %H:%i“)
-> '14 Sep 2012 11:25'

SELECT LEFT(DAYNAME(u.sign_in_date), 2)
-> 'Fr'

SELECT MONTHNAME(u.sign_in_date)
-> 'September'

- Bear in mind this uses slightly different syntax to ruby strftime

Notable differences:

Hour, padded digits (01 through 12)
%I : Ruby
%h : MySQL

Minutes, padded digits (00 through 59)
%M : Ruby
%i : MySQL

Weekday name (Sunday through Saturday)
%A : Ruby
%W : MySQL

Month name (January through December)
%B : Ruby
%M : MySQL

Week (0 through 53) where Monday is the first day of the week
%W : Ruby

Boolean fields

IF |CASE

SELECT CASE
 WHEN t.approved = TRUE THEN "Approved"
 WHEN t.approved = FALSE THEN "Declined"
 ELSE “Pending"
END AS approved
-> 'Pending'

SELECT IF(t.completed_at IS NOT NULL, ‘Complete', 'Incomplete')
-> 'Complete'

IF / CASE statements are great for formatting boolean values into readable data. While IF-ELSE statements do exist in MySQL, I personally find CASE statements more readable when you’re dealing with more than 2 potential outputs.

In Conclusion

I’ve found that being able to wrangle raw SQL data into a human-readable format has been useful in a variety of ways, from increasing report output speeds by looping through pre-formatted data to simply being able to understand your database query results at a glance.