Database table joins

[mlines]

I am not a programmer but I am attempting to use Fabrik to provide additional functionality to a Joomla package that I have loaded on my website. The package is Alphauserpoints which is a reward system for users engaging in the website.

Alphauserpoints (aka Altauserpoints) has a feature called "medals" where an admin (or automated process) can award users a medal. I want to add on a feature where the users can admin their own medals (award, delete, modify).

To do this I need to pick up information from the following tables.

jos_users - to get the Joomla username and ID,
jos_alphauserpoints - to get the Alphapoints user id (which is different to the Joomla ID, the Joomla ID is in a field within the table row.
jos_alphauserpoints_medals - each entry represents a medal that a user has, so from zero entries to X entries depending on how many the user has. Contains the medal type and the Alphapoints user id.
jos_alphauserpoints_rank - defines the medal types.

So I have successfully got a List working (to show the medals the logged in user has) and a Form (to allow the editing/deletion/modifying of medals.

To pick up the data I need to display, I use the join "Join" function within the data tab of List.

I am confused about the "ORDER" of the join and its effect on the operation of the list/form.

If I use jos_users as the "primary" entry ( ID>>Alphapoints ID>>Alphapoint Medals>>Alphapoint Rank) then all the displays work but any delete function works on the jos_users table, deleting the user instead of the medal.

If I use alphauserpoints_medal as the "primary" entry then ADD/MODIFY/DELETE works just fine except in the case where the user has no medals in the first instance when I get a "Duplicate ENtry for id=0" entry when adding. I presume this is because there is no userid reference in a blank new medal to follow through the joined tables to get the real user id.

Basically the "master" userid is jos_users and I want to add/delete/modify info in the medals table without affecting the other tables, including starting from a zero medal point.

Any advice on the ordering/setup I need to do this. I feel I am really "close but no medal" to coin a phrase.

 

[cheesegrits]

I would try, as the first simple experiment, a list built on jos_alphauserpoints_medals, with no list joins.

Set a list pre-filter as:

WHERE
Field: rid
CONDITION: EQUALS
Value: SELECT id FROM #__alpha_userpoints WHERE userid = '{$my->id}'
Type: Query
Apply to: Public

This prefilter will mean users only see "their" medals, by matching the 'rid' (their alphapoints id) against the userid in the main #__alpha_userpoints table.

Set the 'medal' element to be a database join to the #__alpha_userpoints_levelrank table, with a WHERE clause of:

{thistable}.typerank = 1

... which will restrict it to just showing the medals in that table.

Make the 'rid' element hidden, with a Default of:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('id')->from('#__alpha_userpoints')->where('userid = ' . $this->user->get('id'));
$db->setQuery($query);
return $db->loadResult();

... and set Eval to Yes. This will set the 'rid' on medals they add to their own rid (by getting the id from the main userpoints table, looking up their user id.

And that should do it. You can tidy the list up by only showing the 'medal' and optionally reason / date in the list. Set the labels on the element how you want. Make sure the 'medaldate' is a date element set to "today's date" (and if it asks you to change type to DATETIME when you save it, just say no). Set the "Access" on the list so only registered users can use it.

That should give you a list you can then add a menu item for on the front end, where users will see their own medals, and can add / edit / delete them.

-- hugh

 

[cheesegrits]

Here's that list in action:

https://www.screencast.com/t/0jDQSF2GENhG

... and here's the constituent parts:

https://www.screencast.com/t/ShNbPIf9

It actually took longer to explain it and record the screencasts than it did to set it up.

-- hugh

 

[cheesegrits]

Note that you may want to improve it a little ... like you'll notice I can add the same medal twice (I only had one to test with) ... but you could extend that "where" clause on the medal join element to exclude any which already exist for that rid in the medals table.

-- hugh

 

[mlines]

Hugh,

Thank-you so much, thats fantastic. As I am not a programmer I was trying to do it within the selections available through the main fabric menu items rather than putting SQL querys into the EVAL parts which was beyond me.

In fact I want users to be able to add "medals" more than once as what I am actually using them for is to record and display what vehicles my forum members have on an enthusiasts website.



EDIT: I had a typing error that was causing an issue that I originally queried in this message. Correcting the error solved the issue so I have deleted the text from here.

Many thanks for this great step forward.

Martin

 

[cheesegrits]

Glad it worked for you.

-- hugh