[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

Temporary table to join

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

Recommended Posts

Aggregate Rails enums from SQL
Cloud9 (Rails) from Github
Add & save from rails database creation
[Environment construction] Uninstall rails from local