Display on a dropdown the name of the registered users

Hi,

I want to show on a dropdown field with identifier id the name of the users that have assigned the "Registered" group on Joomla users section.

I am trying to make this SQL Select query With DB Read:

SELECT `User`.`id` AS `User.id`, `User`.`name` AS `User.name` FROM `z2ma5_users` AS `User`,`z2ma5_user_usergroup_map` AS `UserGroups` WHERE `User`.`id` = `UserGroups`.`user_id` AND `UserGroups`.`group_id`= '2';

I get this error:

Unknown column 'User.user_groups_user_id' in 'on clause' SQL=SELECT `User`.`id` AS `User.id`, `User`.`name` AS `User.name` FROM `z2ma5_users` AS `User` LEFT JOIN `z2ma5_user_usergroup_map` AS `UserGroups` ON `User`.`user_groups_user_id` = `UserGroups`.`user_id`

PLEASE, CAN YOU TELL ME WHAT I HAVE TO PUT ON DB READ SETTINGS?

Thanks. Regards,

Joaquín

Hi Joaquín,

The simplest way is probably to use a Custom Code action to set the query something like this

<?php
$db = \JFactory::getDBO();
$query = "
    SELECT `id`, `name`
        FROM `#__users` AS `Users`
        JOIN `#__user_usergroup_map` AS `UserGroups`
          ON `Users`.`id` = `UserGroups`.`user_id`
        WHERE `UserGroups`.`group_id` = '2' ;
";
$db->setQuery($query);
$form->data['Users'] = $db->loadAssocList();
?>

Bob

PS I tried the DB Read option but could not get it to work with these settings:

+ In the Dropdown > Dynamic Data: Enabled - Yes; Data path - Users; Value key - id; Text key - name

+ In the DB Read > Basic: Model ID - Users; Fields - id,name; Conditions -

<?php
return array('UserGroups.group_id' => 2);
?>

+ In the DB Read > Relations: Model - UserGroups; Type - HasOne; Foreign key - user_id