Drupal DB Query Code to Fetch Active Users and Accessed Website Within last One Year

December 30, 2021

Introduction
Drupal DB Code
Mysql Query to fetch All users who accessed website within last 1 year

Introduction

Here, we will see the drupal code to fetch all the active users (not blocked) and who accessed the website within last year.

We will also convert the output to JSON and save the JSON in a file.

This can be useful if you want to fetch users who are inactive. You may want to send them an email to login, or you may want to disable their accounts due to inactivity.

Drupal DB Code


//1 year seconds
$seconds = 3600 * 24 * 365;
$newtime = time() - $seconds;

$baseFolder = 'your_folder/';

$query = \Drupal::entityQuery('user')
    ->condition('status', '1')
    ->condition('access', '0', '!=')
    ->condition('access', $newtime, '<=');
$uids = $query->execute();

foreach ($uids as $uid) {
  $filename = $baseFolder.$uid.'.json';

  $user = user_load($uid);
  $data = \Drupal::service('serializer')->serialize($user, 'json', ['plugin_id' => 'entity']);
  
  print('<br/>Writing to file: '.$filename);
  file_put_contents($filename, $data); 
}

If your users are a lot, you can use pagination.


//1 year seconds
$seconds = 3600 * 24 * 365;
$newtime = time() - $seconds;

$baseFolder = 'your_folder/';

$query = \Drupal::entityQuery('user')
    ->condition('status', '1')
    ->condition('access', '0', '!=')
    ->condition('access', $newtime, '<=')
    ->range(0, 100);
$uids = $query->execute();

foreach ($uids as $uid) {
  $filename = $baseFolder.$uid.'.json';

  $user = user_load($uid);
  $data = \Drupal::service('serializer')->serialize($user, 'json', ['plugin_id' => 'entity']);
  
  print('<br/>Writing to file: '.$filename);
  file_put_contents($filename, $data); 
}

Mysql Query to fetch All users who accessed website within last 1 year

select uid, name, status, from_unixtime(created), from_unixtime(changed), 
from_unixtime(access), from_unixtime(login) 
from users_field_data 
where status=1 and access!=0 
and access >= DATE_SUB(NOW(), INTERVAL 1 YEAR) 
limit 1000, 10;