كتابات:
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 hoursSELECT 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