Filter OR condition / additive result / full-custom WHERE clause

[spmclaugh]

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 the sql_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!

[moebe]

you can do this with the following “trick”

OR Conditions in Looker - a nightmare or am I just thinking wrong Modeling

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…

[spmclaugh]

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…?

[dan-man]

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.

[spmclaugh]

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 more expression AS identifier clauses. Each identifier must match a column name from the SELECT * statement. In the output column list, the column that matches the identifier in a REPLACE clause is replaced by the expression in that REPLACE 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…?

[spmclaugh]

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!

[sarsasid]

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