Drupal Mysql Query to Fetch User Field Details and its Alias

December 30, 2021

Introduction

In this posr, we will see how to prepare mysql query to fetch user details, its fields, and its alias.

Mysql DB Query

select data.name, data.mail, from_unixtime(data.created), 
from_unixtime(data.changed), fname.field_full_name_value 
from users user  
left join user__field_full_name fname on user.uid = fname.entity_id  
left join users_field_data data on user.uid = data.uid  
left join path_alias alias on alias.path like CONCAT('/user/', user.uid) 
where data.status=1 limit 0,1000;

In above query,

  • I have converted integer timestamp for fields (created, changed) to date-time format by using from_unixtime function
  • Here, I have a field named full_name.
  • The alias scheme is /users/<username>

To get the output data as JSON

select JSON_OBJECT('name', data.name, 'email', data.mail, 
'created', from_unixtime(data.created), 'changed', from_unixtime(data.changed), 
'full_name', fname.field_full_name_value)  
from users user  
left join user__field_full_name fname on user.uid = fname.entity_id  
left join users_field_data data on user.uid = data.uid  
left join path_alias alias on alias.path like CONCAT('/user/', user.uid) 
where data.status=1 limit 0,1000;

Also see Drupal Code to Fetch Active users

drupalmysql

Similar Posts

Latest Posts