2

I need to create an SQL query that finds all nodes of a certain type created X days ago, so I can get their IDs. Due to the massive number of nodes in the database, I would prefer to use a direct SQL query, rather than entityQuery. I was warned entityQuery can be slow if working with large volumes of data.

Anyway, I need to find where is the node created timestamp stored in the database. In Drupal 7, it is stored directly in the node table, but not in Drupal 8.

  • You can directly query the 'node_field_data' table but honestly the difference in performance with entityQuery will be small if you are setting a limit on the number of rows returned as you should if you want to do this in a single request. – Alfred Armstrong Nov 14 at 16:15
7

The created date is stored in the node_field_data table as a UNIX timestamp in a cloumn called created.

Here's the code you would use to fetch the nodes that has been created X days ago, using a direct select query.

$day = 86400; // One day in seconds.
$days = 3; // The amount of days ago to fetch from.

$nids = \Drupal::database()->select('node_field_data', 'n')
  ->addField('n', 'nid')
  ->condition('n.created', \Drupal::time()->getCurrentTime() - $days * $day, '>')
  ->execute()
  ->fetchField();

$nodes = \Drupal::entityTypeManager()->getStorage('node')->loadMultiple($nids);

Make sure to inject the database and the entity_type.manager services (if possible) when implementing the code yourself.

3

If you go into your Drupal database you should find a table called 'node_field_data'. In this table there is a column labeled 'created'. That will be your Unix timestamp for when the node was created.

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.