List of users in specified group

[Sadler]

Hi,

I am trying to show a list of users (usernames) in a specific user group.

I have a form set up and I used a database join element to give me a dropdown list showing usernames with the value of each item being the user id. I wanted to restrict this to show only users within a specific user group so I added the following to the "data-where" tab in the additional join statements box:

LEFT JOIN foo_user_usergroup_map ON (foo_users.id=foo_user_usergroup_map.user_id) WHERE foo_user_usergroup_map.group_id=12

I am getting an error telling me I have a syntax issue but I am not able to see the problem. Anyone done something similar ?

Cheers

B

 

[Bauer]

Your 'Joins where' filter would be...
WHERE fooo_user.id IN ( SELECT DISTINCT(user_id) FROM #__user_usergroup_map WHERE group_id=12 )

 

[Sadler]

Hi,

thanks for that one, I was way off base. Here is the code that got it working:

WHERE foo_users_3.id IN ( SELECT DISTINCT(user_id) FROM #__user_usergroup_map WHERE group_id=12 )

I added the "s" to the table name and added "_3" which showed up in the error message, I am guessing that the system somewhere adds the suffix _3 to the table name. At any rate it works!

Thanks

Carl

 

[Bauer]

I'm not sure this solves the problem. I'm pretty sure the _3 was added by fabrik because there are other references to foo_users in the same form - or it is in reference to the list row Id. You may need to change that _3 to a _{placeholder} that represent the row_id - and I'm not sure off the top of my head what that would be.

If that is the case, then this might work for your test user - but will it work for another user with that hard-coded '_3' added? If it still works, then (in my best Roseanne Roseannadanna voice...) "Nevermind".