[SOLVED] Plan Integrations / SQL Actions

I am having some problems with SQL actions working properly under subscription plan Integrations tab. Some are working and some are not, although the SQL code is correct. I am wondering about the order of these actions and if multiple SQL codes are acceptable for a single SQL Action.

1. The tool tip for each SQL Action indicates that multiple queries are possible as long as they are separated by a ; and a line break. If multiple queries are OK, what is the purpose of having 5 different SQL Actions? Is there some advantage (or disadvantage) to having multiple queries in a single action?

2. Is the order of multiple queries important?

3. Is it required to put a ; at the end of the last query in a single action?

These are the SQL actions that I am trying to accomplish upon activation of a specific plan:

UPDATE `y2vfp_comprofiler` SET `cb_activemember`='1' WHERE `user_id` = [user_id];
UPDATE `y2vfp_comprofiler` SET `cb_usertype`='h' WHERE `user_id` = [user_id];
DELETE FROM `y2vfp_cbsubs_subscriptions` WHERE `y2vfp_cbsubs_subscriptions`.`user_id` = [user_id] AND `plan_id` = '3';
UPDATE `y2vfp_comprofiler` SET `approved`='0' WHERE `user_id` = [user_id] AND `group_id` = '13';
DELETE FROM `y2vfp_cbsubs_subscriptions` WHERE `y2vfp_cbsubs_subscriptions`.`user_id` = [user_id] AND `plan_id` = '10';
DELETE FROM `y2vfp_user_usergroup_map` WHERE `user_id` = [user_id] AND `group_id` = '13'

Is it better to have these grouped differently according to SQL Acton number? Or will a different order be more effective if they are all included in a single SQL Action?

It seems that it is the last 3 queries that are not working...

After researching I think I can see a problem:

UPDATE `y2vfp_comprofiler` SET `approved`='0' WHERE `user_id` = [user_id] AND `group_id` = '13';

How can I indicate that `group_id` comes from the table `y2vfp_user_usergroup_map` in the WHERE clause?

You'll need to join the usergroup map table. I'm sorry, but I cannot help you write custom SQL. That's outside the scope of support and we really need to start being more strict about it. We'll be considering a new subscription level that can provide some coding assistance.

For an easier approach you should be able to replace all of that with CB Auto Actions so you don't have to code anything.

The following would be Field actions.
The following would be CB Paid Subscription actions.
The following would be a User Group action.
The following would still need to be a query via a Query action, but you won't need the group_id in the query as you can do that in the conditions of the auto action. Will review adding support to the Field action to handle the approved/confirmed/blocked columns though so that won't need to be coded either.

Ok, new CB Auto Actions build release is now available to extend the Field action so you can now set user state from it. This means you'll be able to easily toggle approved state without needing to write a custom query and can just use a Field action for that.

If we are running only stable CB build releases on our site, is it OK for us to update only Auto Actions to the new nightly build? Or is it necessary to update all plugins to nightly build versions?

And which auto action trigger would we use for these if we want this to happen once successful payment has been made for plan_id = 1 (plan 1 has become active)?:

The following would be CB Paid Subscription actions.

Code:

DELETE FROM `y2vfp_cbsubs_subscriptions` WHERE `y2vfp_cbsubs_subscriptions`.`user_id` = [user_id] AND `plan_id` = '3'; DELETE FROM `y2vfp_cbsubs_subscriptions` WHERE `y2vfp_cbsubs_subscriptions`.`user_id` = [user_id] AND `plan_id` = '10';

With the Field auto action, it is now acting strangely upon saving. I set the action to set Approved to 0 like this:


But when I save it, the field selection keeps reverting back to Block:



Other auto actions of type Field created after installing the nightly build are also not working correctly. For example, I made an auto action of type Field, with no triggers or conditions and only 1 action: to set a comprofiler field to a certain value. But it doesn't work. I made the exact same one on the live site (running the stable version of CB plugins) and it works correctly.