One query options dependent upon another query selects

In a pre-sale post ( www.joomlapolis.com/forum/87-presales/234523-hide-options-in-a-cb-query-based-on-selected-options-in-a-previoius-cb-query?limitstart=0 )
I asked:

If we have 2 CB Query fields, pulling their options both from the same table (and same query).
...
Question 2 - if the user select 'Option1', 'Options2', and 'Option3' in the first CBQuery field, can we have those options disabled (or hidden or something) in the second CB Query field (so they can't select them again).

Specific application is that the user needs to select topics. First select is the '3 favorite topics'. Second select is '3 additional topics I am interested in'.

Is there a way to do that within CB, the CB Query field (and any other add-ins)?

And nant replied:

Yes the latest CB Query field plugin lets you do this - see our video tutorials for examples.

I've watched all the videos (great job), and I've reviewed a few other text tutorials, but I can't figure out how to get this to happen.

The first field is: cb_reviewer_fiction_favcat
The second field is: cb_reviewer_fiction_othercat

In cb_reviewer_fiction_othercat's Parameters/Query I have:

However, because both fields are being populated on page display, both have all of the elements displayed.

I've also tried setting up a CB Conditional so that cb_reviewer_fiction_othercat is hidden until cb_reviewer_fiction_favcat is 'not empty'. The field then doesn't show until something is checked in the 'favcat' field, but it doesn't appear to have any effect to the contents of the values (i.e. the query results) in the 'othercat' field.

Are there instructions somewhere that explain how to handle this usecase?
Or can you tell me how to get this functionality working?

I did some searching and found:
www.joomlapolis.com/component/content/article?id=18533:configuring-dynamic-usa-state-city-zip-fields-with-cb-query-field-plugin&catid=107:use-cases&Itemid=129

And I found part of the problem.
The "Query Check Box (Multiple)" type is missing the "Update On" option in the Parameters|Query area.

So I switched back to a 'Query Drop-down (Multiple)" and it tries to work (wheel spins), but still doesn't filter the 2nd selector.

My guess is that '[cb_reviewer_fiction_favcat]' is not returning a single value, so "<>" won't work.

I tried using "id NOT IN '[cb_reviewer_fiction_favcat]' but that gives a mysql error about NOT IN ''

I'm not sure how to make these work, because I'm not sure exactly how the '[cb_reviewer_fiction_favcat]' returns the values from the first selection.

Could you tell me what format that is returning, and/or what the proper WHERE clause would be to get that to work?

The substitutions you can use in update on are based off what you're updating on. If I set my update on to Text (cb_text) then it can substitute in the new value of Text using [cb_text] in my query. When I change the value of Text it will ajax update whatever field has an update on with Text selected.

This all assumes your site has no errors. If the ajax response doesn't come back json it will fail. So you need to debug the HTTP request if it just sits there infinitely spinning. There also could be a problem with your query it self. To debug this press F12 then click the Network tab and trigger update on. You should see the HTTP request there and can check its response.

The Update On is supported for single and multi query select fieldtypes only. It can not work with checkbox/radio usages at this time.

Thanks, I tracked it down to a problem in my query.
I'm now able to get almost everything working.

IF the first field has just 1 value selected, then this query works on the 2nd one:

However, if the first has multiple values selected, that WHERE clause won't work, because the initial field contains something like:

51|*|12|*|34

Which leaves me with 2 additional questions:

1. What is the "*" that is inserted into the saved results
2. Do you have any samples of a WHERE clause that will work in this case? (i.e. properly exclude any in this query that were already selected in the first - [cb_reviewer_fiction_favcat]?)

NOTE: I've tried both a NOT IN as well as a NOT LIKE clause, but I'm not having the proper results (I'm guessing because of the "|"/Pipe delimiter and/or the "*" in the field).

If there are some docs, tutorial, or other that you can refer me to that would be great.

Sorry for not thinking about this earlier ... the solution (of course) is REGEXP.

I was finally able to get it working with the following query:

SELECT DISTINCT `id`, `name`
FROM #__classification_parent
WHERE `flag`=1 AND `parent`=0 AND NOT '[cb_reviewer_fiction_favcat]' REGEXP concat(':<:',`id`,':>:')
ORDER BY `name`

- hope this helps someone else

NOTE: It might be helpful for others to have this documented somehwere. Is there a 'users wiki' or something where I could contribute this 'solution'?