Forums

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

momentis 28 May, 2013
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
momentis 03 Jun, 2013
Is anyone still monitoring these boards?
GreyHead 03 Jun, 2013
Hi Rick,

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

Bob
momentis 03 Jun, 2013
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
GreyHead 03 Jun, 2013
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
Max_admin 06 Jun, 2013
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
Max
ChronoForms developer...
Did you try ChronoMyAdmin for managing your Joomla database tables ?
thehitechpanky 19 Jun, 2013
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
Max_admin 21 Jun, 2013
Hi,

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

Regards,
Max
Max
ChronoForms developer...
Did you try ChronoMyAdmin for managing your Joomla database tables ?
This topic is locked and no more replies can be posted.