Multiple custom filters to a named query

Hello!

I have a modified named query

SELECT DISTINCT
    Asset.asset AS "Activo",
    Asset.assetName AS "Descripcion",
    Asset.EquipmentType || '  <' || EquipmentType.EquipmentTypeName || '>' AS "Clase equipo",
    Asset.parentAsset AS "Activo padre",
    Asset.businessUnit AS "Ud. negocio",
    ALD.Value AS "Dato de nivel",
    Asset.companyLevel AS "Nivel",
    Asset.priority AS "Prioridad"
FROM
    Asset
LEFT JOIN EquipmentType ON Asset.equipmentType = EquipmentType.EquipmentType
LEFT JOIN AssetLevelData ALD ON Asset.asset = ALD.asset
WHERE
    Asset.company = 'APPLE' AND EquipmentType.company = 'APPLE'
    AND (:tableFilter IS NULL OR Asset.businessUnit = :tableFilter)
    AND (:nivelFilter IS NULL OR Asset.companyLevel = :nivelFilter)
ORDER BY
    CASE
        WHEN SubStr(Asset.businessUnit, 1, 3) IN ('CAT', 'BAL', 'MUR', 'VAL', 'AND', 'ARA', 'AST', 'CAL', 'EUS', 'GAL', 'NAV', 'RIO') THEN 2
        WHEN SubStr(Asset.businessUnit, 1, 5) = 'MAD-F' THEN 0
        WHEN SubStr(Asset.businessUnit, 1, 7) = 'MAD-PAR' THEN 0
        ELSE 1
    END,
    Asset.businessUnit,
    Asset.parentAsset,
    Asset.asset

I added the parameters tableFilter and nivelFilter so I can aply both filters at once

-tableFilter custom filter comes from a custom property on my view that comes from a multiselect dropdown

-nivelFilter comes from 4 checkboxes,
image

def transform(self, value, quality, timestamp):

    selected_levels = []
    if value['chk_5']:
        selected_levels.append('5')
    if value['chk_6']:
        selected_levels.append('6')
    if value['chk_7']:
        selected_levels.append('7')
    if value['chk_8']:
        selected_levels.append('8')
    
    
    return ','.join(selected_levels) if selected_levels else None

When I choose more than one it doesn´t show any data on the table, just when I choose 1 checkbox it works.

Should I aply the filter to the namedQuery data without a parameter instead?
as:
image

def transform(self, value, quality, timestamp):

    output = []
    for row in value['data']:
        nivel = row['Nivel']
        if value['chk_5'] and nivel == 5:
            output.append(row)
        elif value['chk_6'] and nivel == 6:
            output.append(row)
        elif value['chk_7'] and nivel == 7:
            output.append(row)
        elif value['chk_8'] and nivel == 8:
            output.append(row)
    return output

Cheers!

Because the equality is not true anymore.
Let's say Asset.companyLevel is 6. When you select the option that corresponds to 6, you get a match. But if you select another option, let's say 8, your parameter becomes "6, 8", and 6 is not equal to "6, 8".
I don't think there's a perfect solution to this with named queries, but you could explore:

  • using a query string parameter
  • use like '%:nivelFilter%' instead of an equality comparison
  • build your query dynamically with a dynamically built list of placeholders then use runPrepQuery
  • maybe you could solve this with sql scripts and stored procedures ?
1 Like

Thanks Pascal!

The approach where the query filter works without using parameters just Expression Structure script works fine. Now, I need to find a way to apply multiple filters using this method.

One potential solution is to use bound Expression Structure scripts. I can start by binding an Expression Structure script to the original named query. This script will handle the first filter. Then, I can apply another bound Expression Structure script to the result of the first one to handle the second filter. By repeating this process, I can apply multiple filters step by step.

image

def transform(self, value, quality, timestamp):

    output = []
    for row in value['data']:
        nivel = row['Nivel']
        if value['chk_5'] and nivel == 5:
            output.append(row)
        elif value['chk_6'] and nivel == 6:
            output.append(row)
        elif value['chk_7'] and nivel == 7:
            output.append(row)
        elif value['chk_8'] and nivel == 8:
            output.append(row)
    return output

