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

Not possible to sort by Custom Expression fields #6239

Open
M4zD4i opened this issue Oct 23, 2017 · 16 comments
Open

Not possible to sort by Custom Expression fields #6239

M4zD4i opened this issue Oct 23, 2017 · 16 comments

Comments

@M4zD4i
Copy link

@M4zD4i M4zD4i commented Oct 23, 2017

  • Your browser and the version: Safari 11.0 (12604.1.38.1.7)
  • Your operating system: MacOS Sierra 10.12.6
  • Your databases: Apache Impala
  • Metabase version: fork from 0.26.1
  • Metabase hosting environment: Docker
  • Metabase internal database: Postgres

Steps to reproduce:

  1. Create a Custom Expression field in View section of query Builder
  2. Create some query displaying this field
  3. Column with this field is not sortable

It seems as the similar problem with Metric columns: #5943 and Custom field columns: #6238

@dfx413

This comment has been minimized.

Copy link

@dfx413 dfx413 commented Apr 2, 2019

please please fix this

@flamber

This comment has been minimized.

Copy link
Member

@flamber flamber commented Jul 10, 2019

This is partially fixed - tested with 0.32.9.
You can now click the Custom Expression column header and sort asc/desc.
image
But you cannot start by using the picker, since the Custom Expression is not available in the dropdown:
image

@anoopk19

This comment has been minimized.

Copy link
Contributor

@anoopk19 anoopk19 commented Jul 27, 2019

Listing the custom expression as a Sortable column in the query builder is really important. It has a huge impact on the usability factor.

@mazameli

This comment has been minimized.

Copy link
Contributor

@mazameli mazameli commented Aug 1, 2019

Same situation in release-0.33.x so far: you can sort custom expressions with the column heading on a table, but not in the notebook Sort step.

sort

notebook-sort

@mazameli

This comment has been minimized.

Copy link
Contributor

@mazameli mazameli commented Sep 30, 2019

I'm now finding on our stats instance that I can't even sort a custom expression from the column heading anymore.

@tlrobinson, do you think solving this bug might be made easier by some of the recent changes and improvements you've made to the custom expressions/columns code?

@mazameli

This comment has been minimized.

Copy link
Contributor

@mazameli mazameli commented Oct 22, 2019

Bumping priority — lots of folks are running into this.

@npuglisi

This comment has been minimized.

Copy link

@npuglisi npuglisi commented Jan 23, 2020

@mazameli
Sorting with heading column doesn't show to me, I have a custom expression and I can't sort this column at all.

image

image

image

@mazameli

This comment has been minimized.

Copy link
Contributor

@mazameli mazameli commented Jan 27, 2020

@npuglisi Sorting by the column heading for custom columns does appear to work on master, contrary to what I'd said back in September. Which version are you using?

@npuglisi

This comment has been minimized.

Copy link

@npuglisi npuglisi commented Feb 6, 2020

Hi @mazameli

Thank you for you answer

I learned to make this work, I have to use the button at the bottom after created a Custom Column, and then I could sort the column.
But the problem is the error that I got when I use it, like pressing the Pre Visualize (at the red marked)
image

image

If I see the SQL, the MKBE column dont exist for real, because the alias of expression is “expression”
image

Do you know how to fix it?

My configs:
{
“browser-info”: {
“language”: “pt-BR”,
“platform”: “Win32”,
“userAgent”: “Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.88 Safari/537.36”,
“vendor”: “Google Inc.”
},
“system-info”: {
“java.runtime.name”: “OpenJDK Runtime Environment”,
“java.runtime.version”: “11.0.5+10”,
“java.vendor”: “AdoptOpenJDK”,
“java.vendor.url”: “https://adoptopenjdk.net/”,
“java.version”: “11.0.5”,
“java.vm.name”: “OpenJDK 64-Bit Server VM”,
“java.vm.version”: “11.0.5+10”,
“os.name”: “Linux”,
“os.version”: “4.4.0-128-generic”,
“user.language”: “en”,
“user.timezone”: “GMT”
},
“metabase-info”: {
“databases”: [
“postgres”,
“sqlserver”
],
“hosting-env”: “unknown”,
“application-database”: “postgres”,
“run-mode”: “prod”,
“version”: {
“date”: “2020-01-13”,
“tag”: “v0.34.1”,
“branch”: “release-0.34.x”,
“hash”: “265695c”
},
“settings”: {
“report-timezone”: “America/Sao_Paulo”
}
}
}

@morganhankins

This comment has been minimized.

Copy link
Contributor

@morganhankins morganhankins commented Feb 6, 2020

(At this point this issue contains an ambiguous report description, mostly out of date info and old-UI screenshots. Maybe a new issue should be created?)

