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 SELECT
s 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