| Subject: | Invalid SQL from paging total |
I am running a query like this with some aggregate functions and paging:
my $rs = $schema->resultset("items")->search([
{},
{
'join' => [ "counts" ],
'+select' => [
{ 'sum' => "counts.qty" },
],
'+as' => [
"expected",
],
'group_by' => [ "me.countID" ],
'having' => {
'finalCount' => { '-ident' => "expected" },
},
'rows' => 100,
'page' => 1,
},
]);
This generates SQL like so:
SELECT ...
FROM (
SELECT me.itemID, ..., me.finalCount, SUM( counts.qty ) AS expected
FROM item me
LEFT JOIN count counts ON counts.itemID = me.itemID
GROUP BY me.itemID
HAVING finalCount = expected
LIMIT 100
) me
LEFT JOIN count counts ON counts.itemID = me.itemID;
However, when I try to get the total number of items (to calculate the total # of pages available), using:
my $itemCount = $rs->pager->total_entries;
It generates this SQL, which results in "Unknown column 'expected' in 'having clause'":
SELECT COUNT( * )
FROM (
SELECT me.itemID, me.finalCount
FROM items
LEFT JOIN count counts ON counts.itemID = me.itemID
GROUP BY me.itemID
HAVING finalCount = expected
) me
LEFT JOIN count counts ON counts.itemID = me.itemID