Last tested: May 6, 2020
I have an access_filter for one of my explores. But I'd like to use it for a view instead so that it will automatically come through for all explores which use this view.
We can replicate the functionality of access filters in views by referencing the user attribute in the WHERE clause of the sql_table_name or derived table sql parameter.
explore: customer {
access_filter: {
field: customer.name
user_attribute: allowed_customers
}
}
2. If the view is not a derived table, we can add a WHERE clause to the sql_table_name
parameter like so:
view: customers {
# old version
# sql_table_name: public.customers;;
# new version with added WHERE clause
sql_table_name: (SELECT * FROM public.users
WHERE customer.name = {{ _user_attributes['allowed_customers']}}) ;;
}
Q: This works, but what if I have multiple values inside that user attribute? Can I dynamically change between '=' and 'IN' based on the number of values inside the user attribute?
Sure! We can use Liquid to check whether the value has a comma. If so, then we can use IN instead of =.
view: customers {
# old version
# sql_table_name: public.customers;;
# new version with added WHERE clause
sql_table_name: (SELECT * FROM public.users
WHERE customer.name
# check for comma (multiple user attribute values)
{% if _user_attributes['allowed_customers'] contains ","%}
# if comma, use IN
IN
{% else %}
=
{% endif %}
{{ _user_attributes['allowed_customers']}}) ;;
}
This content is subject to limited support.