Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Expanding multiple optional clauses to include JOIN #8640

Closed
synweap15 opened this Issue Oct 5, 2018 · 8 comments

Comments

Projects
None yet
6 participants
@synweap15
Copy link

synweap15 commented Oct 5, 2018

The documentation regarding optional clauses states that:

To use multiple optional clauses you must include at least one regular WHERE clause followed by optional clauses, each starting with AND.

Is there a reason why the optional clauses are limited to that scope? My idea is that they could be used to build conditional joins. For example something like

[[JOIN study_year study_year_used ON (study_year_used.id = fscyp.study_year_id AND {{study_year}})]]

could boost performance of some queries with optional heavy drilldowns requiring lots of joins.

@synweap15 synweap15 changed the title Expanding multiple optional clauses to JOIN Expanding multiple optional clauses to include JOIN Oct 5, 2018

@salsakran

This comment has been minimized.

Copy link
Contributor

salsakran commented Dec 28, 2018

it should work in that situation.

@salsakran salsakran closed this Dec 28, 2018

@synweap15

This comment has been minimized.

Copy link
Author

synweap15 commented Mar 6, 2019

But it unfortunately does not work, checked with v0.31.2. When the join is conditional as in example above, the variable is of filter type, the join occurs whenever a value is given for this field or not. Any thoughts?

@flamber

This comment has been minimized.

Copy link

flamber commented Mar 6, 2019

Hi @synweap15
When using the Field Filter and you haven't defined anything in the parameter, then it's replaced with 1=1, which is why the JOIN is included all the time.

@salsakran Is there any way to avoid the join, when there's no parameter value? I've tried playing around with "complex default value" without success:
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#default-value-in-the-query

@synweap15

This comment has been minimized.

Copy link
Author

synweap15 commented Mar 6, 2019

Hey @flamber
Correct, and documentation doesn't point Field Filter type as a special type that would allow the optional clause to be rendered even if it's empty (1=1).

I imagine my case (don't join unless the join is filtered) isn't the only out there where an improvement to the optional clause logic would help :-)

@MassimilianoCuccia

This comment has been minimized.

Copy link

MassimilianoCuccia commented Mar 11, 2019

it does not work
I'm using metabase over mysql and the clause is not ignored.

please reopen this issue

@petragianlu

This comment has been minimized.

Copy link

petragianlu commented Mar 11, 2019

Hi,
I've the same issue,
When the variable is left empty, what I expect is that the whole condition inside the brakets [[]] is ignored, instead the variable is replaced with value '1=1';
So the join subqueries inside the [[]] don't work as expected.
How can I fix this?

@flamber

This comment has been minimized.

Copy link

flamber commented Mar 11, 2019

@camsaul Looks like there's problems with Optional Clauses - can you reopen this?

@prigal

This comment has been minimized.

Copy link

prigal commented Mar 12, 2019

Same problem for me, as discussed here
https://discourse.metabase.com/t/default-value-in-field-filter-date-variable-to-now/5605/2, I'm not able to set a default as long as variable is valuated (1=1).

That issue should be reopened.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.