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_unixtimefunction - 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
