Multi-table relation (one-to-many) with a WHERE clause

I am trying to use CC to build a list of users from the Joomla user table (user) that belong to certain user groups (table: user_usergroup_map - group_id's 11 and 12). I just cannot this to work, and I do not know why. Here's what I have for the multi-tables settings:

GENERAL:
Enabled: Yes
Main table: prefix_user_usergroup_map
ModelID: groups
Join Rule: userinfo.id = groups.user_id (userinfo is the ModelID of the main users table)
Related to: userinfo

ADVANCED:
Hierarchical: No
Relation type: One To Many
Foreign key: user_id
Left foreign key: <blank>
Where attachment: group_id = 11 (I have also tried using groups.group_id = 11 to no avail)
Order by: <blank>
Limit: <blank>

I have entered nothing on the OPERATIONS or FIELDS tabs.

No combination I have tried can make this work. Any ideas? I am so confused!!

Thanks!
Rick

Is anyone still monitoring these boards?

Hi Rick,

Nope, I don't read them any more - just post random replies. That seems to get better results.

Bob

Bob,

I am sorry to offend you, Bob. I know you are on here, but the posts to the CC boards seem to sit, and I know you don't spend much time in those. It was not my intention to make anyone mad!!!!!

Rick

Hi Rick,

I'm not mad, just entertained. Trying to do too much at the moment - and the sun is shining too. I do leave the CC ones until I feel I have time to give a decent answer. I'll see if I can work this out.

Bob

Hi,

Sorry, I should be checking this forum every few days, but I got very busy with the new V5 development recently!

Please try to change the where sql:

`groups`.`group_id` = '11'

You can also try placing it under the main WHERE SQL box.

You should also check the connection's debug, simply check if the generated SQL statement is correct ? or post it here and we can check it ? you may also run it on your phpmyadmin.

Regards,
Max

Same Issue, in my case

GENERAL:
Enabled: Yes
Main table: j16_community_users
ModelID: users
Join Rule: people.id=users.userid (people is the ModelID of the previous relations table)
Related to: people

ADVANCED:
Hierarchical: No
Relation type: One To One
Foreign key: userid
Left foreign key: <blank>
Type: INNER
Where attachment: users.profile_id=9 (I have also tried using profile_id=9 to no avail)
Order by: <blank>
Limit: <blank>

Still All Profile_ids are being displayed

Hi,

Please try to add your where extension to the main SQL code box under the first step instead!

Regards,
Max