Joomla 3x sql field NOT Filtering on Change

Question

I am using Joomla 3x sql field to filter between two select fields.. The problem is that the second field does not filter as expected. It is just populated with all the result and when I select a different value nothing happens.

    <field name="ads_type"
        type="sql"
        label="Select Type of Content"
        sql_select="e.type, e.id, e.state"
        sql_from="#__zoo_item AS e"
        sql_group="type"
        sql_order="e.id ASC"
        key_field="type"
        value_field="type"
    />  

    <field name="ads_content"
        type="sql"
        label="Select Content"
        sql_select="e.name, e.id, e.state"
        sql_from="#__zoo_item AS e"
        sql_group="name"
        sql_order="e.id ASC"
        sql_filter="ads_type"
        key_field="id"
        value_field="name"
    />

I will be glad if someone can help me.

Answer 1

Last week I faced the same problem, (at March 2017, version 3.6.5) it seems to me that this feature is not working or even not implemented. After digging the code I can't see any hook attached to dropdown to catch the change event. Maybe the next version.

For now I needed some pretty hacky tweaks to my module.

The idea: I need a subform with dependent dropdowns form master->slave tables linked by virtuemart_custom_id field

Master id field: virtuemart_custom_id

Slave id field: customsforall_value_id

The slave id fields will become:

{virtuemart_custom_id}_{customsforall_value_id}

In my module I will extract the desired value from the right side of the underscore character.

We need:

• Capture the change event on master.
• Determine the target dropdown.
• Disable all options that not match {selected_value}_
• Destroy the jquery-chosen object and recreate filtering the disabled.

Also we need to do this process with all dropdowns when page is loaded.

In my module.xml, the description section

<description><![CDATA[
    <script type="text/javascript">
        <!--
            jQuery(window).on('load', function(event, row){
                jQuery('.masterSelect.chzn-done').each(function() {
                    var targetfield = 'valorcustom';
                    var chosenvalue = jQuery(this).val();
                    var glue='';
                    if(this.id.indexOf('__') != -1) glue='__';
                        else glue='_';
                    var parentrow = jQuery(this).closest('.subform-repeatable-group');
                    var targetdropdown = '#jform_params'+glue;
                    targetdropdown += jQuery(parentrow).data('baseName')+glue;
                    targetdropdown += jQuery(parentrow).data('group')+glue+targetfield;
                    jQuery(targetdropdown+' option').prop('disabled', true);
                    jQuery(targetdropdown+' option[value^='+chosenvalue+'_]').prop('disabled', false);
                    var previousvalue = jQuery(targetdropdown).val();
                    jQuery(targetdropdown).chosen('destroy');
                    jQuery(targetdropdown).chosen({display_disabled_options: false});
                    jQuery(targetdropdown).val(previousvalue);
                });
            });
        -->
        </script>
      ]]></description>

The field that contains the subform

<field name= "filtroscustom"
        description= "Filtros Custom"
        type= "subform"
        label= "Filtros Custom"
        min= "1"
        max= "1000"
        required= "true"            
        formsource= "modules/mod_adv_virtuemart_product/subcustom.xml"
        multiple= "true"
        buttons= "add,edit,remove"
    layout="joomla.form.field.subform.repeatable-table"
        groupByFieldset="false"
    />

The subform .xml

<?xml version="1.0" encoding="UTF-8"?>
<form>
    <field name="virtuemart_custom_id"
        type="sql"
        label="Field"
        sql_select="e.virtuemart_custom_id, e.custom_title"
        sql_from="#__virtuemart_customs AS e"
        sql_where="e.custom_element='customfieldsforall'"
        sql_order="e.custom_title ASC"
        key_field="virtuemart_custom_id"
        value_field="custom_title"
        class="advancedSelect masterSelect"
        onchange="
        var targetfield = 'valorcustom';
        var chosenvalue = jQuery(this).val();
        var glue='';
        if(this.id.indexOf('__') != -1) glue='__';
            else glue='_';
        var parentrow = jQuery(this).closest('.subform-repeatable-group');
        var targetdropdown = '#jform_params'+glue;
        targetdropdown += jQuery(parentrow).data('baseName')+glue;
        targetdropdown += jQuery(parentrow).data('group')+glue+targetfield;
        jQuery(targetdropdown+' option').prop('disabled', true);
        jQuery(targetdropdown+' option[value^='+chosenvalue+'_]').prop('disabled', false);
        jQuery(targetdropdown).val('');
        jQuery(targetdropdown).chosen('destroy');
        jQuery(targetdropdown).chosen({display_disabled_options: false});
        "
    >
        <option value="0">Choose field</option>
    </field>
    <field name="valorcustom"
        type="sql"
        label="Values"
        sql_select="concat(e.virtuemart_custom_id, '_', e.customsforall_value_id) as myindex, e.customsforall_value_name"
        sql_from="#__virtuemart_custom_plg_customsforall_values AS e"
        sql_order="e.customsforall_value_name ASC"
        key_field="myindex"
        value_field="customsforall_value_name"
        multiple="multiple"
        class="advancedSelect"
    />

targetfield must be the name of target field, in my case 'valorcustom'

targetdropdown id must be defined this way:

• If its outside a subform will be '#jform_params_'+targetfield

• If it's inside a subform will be '#jform_params'+glue+{basename}+glue+{group}+targetfield

In my case was the second option.

glue can be a single underscore character if the row is added on the fly or two underscore characters if the row is already loaded with module.