[RUBY] Aggregate Rails enums from SQL

Introduction

For security reasons, you may not be able to use the rails console in production. When investigating the data registered at this time, I think that you may investigate redash etc. with SQL from BI Tool. I will summarize how to display the enum at that time in an easy-to-read manner.

Aggregation method (method)

The role is defined in the enum as follows:

enum role: { user: 1, admin: 9 }

I will investigate the data like this.

name	role
test_1	1
test_2	1
admin_1	9
admin_2	9

Since it is SQL for investigation, we use subqueries without worrying about speed.


SELECT td.name, td.role, enum_code.name
FROM test_data AS td
JOIN
(
     SELECT 'user' AS name, 0 AS code UNION ALL
     SELECT 'admin' AS name, 9 AS code
) AS enum_code
ON td.role = enum_code.code;

You should see the role name with the following result:

name	role	role_name
test_1	0	user
test_2	0	user
admin_1	9	admin
admin_2	9	admin

Digression

The following is a digression, but I would like to explain additional information.

Tried environment

MySQL 14.14
- paiza
PostgreSQL 12.3
- Docker

Temporary table to join

If there is an official name for the temporary table, I would appreciate it if you could teach me.

SELECT 'user' AS name, 0 AS code

You can create a temporary table like this by running.

name	code
user	0

I made multiple lines and joined them line by line with ʻUNION ALL`.

name	code
user	0
admin	9

Then, join by column by JOIN.

name	role	role_name
test_1	0	user
test_2	0	user
admin_1	9	admin
admin_2	9	admin

Omission of FROM

https://oss-db.jp/dojo/dojo_09

In MySQL and PostgreSQL, you can write SQL statements without FROM. Since FROM cannot be omitted in Oracle and DB2, use FROM dual.

SELECT 'user' AS name, 0 AS code FROM dual;

https://docs.oracle.com/cd/E16338_01/server.112/b56299/queries009.htm https://www.ibm.com/support/knowledgecenter/ja/SSEPGG_11.1.0/com.ibm.db2.luw.apdv.porting.doc/doc/r0052874.html

with clause

PostgreSQL can use the with clause, so you can write a temporary table first. This writing method is used when creating a query for aggregation because the processing can be followed in order from the top. In the case of MySQL, it seems that it can be used from 8.0.

WITH enum_code AS (
    SELECT 'user' AS name, 0 AS code UNION ALL
    SELECT 'admin' AS name, 9 AS code
)
SELECT td.name, td.role, enum_code.name AS role_name
FROM test_data AS td
JOIN enum_code
ON td.role = enum_code.code;

https://www.postgresql.jp/document/12/html/queries-with.html https://dev.mysql.com/doc/refman/8.0/en/with.html