Join an existing table with a generated table of dates and count rows for each date?

Question

I would like to use this complex mysql query in my joomla model to generate a timeline and count entries within a specific date range.

My SQL Query is:

SELECT T.calendar, COUNT(DATE(M.date)) as number
FROM (
    SELECT (CURDATE() - INTERVAL c.number DAY) AS calendar
    FROM 
    (
        SELECT singles + tens + hundreds number FROM 
        ( 
            SELECT 0 singles
            UNION ALL SELECT   1 UNION ALL SELECT   2 UNION ALL SELECT   3
            UNION ALL SELECT   4 UNION ALL SELECT   5 UNION ALL SELECT   6
            UNION ALL SELECT   7 UNION ALL SELECT   8 UNION ALL SELECT   9
        ) singles
        JOIN 
        (
            SELECT 0 tens
            UNION ALL SELECT  10 UNION ALL SELECT  20 UNION ALL SELECT  30
            UNION ALL SELECT  40 UNION ALL SELECT  50 UNION ALL SELECT  60
            UNION ALL SELECT  70 UNION ALL SELECT  80 UNION ALL SELECT  90
        ) tens
        JOIN 
        (
            SELECT 0 hundreds
            UNION ALL SELECT  100 UNION ALL SELECT  200 UNION ALL SELECT  300
            UNION ALL SELECT  400 UNION ALL SELECT  500 UNION ALL SELECT  600
            UNION ALL SELECT  700 UNION ALL SELECT  800 UNION ALL SELECT  900
        ) hundreds
        ORDER BY number DESC
    ) c  
    WHERE c.number BETWEEN 0 AND 182
) AS T 
LEFT JOIN table_goals AS M ON T.calendar = DATE(M.date)
GROUP BY T.calendar`

How can I convert this into a "normal" Joomla JDatabase query?

Answer 1

You can just execute it in the same way than a simple query.

    db->setQuery($my_big_query);
    $row = $db->loadObject();

Of course, it would be very difficult to translate the SQL statement to build it with Query API $query->select(...)->from(...).

It is better to just handle the query with a big string, avoiding PHP performance bottlenecks.

    $query[] = 'SELECT ..';
    $query[] = 'FROM ..';
    $query[] = 'FROM ..';
    $query[] = '(SELECT ..';
    ...
    $query = implode(' ', query);

TIP: Remember to set big selects mode:

    $db = JFactory::getDbo();
    $db->setQuery('SET sql_big_selects=1');
    $db->query();

Answer 2

I find that the glaring disadvantage to your original sql code block is that it generates a 1000-row derived table, only to prune it back to the desired 182 rows -- this is an indirect approach and simply makes sql work harder than it needs to. (I do find it to be a clever approach regardless -- I wouldn't have considered doing that way.)

I will recommend generating your derived table consisting only of the date values required via php then LEFT JOIN, GROUP BY, and COUNT() your table_goals data for a clean, manageable, direct piece of code. The following will provide your intended result without ORDER BY, WHERE, nor a verbose, hardcoded subquery.

$db = JFactory::getDBO();
try {
    $days_ago = 182;
    for ($x = $days_ago; $x >= 0; --$x) {
        $unions[] = "SELECT " . date("'Y-m-d'", strtotime("-$x day")) . ($x == $days_ago ? " AS `date`" : "");
    }
    $derived_table = implode(' UNION ', $unions);

    $query = $db->getQuery(true)
                ->select("A.`date`, COUNT(B.`date`) AS `count`")
                ->from("($derived_table) A")
                ->leftJoin("table_goals B ON A.`date` = DATE(B.`date`)")
                ->group("A.`date`");
    // echo $query->dump();
    $db->setQuery($query);
    if (!$results = $db->loadAssocList()) {
        echo "Logic Error - Check the derived table generating code";
    } else {
        foreach ($results as $row) {
            echo "<div>{$row['date']} -> {$row['count']}</div>";
        }
    }
} catch (Exception $e) {
    echo "Syntax Error While Getting Timeline Data"; // , $e->getMessage(); // <- don't show getMessage() to the public
}

Some notes:

• Because the resultset order needs to be date ASC, I am running a decrementing for loop.
• The ($x == $days_ago) condition in the $union line reduces query bloat by only assigning the column alias to the first SELECT -- the subsequent SELECTs will inherit the column name.
• Look carefully, I have written the single quotes in the first parameter of date().
• I am electing not to call qn() to generate backticks on my hardcoded column names. This is a personal preference to reduce code bloat. This query doesn't have any insecurities, but you can call qn() on everything if you like.
• I am calling DATE() on your table_goals.date assuming that it is a DATETIME type field. If it is already a DATE type, then you can remove that call in the leftJoin() line.
• Using a LEFT JOIN is critical for the query logic. If you use an INNER JOIN you will disqualify rows where there were no "join-able" date value -- this would effectively mean that all zero-count rows would disappear AND if you wanted that kind of a result, then the derived table should be omitted from the code logic entirely.

When I tested the above snippet today (2018-06-19), this is the dumped query and displayed result rows:

Query Dump:

SELECT A.`date`, COUNT(B.`date`) AS `count`
FROM (SELECT '2017-12-19' AS `date` UNION SELECT '2017-12-20' UNION SELECT '2017-12-21'
UNION SELECT '2017-12-22' UNION SELECT '2017-12-23' UNION SELECT '2017-12-24'
UNION SELECT '2017-12-25' UNION SELECT '2017-12-26' UNION SELECT '2017-12-27'
UNION ... more rows ...
UNION SELECT '2018-06-15' UNION SELECT '2018-06-16' UNION SELECT '2018-06-17'
UNION SELECT '2018-06-18' UNION SELECT '2018-06-19') A
LEFT JOIN table_goals B ON A.`date` = DATE(B.`date`)
GROUP BY A.`date`

Output:

2017-12-19 -> 50
2017-12-20 -> 10
2017-12-21 -> 0
2017-12-22 -> 99
... more rows ...
2018-06-16 -> 382
2018-06-17 -> 152
2018-06-18 -> 20
2018-06-19 -> 0