Data Where Query for multiple groups

[SoilentRed]

I?m using Fabrik in conjunction with JomSocial. I?m using the dabasejoin element to that a user can select another site member. I would like for the only names that appear to be members that share the same groups as the user. The example in the data-where documentation only specifies results from a single group.

WHERE {thistable}.`username`
IN (SELECT jos_users.username FROM jos_users, jos_user_usergroup_map
WHERE jos_users.id = jos_user_usergroup_map.user_id
AND jos_user_usergroup_map.group_id = 14)

FYI, the Joomla usergroup tables (jos_user_usergroup_map) stores the same user id data as the JomSocial usergroup details (jos_community_groups_members). user_id in jos_user_usergroup_map is the same value as memberid in jos_community_groups_members

Obviously a user can exist in multiple groups (in JomSocial AND Joomla). Would someone please help me form a query so that the only members presented are members that share the same groups as the user?

 

[SoilentRed]

Another tid-bid to consider: The value we are storing is with the databasejoin element is "email" and the label we are using is "name".

 

[troester]

Your dbjoin is going to jos_users? (jos_users.id is holding the userid)

Try

WHERE {thistable}.id IN
(
SELECT cm1.memberid FROM jos_community_groups_members cm1 WHERE cm1.memberid IN (SELECT cm2.groupid FROM jos_community_groups_members cm2 WHERE cm2.memberid = {$my->id})
)

(not sure if this can be done a bit simpler)

 

[SoilentRed]

Thanks! I'm not very sql savvy. What are the cm1 and cm2 referencing? Are those commands? Should I paste that in, as-is, or am I to replace that with something? Thank you for your patience.

 

[troester]

cm1 and cm2 are table aliases (long version would be "FROM jos_community_groups_members AS cm1", needed because the same table is used twice), you can use any name but you have to use the same names with the columns.

You can paste it as-is (if I didn't make a typo or get the wrong logic)

 

[SoilentRed]

Thanks. It's not working though.

Yes, the Database join element is connecting to jos_users. However, the value I'm storing is email and the label I'm displaying is name. Should it then be WHERE {thistable}.email IN or WHERE {thistable}.name IN? Not sure.

I am storing the user's id on submit in a hidden field called creator_id if that helps at all...

Thanks again for all your help!

 

[SoilentRed]

Just FYI, here is a list of all the variations I've tried. None of which work. Looking forward to your help!

WHERE {thistable}.id IN
(
SELECT cm1.memberid FROM jos_community_groups_members cm1 WHERE cm1.memberid IN (SELECT cm2.groupid FROM jos_community_groups_members cm2 WHERE cm2.memberid = {$my->id})
)

------------------------------------------------

WHERE {thistable}.'id' IN
(
SELECT cm1.memberid FROM jos_community_groups_members cm1 WHERE cm1.memberid IN (SELECT cm2.groupid FROM jos_community_groups_members cm2 WHERE cm2.memberid = {$my->id})
)

------------------------------------------------

WHERE {thistable}.email IN
(
SELECT cm1.memberid FROM jos_community_groups_members cm1 WHERE cm1.memberid IN (SELECT cm2.groupid FROM jos_community_groups_members cm2 WHERE cm2.memberid = {$my->id})
)

------------------------------------------------

WHERE {thistable}.'email' IN
(
SELECT cm1.memberid FROM jos_community_groups_members cm1 WHERE cm1.memberid IN (SELECT cm2.groupid FROM jos_community_groups_members cm2 WHERE cm2.memberid = {$my->id})
)

------------------------------------------------

WHERE {thistable}.name IN
(
SELECT cm1.memberid FROM jos_community_groups_members cm1 WHERE cm1.memberid IN (SELECT cm2.groupid FROM jos_community_groups_members cm2 WHERE cm2.memberid = {$my->id})
)

------------------------------------------------

WHERE {thistable}.name_raw IN
(
SELECT cm1.memberid FROM jos_community_groups_members cm1 WHERE cm1.memberid IN (SELECT cm2.groupid FROM jos_community_groups_members cm2 WHERE cm2.memberid = {$my->id})
)

------------------------------------------------

WHERE {thistable}.'name' IN
(
SELECT cm1.memberid FROM jos_community_groups_members cm1 WHERE cm1.memberid IN (SELECT cm2.groupid FROM jos_community_groups_members cm2 WHERE cm2.memberid = {$my->id})
)

------------------------------------------------

WHERE {thistable}.'name_raw' IN
(
SELECT cm1.memberid FROM jos_community_groups_members cm1 WHERE cm1.memberid IN (SELECT cm2.groupid FROM jos_community_groups_members cm2 WHERE cm2.memberid = {$my->id})
)

 

[troester]

Ah, yes, it should be
WHERE {thistable}.id IN
(
SELECT cm1.memberid FROM jos_community_groups_members cm1 WHERE cm1.groupid IN (SELECT cm2.groupid FROM jos_community_groups_members cm2 WHERE cm2.memberid = {$my->id})
)

 

[SoilentRed]

ROOOOOCCCCKKKK STAAAAARRR!!
Thank you very much and kudos to you.

One last thing. Is there a way to prevent the user from appearing in the list. It makes no sense for them to be able to select themselves. Other than that, this is perfect. Love Fabrik.

 

[troester]

WHERE {thistable}.id IN
(
SELECT cm1.memberid FROM jos_community_groups_members cm1 WHERE cm1.groupid IN (SELECT cm2.groupid FROM jos_community_groups_members cm2 WHERE cm2.memberid = {$my->id})
)
AND {thistable}.id <>{$my->id}

 

[SoilentRed]

Many thanks, Troester!