Filter BlogPosts by year using get()->filter[]

Questions
,
1

Silverstripe Version: 4.*

Question:

I’m trying to prepare the filter for my blog posts. Is it possible to use mysql YEAR(date) for my query in the filter?

BlogPost::get()->filter([
         'ParentID' => $blog->ID,
         'Categories.Title' => $kat,
         'Year(PublishDate)' => $year]);  // is there a syntax for this?
2

Generally, database-specific syntax isn’t available via the Silverstripe ORM, since it needs to support multiple databases. So, a couple of choices:

You can use SQLSelect or similar to build the query in a more manual way:

You can also use search modifiers to build your query within the ORM:

//Example only - You can use fancier means to build the start / end (eg. DateTime, php date, etc)
$year = 2021;
$start = $year . '/01/01';
$end = $year . '/12/31';

BlogPost::get()->filter([
  'ParentID' => $blog->ID,
  'Categories.Title' => $kat,
  'PublishDate:GreaterThanOrEqual' => $start,
  'PublishDate:LessThanOrEqual' => $end
]);

Now, the one thing I’m not sure about without actually testing it is whether you can use the methods on the DBDate field directly in the query (sorry, I should, but it’s been a while!)

So, try the following before you get too carried away!

BlogPost::get()->filter([
         'ParentID' => $blog->ID,
         'Categories.Title' => $kat,
         'PublishDate.Year' => $year
]);

If that doesn’t fly, then you could get fancier and add a method to the BlogPost class with an extension and filter against that… but I’ll wait to see what happens with the above first!

3

Should also be able to use the :StartsWith modifier:

$year = 2021;
BlogPost::get()->filter([
         'ParentID' => $blog->ID,
         'Categories.Title' => $kat,
         'PublishDate:StartsWith' => $year
]);
1 Like
5

Hi guys,
thank you for your insights :slight_smile:

Generally, database-specific syntax isn’t available via the Silverstripe ORM…
I know. I just used it to show what I need/want :slight_smile:

I solved it like this:

if(isset($_GET["year"]) && is_numeric($_GET["year"])) {
    $year = $_GET["year"];
} else {
    $year = date('Y');
}

BlogPost::get()->filter([
      'ParentID' => $blog->ID,
      'Categories.Title' => $kat,
      'PublishDate:GreaterThanOrEqual' => $year . '-01-01 00:00:00',
      'PublishDate:LessThanOrEqual' => $year . '-12-31 23:59:59']);

I added the check for sql injections - Are there any prepared functions for this in Silverstripe?

I tried variations ‘PublishDate.Year’ => $year and ‘Year(PublishDate)’ and ‘PublishDate:Year’ and so on before I wrote here. I don’t remember all the errors - every attempt produces a new one :smiley:

@kafka
I think I’ll try your idea. I think it could be safer if for some reason the date format changes.
And it’s one line :slight_smile:

4

Interesting… hadn’t occurred to me to treat it as a string comparison!
Would be interesting to see the kind of queries all these approaches produce and whether there are any major performance differences. (For the size of the data set, it’s probably irrelevant, but I find it interesting :nerd_face:)

1 Like
7

Just be careful here… that method relies on the date format being in a specific way, since it’s doing a string comparison. The GreaterThan, LessThan methods are doing a date comparison in the database (which is using a DateTime column type)… so I’d argue that this method would be more robust against format changes.

6

Yeah, that was a bit of a longshot on my part… the method you’re using now should be fine.

There are methods on the request class to read data from get variables, rather than accessing the superglobals directly. The request class does all the isset() checking, etc. and will give you a nice consistent result for the request object. The ORM handles the SQL injection as part of the filter() process.

If you’re in a controller method which is handling a request, you can generally get it as a parameter, eg,

public function showblogposts(HTTPRequest $request)
{
  $year = $request->getParam('year');
  //
  Rest of the code
 // 
}

Or you can grab the current request directly, eg. for methods which you are accessing from the template:

public function getPostsByYear()
{
  $request = Controller::curr()->getRequest();
  $year = $request->getVar('year');
  //
  ...
  //
}

You can still add any additional sanity checks too, eg.

$requestYear = (int) $request->getParam('year');
$year = ($requestYear > 2000) ? $requestYear : date('Y');
8

You can also use directly the where method, e.g.:

// `where("YEAR(PublisDate) = $year")` will probably work too,
// but it is prone to a PostgreSQL issue about quoting
BlogPost::get()
    ->where([
        'YEAR("PublishDate") = ?' => $year
    ])
    ->filter([
        'ParentID' => $blog->ID,
        'Categories.Title' => $kat
    ])
9

to use the MySQL YEAR() function in a SilverStripe database query.

To use the YEAR() function in a SilverStripe database query, you can use the SQLSelect class and call the selectFunction method on the SQLSelect object.

For example, you can use the following code to select all blog posts from a specific year:

$year = 2020;
$posts = BlogPost::get()
    ->where("YEAR(\"PublishDate\") = $year")
    ->sort("PublishDate DESC");

This will select all BlogPost records where the PublishDate field has a year value of 2020. The sort method is used to sort the results by the PublishDate field in descending order.

You can also use the selectFunction method to apply the YEAR() function to a field in the SELECT clause of the query. For example:

$posts = BlogPost::get()
    ->select(["YEAR(\"PublishDate\") AS Year"])
    ->sort("PublishDate DESC");

This will select all BlogPost records and add a new field to the results called Year, which contains the year value of the PublishDate field.