Possible? Complex databasejoin lookup?

[chris.paschen]

I've got a usecase that I'm not 100% sure that Fabrik can do.
I've spent a lot of time 'playing' with the various databasejoin options, but can't see exactly how to do this.

Table 1:
UserID
CategoryID

Table 2:
UserRecord (databasejoin using Table1)

The databasejoin needs to be able to lookup the current user - i.e. 'manager' (the person who is adding the record to Table 2) in Table 1 to find out their CategoryID. Then the databsejoin needs to only display those users - i.e. ' team member' (from Table1) who have the same CategoryID as the 'manager' and only make those 'team member's available for selection.

Then the kicker ... IF any user ('team member') changes their CategoryID in Table 1 (in the future) I need them to be removed from the 'manager's entry in Table2 where they don't match. [This could be a batch process if needed] (I do NOT need the system to automatically add them to a different 'manager' record at this point - it could actually just remove all of their records in Table 2 when they change their category).

Other details:
Table 1 is a comprofiler (Community Builder) table, so I can't 'manage' it from within Fabrik (but I can run code there on changing.

The 'kicker' section is just to help see what is the best db structure for Fabrik to make this all happen.

Anyone have ideas on this?
Do you think that it's possible, and if so, what type of db structure would work best?

Thanks for any help.

 

[troester]

Databasejoin:
in "Joins where and/or order by statement (SQL)" (Data-where tab) add something like
WHERE {thistable}.categoryID IN (SELECT a.categoryID from table1 a WHERE a.userID = {$my->id})

You can manage every DB table in Fabrik but I assume the "team members" will change their categoryID via CB?

 

[chris.paschen]

Thanks that gets me closer. But the {thistable}.categoryID won't work because the category for the users are all stored in the CB table.

However, does {$my->id} always return the user's Joomla ID number?
If so, then maybe I can just use that to first lookup the current user's Joomla ID, then query the CB table to find the current user's category THEN query to find all users in the CB table (except the current user) who have the same categoryID. WHEW ... that's going to be some complex query.

 

[troester]

Did you try?
I assume your databasejoin element is going to table1 (the CB table), then {thistable}.categoryID (literally {thistable}) is table1.categoryID.

then query the CB table to find the current user's category THEN query to find all users in the CB table (except the current user) who have the same categoryID

This is exactly
WHERE {thistable}.categoryID IN (SELECT a.categoryID from table1 a WHERE a.userID = {$my->id})

 

[chris.paschen]

troester,

Well, I FINALLY was able to get back to this project (and to trying fabrik for my project) and your final query worked PERFECTLY.

For those that are trying to filter a drop-down list (selector) to work with data from community builder, filtering on one custom CB field, this works. It allows you to list only other CB users who have the same 'categoryID' (or cb_categoryid to be specific) and display only those entries.

Thanks for clearing it up and I'm now saving this as a sample code for future fabrik database joins issues.