SQL Statement snippets

    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;

    , '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

فضلاً إذا أعجبتك هذه الصفحة لاتنسى أن تقوم بمشاركتها