2

I'm performing an entity query that searches for nodes of my custom content type called 'news'. The content type has a date field called 'field_date'. I want to be able to perform the query in a way that only produces the 'news' nodes that have a 'field_date' that fits within a specified year. Here is an attempt I made below.

$year = 2015;
$query = \Drupal::entityQuery('node')
  ->sort('field_date', 'DESC')
  ->condition('type', 'news')
  ->condition('field_date', [date('Y','field_date'), $year], '=')
  ->range(0, 10);

In the code above I'm attempting to only grab the year from the 'field_date' datefield and compare that to $year, 2015 in this case. However I receive SQL errors like:

Invalid parameter number: number of bound variables does not match number of tokens

I'm guessing part of it is date('Y','field_date') doesn't know what 'field_date' is but I'm not sure how to go about it.

How do I make a year comparison on a datefield in a entityquery condition?

4

Assuming the data in the database looks like this: "2015-10-12T21:30:00", you can use a like query

->condition('field_date', $year . '%', 'like')

Your code revised with the change:

$year = 2015;
$query = \Drupal::entityQuery('node')
  ->sort('field_date', 'DESC')
  ->condition('type', 'news')
  ->condition('field_date', $year . '%', 'like')
  ->range(0, 10);
  • Awesome worked. But I had to change fieldCondition to condition. – Matt Oct 17 '16 at 19:34
  • oops, that's correct, updated. – oknate Oct 17 '16 at 19:42
  • 1
    This is pretty inefficient, you're treating dates as text and running a full table scan. Dates are stored as either DATETIME_DATETIME_STORAGE_FORMAT or DATETIME_DATE_STORAGE_FORMAT (see datetime.module) and both of those have been designed so that they sort numerically, and the column is indexed, so you can build a new DateTime('2015-0-0...') - i.e. first second of the year - and just compare $mydt->format('DATETIME_DATETIME_STORAGE_FORMAT') to the column with a more than query. Add a "less than last second of 2015" condition and you've got "in 2015". – George Jul 10 '18 at 0:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.