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;