On master, I tried a few scenarios:

  • Broken: Grouped Summary Column with any Custom Expression (ie 'Sum(Quantity) + 1')
    Problem: Try to add a 'Sort', but the column doesn't show up
    Problem: Visualize the results, click on the column header, sort options don't show up (and js error thrown)

  • Also Broken: Custom Column that references a Grouped Summary Column with Custom Expression
    Problem: Appears to work: you can use the builder to add a sort for the Custom Column, but when you try to preview/run the query, you get the 'column source.summary_col doesn't exist' error that @npuglisi mentions on Feb 6th.
    Note: this case is almost certainly caused by the previous case.

  • Working: Custom Column with Custom Expression (no summary column ref)
    Working: Grouped Summary Column with Basic Metric (eg 'Count of rows')
    No problem builder-sorting, header-sorting or using as a variable in a Basic Metric.

@npuglisi Does this description of the problem sound correct to you?

@npuglisi

This comment has been minimized.

Copy link

@npuglisi npuglisi commented Feb 10, 2020

@morganhankins
That solution will not work for me because I need to summarize the result =/

KM / Qtde
image

And I need to group this metric for Motorista, and in summarize I cant do that with a custom column.

I'm really sad about it because my solution is incomplete without a sort =//
I just need to order my summarize metric =(((

@npuglisi

This comment has been minimized.

Copy link

@npuglisi npuglisi commented Feb 12, 2020

If I create a custom column and give the ‘expression’ name for my custom metric, I could sort the results, but only with the custom column.
I have a lot of filters for this question, and the problem is it only information that I group on my metric is used on filter, others filters is no avaliable anymore. =(

image

now I have only one filter
image

the way it was before
image

@ignaski

This comment has been minimized.

Copy link

@ignaski ignaski commented Feb 13, 2020

I am using the latest version (v0.34.2) and I can not make it working. I have a dozen of custom expression metrics but I am unable to use it in sorting neither in the visualized results (table) nor via +sort button in the editor... Unfortunately this is a blocker for me, because I can not even make a simple "top performers" question without using custom sql query.
image
image
image

@npuglisi

This comment has been minimized.

Copy link

@npuglisi npuglisi commented Feb 13, 2020

Change the name your metric 'profit' to 'expression'.
Before you sort, create a custom column using the custom metric (with the bottom button)
Then try to sort again (with the bottom button, again) and the expression will appears.
After you use the sort, you could delete the custom column.

@ignaski

This comment has been minimized.

Copy link

@ignaski ignaski commented Feb 13, 2020

Ok, so the problem is that the query builder names the custom expression as "expression", the 2nd one as "expression_2" instead of using the actual expression names.
SELECT "bi"."campaign_stats"."tracker_campaign_name" AS "tracker_campaign_name", (sum("bi"."campaign_stats"."ts_costs") / CASE WHEN sum("bi"."campaign_stats"."tr_conversions") = 0 THEN NULL ELSE sum("bi"."campaign_stats"."tr_conversions") END) AS "expression", ((sum("bi"."campaign_stats"."ts_revenue") - sum("bi"."campaign_stats"."ts_costs")) / CASE WHEN sum("bi"."campaign_stats"."ts_costs") = 0 THEN NULL ELSE sum("bi"."campaign_stats"."ts_costs") END) AS "expression_2" FROM "bi"."campaign_stats" GROUP BY "bi"."campaign_stats"."tracker_campaign_name" ORDER BY "bi"."campaign_stats"."tracker_campaign_name" ASC

To me it looks like it should be quite easy to fix this?

EDIT:
Filtering by custom expressions does not work for the same reason. Example of the query:
SELECT "source"."campaign_stats" AS "tracker_campaign_name", "source"."expression" AS "expression", "source"."expression_2" AS "expression_2" FROM (SELECT "bi"."campaign_stats"."tracker_campaign_name" AS "tracker_campaign_name", (sum("bi"."campaign_stats"."ts_costs") / CASE WHEN sum("bi"."campaign_stats"."tr_conversions") = 0 THEN NULL ELSE sum("bi"."campaign_stats"."tr_conversions") END) AS "expression", ((sum("bi"."campaign_stats"."ts_revenue") - sum("bi"."campaign_stats"."ts_costs")) / CASE WHEN sum("bi"."campaign_stats"."ts_costs") = 0 THEN NULL ELSE sum("bi"."campaign_stats"."ts_costs") END) AS "expression_2" FROM "bi"."campaign_stats" GROUP BY "bi"."campaign_stats"."tracker_campaign_name" ORDER BY "bi"."campaign_stats"."tracker_campaign_name" ASC) "source" WHERE ("source"."CPA" > '0' AND "source"."TS_ROI" IS NOT NULL)

In the where clause it uses the correct custom expression names. One more issue is "source"."CPA" > '0' if im not mistaken

@morganhankins

This comment has been minimized.

Copy link
Contributor

@morganhankins morganhankins commented Feb 14, 2020

@npuglisi your discovery that sorting works when using the name 'expression' was a great find! 🎖

@ignaski great research, I think that makes sense.

Specifically, metabase.query-processor.middleware.annotate is getting confused (maybe here?) as to weather to use the :name or :display_name option.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
9 participants
You can’t perform that action at this time.