Here's a SQL query that selects all of the unique month and year values from every instance of the class Post, which has a property, Date:

$months = DB::query("SELECT DISTINCT DATE_FORMAT(`Post`.`Date`, '%M') AS `Month`, DATE_FORMAT(`Post`.`Date`, '%Y') AS `Year` FROM `Post` ORDER BY `Post`.`Date` DESC");

I’d like to refine this query to include only Posts that are children of the Page running the query. Something like the following (except this doesn't actually work):

$months = DB::query("SELECT DISTINCT DATE_FORMAT(`Post`.`Date`, '%M') AS `Month`, DATE_FORMAT(`Post`.`Date`, '%Y') AS `Year` FROM `Post` ORDER BY `Post`.`Date` DESC, WHERE parentID = '". $this->ID . "' ");

What's the correct syntax?

Do you, by any chance, have a complete non-php-generated version of that query? That would make it easier.

Basically, DATE_FORMAT() AS Month won't work in any way at first. it should simply be "MONTH(`DateField_Here`)" If I'm not mistaken, same goes for the year.

You can't sort by Post.Date, if you use the AS feature, you want "SORT `Year`, `Month` DESC" method. Since you're disassembling the datefield, and trying to still address it as the original date-field, which is impossible, because you don't have a field "Date" in your query, you can't do that.

You are calling things, then sorting by it's originator. Considering the time I post this, it's just probably a small pointer to where it's going wrong.

Thanks.

The first query works to return unique months and years - it correctly lists all of the unique month and year values from all of the Posts in the database.

The part that's not working is filtering by ParentID, and it seems like it's a syntax problem. Here's the error from SilverStripe:

[User Error] Couldn't run query: SELECT DISTINCT DATE_FORMAT(`Post`.`Date`, '%M') AS `Month`, DATE_FORMAT(`Post`.`Date`, '%Y') AS `Year` FROM `Post` ORDER BY `Post`.`Date` DESC, WHERE ParentID = '8' You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE ParentID = '8'' at line 1

There happens to be a comma after your ORDERBY which shouldn't be there. Also, put the WHERE before the order by..

SELECT [..] FROM [..] WHERE [..] ORDERBY [..]