sqlsnip

SQL Statement snippets

    SELECT
    td.name AS term_name
    , td2.name AS content_type
    , COUNT(meta.request_id) AS total_requests
    , SUM(meta.total_plans) total_plans
    , SUM(meta.invoice_total) AS total_amount
    , DATE_FORMAT(FROM_UNIXTIME(n.created), '%Y%m') AS date_of_the_request
    , DATE_FORMAT(FROM_UNIXTIME(meta.request_approved), '%Y%m') AS date_of_the_approval
    FROM the_meta_table meta
    INNER JOIN node AS n ON n.nid = meta.request_id
    INNER JOIN field_data_field_reference AS fr ON fr.bundle = 'the_content_type' AND fr.field_data_field_reference_target_id = meta.request_id
    INNER JOIN field_data_field_term_name AS mn ON mn.bundle = 'the_types' AND mn.entity_id = fr.entity_id
    INNER JOIN taxonomy_term_data AS td ON td.tid = mn.field_the_tid
    INNER JOIN taxonomy_term_data AS td2 ON td2.tid = 1
    WHERE meta.request_approved != ''
    GROUP BY td2.tid, td.tid
    ORDER BY td.tid ASC

// Webform getting total submissions, and join only one result for no reason :)
SELECT ws.webform_id, COUNT(ws.sid), DATE_FORMAT(FROM_UNIXTIME(ws.created), '%Y-%m')
FROM webform_submission ws
LEFT JOIN webform_submission_data wsd ON wsd.webform_id = ws.webform_id AND wsd.sid = (
    SELECT sid FROM webform_submission_data WHERE webform_id = ws.webform_id ORDER BY sid DESC LIMIT 1
)
LEFT JOIN webform_submission_log wsl ON wsl.sid = wsd.sid
WHERE ws.webform_id IN ('WEBFORM_ID1', 'WEBFORM_ID2', 'WEBFORM_ID3')
GROUP BY ws.webform_id

// Convert date to timestamp
UNIX_TIMESTAMP() // current timestamp
UNIX_TIMESTAMP('2022-06-15') // getting timestamp from a date


// Created users last 24 hours
SELECT COUNT(u.uid) AS total
FROM users_field_data AS u 
WHERE u.created >= UNIX_TIMESTAMP() - 86400;

SELECT
REPLACE(
    REPLACE(FIELD_NAME
    , 'yes', 'super hero')
    , 'no', 'not super hero')
    AS is_super_hero

// Getting latest watchdog logs in 24 hours


SELECT
REPLACE(
    REPLACE(
        REPLACE(
            REPLACE(
                REPLACE(
                    REPLACE(
                        REPLACE(
                            REPLACE(severity
    , 0, 'Emergency')
    , 1, 'Alert')
    , 2, 'Critical')
    , 3, 'Error')
    , 4, 'Warning')
    , 5, 'Notice')
    , 6, 'Info')
    , 7, 'Debug')
    AS severity_level

, COUNT(wid) AS total
FROM watchdog
WHERE timestamp >= UNIX_TIMESTAMP() - 86400
GROUP BY severity
ORDER BY severity

// Latest log entires
// CAST Hex field value of log variables
SELECT DATE_FORMAT(FROM_UNIXTIME(timestamp), '%Y-%m-%d %h:%i %p') AS time, uid, type, message, CAST(variables AS CHAR(10000) CHARACTER SET utf8) AS vars, severity, link, location, referer, hostname
FROM watchdog
WHERE severity IN (0,1,2,3)
ORDER BY timestamp DESC
LIMIT 50