I’m looking for a way to have my Explore’s generated SQL
include a WHERE
clause that includes OR
logic.
This wouldn’t be:
... WHERE (field1 = 'user_value') AND (field2 = 'hard-coded value 1' OR field3 = 'hard-coded value 2'
Rather, it would be:
... WHERE (field1 = 'user_value') OR (whatever-I-want)
The documentation for sql_where
states:
If you forgot to add the parentheses in this example, and a user added their own filter, the generated
WHERE
clause could have the form:WHERE user_filter = 'something' AND region = 'Northeast' OR company = 'Periaptly'
In this situation, the filter that the user applied may not work. No matter what, rows with
company = 'Periaptly'
will show up, because the AND condition is evaluated first. Without parentheses, only part of thesql_where
condition combines with the user’s filter.
That’s exactly what I want!! But neither sql_where
nor sql_always_where
behave that way in reality!
Is there any other way to get the behavior I want? I want to fully override the SQL
WHERE
clause with a custom behavior of my choosing. I have some pre-filtered derived tables, each containing rows that are filtered according to table-specific fields, and I essentially want to show all rows where the table-specific filters are met. Columns not appearing in one table at all will show up as NULL
in the final results; I am OK with that, it’s the expected behavior – any filters on that field should only limit results that come from that table’s source.
The only way I can think to achieve the desired behavior is to switch to using Liquid for EVERYTHING, and having separate dimensions for both “things to include in the result table” (can_filter:no
) and filter-only fields (Liquid) and manually applying all of the filters by hand. That’s neither very good for maintainability nor clear to the user (“why can’t I just filter on X like I’m used to?”)… There must be a better way.
I saw some google results for a post titled “filtering between multiple fields with or using a yesno
dimension”, but it looks like that post is no longer available. Not sure if it’d help or not…
What I essentially want to do is include an “OR TRUE
” (no parenthesis) in the main query’s WHERE
clause. Having the end-user use a custom filter with the relevant OR
logic is not a viable option… The OR
is implied by the nature of what I’m doing.
I could make some kind of a join
where each row includes the results from its “source table” plus the results from the most recent [matching] row of each other table, but that’s both non-performant and confusing to the user: NULL
is more straightforward to the user than including some “nearby data”.
Thanks in advance for any help!
you can do this with the following “trick”
Little known trick: if you use a filter-type field, you can define a very custom SQL for it that will be used in the where clause. filter: department { type: string suggest_dimension: company_department sql: {% condition department %} ${company_department} {% endcondition %} OR {% condition department %} ${product_owner} {% endcondition %} ;; } (The department inside of the {% condition %} tag may be optional when a field is referring to it’s own filter selection, I forget, bu…
Thanks @moebe! I gave that a try, making the following filter in my base View:
filter: include_or_true {
sql: OR TRUE ;;
}
Resulting SQL with one user-defined filter:
WHERE (source_1 = 'user-input-here') AND (OR TRUE)
Any other ideas? I wonder if this behavior was changed in a recent Looker release maybe…?
This is still extremely nasty, but you can, I think, make use of the precendence of ORs vs ANDs in SQL, in combination with the fact that the `sql_always_where` seems to be the last clause in the generated sql.
explore: mytable {
sql_always_where: /*hackstart*/ TRUE) OR ${mytable.field} = "something" AND (TRUE /*hackend*/;;
...
This then produces something like this.... (returns a row for both 42 and -42)
SELECT x
FROM UNNEST([-42, 9, 42]) AS x
WHERE x > 0 AND x = 42 AND (/*hackstart*/ TRUE) OR x = -42 AND (TRUE /*hackend*/)
here the UNNEST(...) AS x is a dummy table for demonstration purposes, and the x > 0 andx = 42 bits are regular filters applied in the query.
Out of desperation, I’m considering going with BigQuery’s SELECT * REPLACE(...)
syntax, and injecting this SQL as the first statement in the Explore’s SELECT
clause:
BigQuery documentation:
SELECT * REPLACE
A
SELECT * REPLACE
statement specifies one or moreexpression AS identifier
clauses. Each identifier must match a column name from theSELECT *
statement. In the output column list, the column that matches the identifier in aREPLACE
clause is replaced by the expression in thatREPLACE
clause.A
SELECT * REPLACE
statement does not change the names or order of columns. However, it can change the value and the value type.WITH orders AS (SELECT 5 as order_id, "sprocket" as item_name, 200 as quantity) SELECT * REPLACE ("widget" AS item_name) FROM orders;
So… : I would define a dummy dimension and force it to be selected in every query, like:
# leading underscore to try to make this always at the top
dimension: _sql_inject {
can_filter: no
# Below: have to figure out a way to include "WHERE source!=source_1"
sql: * REPLACE (NULL as source_1), NULL ;;
}
Then I’d LEFT JOIN
data that I am certain would match the filter for source_1
, to trick the WHERE
clause, but it wouldn’t show up and confuse the user, because I’d replace it back with NULL
above.
Terribly convoluted; I really don’t want to do this, it’d be a wretched hack even for me 😉
Have to decide which is easier for me / clearer to the user: some hack like that, or Liquid for every filterable field in my project… Like I said, there must be a better way to get something as basic as a logical OR
condition…?
Well, I figured out one hack that’s slightly less awful than the above one 😉
explore: my_explore {
from: base_view
always_filter: {
filters: [my_explore.end_block_comment: "sql-injection"]
}
join: first_joined_view {
...
sql_where: TRUE /* ;;
}
...
}
view: base_view {
...
filter: end_block_comment {
sql: */ ;;
}
}
It messed up the highlighting in the IDE, and it’s probably super fragile, but it “seems to work” and stops user-applied filters from getting applied to the main query… Still looking for suggestions on better ways, though!
Hi,
I also had similar requirement for a global OR condition and problem was that Looker was generating chained AND conditions with brackets.
All I wanted is a global OR condition ,as left outer join on a two tables generated NULLs and I wanted the filter to be applied in the following way
`( regions.name IS NULL OR regions.name = <filtered_value> )`
The approach I did is as follows
view main {
dimension: region {
type: string
sql: ${TABLE}.region
can_filter: no
}
filter: region_filter {
type: string
suggest_dimension: region
full_suggestions: yes
sql: ${main.name} IS NULL OR {% condition region_filter %} ${main.name} {% endcondition %};;
}
}
Now we will expose the region_filter on dashbaords and the best thing was that if we give suggest_dimension the Links between various filters was working fine even when defined as filter
Thanks,
Sarath