[SOLVED] GroupJive Category based on CB Field

I have a new project for CBSubs which will require a group to be created in GroupJive for a particular subscription. This is fine as I have seen that the CBSubs GroupJive plugin can do this.

However, I noticed that you have to choose the category for the group to be created in. We have a single select drop down field called cb_sector which exactly matches the category names in GroupJive. What we need is for the group to be created in the category that the user has selected in their cb_sector field.

Is this possible currently or in the next version or CBSubs? If not, would it be possible to use the SQL Actions plugin to do this?

Many thanks in advance!

Post edited by: krileon, at: 2009/09/07 03:05

Easily done using SQL Actions. The majority of the plugins can be or are done with just queries. We used integration plugins to make it simple. However, for in-depth usage of a component I would suggest using queries. Yes, you can do this for groupjive with a query as SQL Actions supports substitutions.

Here are example queries, both will need to be ran. One creates the group. The other inserts the user into the group.

[code:1]
INSERT INTO `#__gj_groups` ( `name`, `descr`, `date_s`, `type`, `creator`, `user_id`, `active`, `category` ) VALUES ( 'My Group', 'My Group Description', NOW(), 1, '[username]', [user_id], 1, [cb_gjcategoryID] );
INSERT INTO `#__gj_users` ( `id_user`, `id_group`, `date`, `status` ) VALUES ( [user_id], '', NOW(), 'active' );
[/code:1]

Complex SQL is going to be needed to grab some IDs. Such as groupid and categoryid. Using a standard drop-down field from field management will not return a categories ID, but its name instead. So you need to do a select query inside the insert query to grab the ID.

Example:
[code:1]
INSERT INTO `#__gj_groups` ( `name`, `descr`, `date_s`, `type`, `creator`, `user_id`, `active`, `category` ) VALUES ( 'My Group - [user_id]', 'My Group Description', NOW(), 1, '[username]', [user_id], 1, ( SELECT `id` FROM `#__gj_grcategory` WHERE `catname` = '[cb_gjcategory]' ) );
INSERT INTO `#__gj_users` ( `id_user`, `id_group`, `date`, `status` ) VALUES ( [user_id], ( SELECT `id` FROM `#__gj_groups` WHERE `name` = 'My Group - [user_id]' ), NOW(), 'active' );
[/code:1]

As you can see I've done a select within an insert to get category ID and groupid. I've also changed the group name to be unique in order to grab its ID from database.

This is just an example! Ensure you make any necessary modifications and run any necessary testing. This has NOT been tested.

Amazing! Thank you so much krileon!

Great idea to do a nested select query, I can defiantly get it working from this!

Thanks again