Forum

Thread tagged as: Problem, Suggestions, Blog

Possible Bug in month_qty for Blog Archive

Hi

I have two distinct blog posts in the same month, but the month_qty count that is available in the months_month_link.html is returning the value 1, rather than the expected count of 2.

It seems the driving query is the following (which I found in PerchBlog_Posts.class) - note, this query contains the parameters as executed in my scenario...

SELECT DISTINCT
    year(postDateTime) AS year,
    month(postDateTime) AS month,
    CONCAT(year(postDateTime),"-",month(postDateTime),"-01") AS postDateTime,
    COUNT(*) AS month_qty
FROM perch3_blog_posts p
WHERE year(postDateTime) = '2017'
AND p.postStatus='Published'
AND p.postDateTime<='2017-06-08 08:20:00' 
GROUP BY year, month, postDateTime
ORDER BY month DESC

I think the confusion / problem comes from using the column alias of postDateTime in this code CONCAT(year(postDateTime),"-",month(postDateTime),"-01") AS postDateTime which is also in the Group By clause and is the same as a the actual table column name of postDateTime.

If I change the query to the following, the month_qty count is being correctly calculated as 2...

SELECT DISTINCT
    year(postDateTime) AS year,
    month(postDateTime) AS month,
    CONCAT(year(postDateTime),"-",month(postDateTime),"-01") AS pDT,
    COUNT(*) AS month_qty
FROM perch3_blog_posts p
WHERE year(postDateTime) = '2017'
AND p.postStatus='Published'
AND p.postDateTime<='2017-06-08 08:20:00' 
GROUP BY year, month, pDT
ORDER BY month DESC

So, using an alias of pDT that differs to the actual existing column name of postDateTime seems to work. Now, I don't know if this is a peculiarity of mySQL or whether I have done something else amiss.

What do you think?

Thanks

James
James Heanly

James Heanly 0 points

  • 4 years ago
Rachel Andrew

Rachel Andrew 394 points
Perch Support

4 years ago

Please post your Diagnostics Report.
James Heanly

James Heanly 0 points

4 years ago

Hi Rachel - Sorry should have done that first time around. Here it is.

Perch: 3.0.4, PHP: 5.6.10, MySQL: mysqlnd 5.0.11-dev - 20120503 - $Id: 3c688b6bbc30d36af3ac34fdd4b7b5b787fe5555 $, with PDO
Server OS: Darwin, apache2handler
Installed apps: content (3.0.4), assets (3.0.4), categories (3.0.4), perch_blog (5.5.1), perch_forms (1.9), perch_gallery (2.8.9)
App runtimes: <?php $apps_list = [ 'perch_forms', 'perch_blog', 'perch_gallery' ];
PERCH_LOGINPATH: /perch
PERCH_PATH: /Users/jamesheanly/Technical/Perch/uni-rugby/perch
PERCH_CORE: /Users/jamesheanly/Technical/Perch/uni-rugby/perch/core
PERCH_RESFILEPATH: /Users/jamesheanly/Technical/Perch/uni-rugby/perch/resources
Image manipulation: GD
PHP limits: Max upload 32M, Max POST 32M, Memory: 128M, Total max file upload: 32M
F1: 0c66c2e1f82f9e0b7617b2cb8270f2c7
Resource folder writeable: Yes
HTTP_HOST: localhost:8000
DOCUMENT_ROOT: /Users/jamesheanly/Technical/Perch/uni-rugby
REQUEST_URI: /perch/core/settings/diagnostics/
SCRIPT_NAME: /perch/core/settings/diagnostics/index.php
James Heanly

James Heanly 0 points

4 years ago

Hi Rachel - Just wondering if you have had a chance to investigate my query any further? For now I have removed the counts from being displayed.

Thanks,

James
James Heanly

James Heanly 0 points

4 years ago

By the way, I have updated to the latest version of Perch and the problem is still occurring. Diagnostic report below...

Perch: 3.0.8, PHP: 5.6.10, MySQL: mysqlnd 5.0.11-dev - 20120503 - $Id: 3c688b6bbc30d36af3ac34fdd4b7b5b787fe5555 $, with PDO
Server OS: Darwin, apache2handler
Installed apps: content (3.0.8), assets (3.0.8), categories (3.0.8), perch_blog (5.5.1), perch_forms (1.9), perch_gallery (2.8.9)
App runtimes: <?php $apps_list = [ 'perch_forms', 'perch_blog', 'perch_gallery' ];
PERCH_LOGINPATH: /perch
PERCH_PATH: /Users/jamesheanly/Technical/Bitbucket/newcastle-uni-rugby/perch
PERCH_CORE: /Users/jamesheanly/Technical/Bitbucket/newcastle-uni-rugby/perch/core
PERCH_RESFILEPATH: /Users/jamesheanly/Technical/Bitbucket/newcastle-uni-rugby/perch/resources
Image manipulation: GD
PHP limits: Max upload 32M, Max POST 32M, Memory: 128M, Total max file upload: 32M
F1: 0c66c2e1f82f9e0b7617b2cb8270f2c7
Resource folder writeable: Yes
HTTP_HOST: localhost:8000
DOCUMENT_ROOT: /Users/jamesheanly/Technical/Bitbucket/newcastle-uni-rugby
REQUEST_URI: /perch/core/settings/diagnostics/
SCRIPT_NAME: /perch/core/settings/diagnostics/index.php
Drew McLellan

Drew McLellan 2638 points
Perch Support

4 years ago

We'll certainly look at it when we next update Blog.