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;

// Last access in 24 hours SELECT COUNT(u.uid) AS total FROM users_field_data AS u WHERE u.access >= UNIX_TIMESTAMP() - 86400;

600 = 10 minutes 900 = 15 minutes 3600 = hour 21600 = 6 hours

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

// Exclude weekends from a specific period between two dates (By Rakan, thanks)
// VALUE FOR (Friday+Saturday as a weekend): 0123334401222334011122340001123400012344001234440
// VALUE FOR (Friday+Saturday as a weekend): 0123334401222334011122340001123400012345001234440 (With case of Starting Saturday and finishing by Friday as 5 working days)
// VALUE FOR (Saturday+Sunday as a weekend): 0123444401233334012222340111123400001234000123440
// NOTE: If the created and finished were on the same day, the working days will be 0 days. Even if the created date has been in a weekend day and finished in the first working day it will be 0 working days.
// Source: https://stackoverflow.com/questions/1828948/mysql-function-to-find-the-number-of-working-days-between-two-dates/6762805#6762805
SELECT (5 * (DATEDIFF(finished_at, created_at) DIV 7) + MID('0123334401222334011122340001123400012344001234440', 7 * WEEKDAY(created_at) + WEEKDAY(finished_at) + 1, 1)) AS days,
FROM mytable 
where DATEDIFF(finished_at, created_at) <= 3

// PHP Code to generate the long number | M T W T F S S -|-------------- M| 0 1 2 3 3 3 4 T| 4 0 1 2 2 2 3 W| 3 4 0 1 1 1 2 T| 2 3 4 0 0 0 1 F| 1 2 3 4 0 0 0 S| 1 2 3 4 4 0 0 S| 1 2 3 4 4 4 0 <?php $day_off = [4,5]; $days = [0,1,2,3,4,5,6]; $days_list = [0,1,2,3,4,5,6]; $result = []; foreach ($days as $i1 => $current_day) { $workdays = 0; $result[$i1] = [ 0 => 0, 1 => 0, 2 => 0, 3 => 0, 4 => 0, 5 => 0, 6 => 0, ]; foreach ($days_list as $i => $day_to_compare) { if ($current_day == $day_to_compare) { $result[$i1][$day_to_compare] = 0; continue; }; if (!in_array($day_to_compare, $day_off)) { $workdays++; } $result[$i1][$day_to_compare] = $workdays; } unset($days_list[$i1]); $days_list[] = $i1; } foreach ($result as $res) { print implode($res, ''); }

// Getting largest DB tables
SELECT TABLE_NAME AS Table, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1034) AS Size (GB) FROM information_schema.TABLES WHERE TABLE_SCHEMA = "drupal" ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC limit 10;
// count duplicated rows with same data value from queue table
SELECT data, COUNT() AS duplicates FROM queue GROUP BY data HAVING COUNT()>1 ORDER BY duplicates DESC;
// Get the biggest (max length rows)
SELECT * FROM queue ORDER BY LENGTH(data) DESC LIMIT 1;

Share this post