After using this filter, aply another filter to the result and repeat it as many times as filter inputs I need.
image

Another option I may have is using dynamic query construction via script.

That was in the list:

2 Likes

ok, in case somebody wants to know what I did to aply the multiselect dropdown and checkboxes group as filters for the dataset, using a query directly on the script and not as a named query

I add a onclick event to my button so it does as follows:

	
	import system.dataset
	
	
	tab_container = self.getSibling("TabContainer")
	
	# Define the parameters for the query
	params = {
	    "tableFilter": tab_container.custom.tableFiler,  # This should be a list of strings
	    "nivelFilter": [tab_container.custom.nivelFilter_1]  # This should be a list of integers
	}
	
	# Ensure tableFilter is not empty
	if not params['tableFilter']:
	    params['tableFilter'] = ['DEFAULT_VALUE']  # Provide a default value or handle as needed
	
	# Convert the list of tableFilter values to a comma-separated string
	tableFilter_str = ",".join(["'{}'".format(item) for item in params['tableFilter']])
	
	# Convert the list of nivelFilter values to a comma-separated string
	nivelFilter_str = ",".join(map(str, params['nivelFilter']))
	
	# Construct the SQL query with parameters
	query = """
	SELECT DISTINCT
	    Asset.asset AS "Activo",
	    Asset.assetName AS "Descripcion",
	    Asset.EquipmentType || '  <' || EquipmentType.EquipmentTypeName || '>' AS "Clase equipo",
	    Asset.parentAsset AS "Activo padre",
	    Asset.businessUnit AS "Ud. negocio",
	    ALD.Value AS "Dato de nivel",
	    Asset.companyLevel AS "Nivel",
	    Asset.priority AS "Prioridad"
	FROM
	    Asset
	LEFT JOIN EquipmentType ON Asset.equipmentType = EquipmentType.EquipmentType
	LEFT JOIN AssetLevelData ALD ON Asset.asset = ALD.asset
	WHERE
	    Asset.company = 'APPLE AND EquipmentType.company = 'APPLE'
	    AND Asset.businessUnit IN ({tableFilter})
	    AND Asset.companyLevel IN ({nivelFilter})
	ORDER BY
	    CASE
	        WHEN SubStr(Asset.businessUnit, 1, 3) IN ('CAT', 'BAL', 'MUR', 'VAL', 'AND', 'ARA', 'AST', 'CAL', 'EUS', 'GAL', 'NAV', 'RIO') THEN 2
	        WHEN SubStr(Asset.businessUnit, 1, 5) = 'MAD-F' THEN 0
	        WHEN SubStr(Asset.businessUnit, 1, 7) = 'MAD-PAR' THEN 0
	        ELSE 1
	    END,
	    Asset.businessUnit,
	    Asset.parentAsset,
	    Asset.asset
	""".format(tableFilter=tableFilter_str, nivelFilter=nivelFilter_str)
	
	# Execute the query
	try:
	    results = system.db.runQuery(query, "PRIMA")  #database connection
	    
	    #the table component has the path "TabContainer/TablaInventario"
	    self.parent.getChild("TabContainer").getChild("TablaInventario").props.data = system.dataset.toDataSet(results)
	    
	    # Log success message
	    self.getSibling("textArea-1").props.text = "Query executed successfully and table data updated."
	except Exception as e:
	    # Log the error
	    self.getSibling("textArea-1").props.text = "Error executing query: {}".format(e)

is working fine

That's not a prep query.
The proper way to do this is to put placeholders in your query ("?") and then pass the list of values to runPrepQuery.

Also, quick tip:
instead of

params = {
	"tableFilter": tab_container.custom.tableFiler,  # This should be a list of strings
	"nivelFilter": [tab_container.custom.nivelFilter_1]  # This should be a list of integers
}
	
# Ensure tableFilter is not empty
if not params['tableFilter']:
	params['tableFilter'] = ['DEFAULT_VALUE']  # Provide a default value or handle as needed

you can do this:

params = {
	"tableFilter": tab_container.custom.tableFiler or ['DEFAULT_VALUE'],  # This should be a list of strings
	"nivelFilter": [tab_container.custom.nivelFilter_1]  # This should be a list of integers
}
1 Like