Create chart REST API

[rsaleev]

Hi,
I want to create a chart only via API, see payload below, but I can't achieve the row ordering by column that not used in the query via columns or all_columns .
{ "datasource_id": 54, "datasource_type": "table", "dashboards": [ 183 ], "owners": [ 1 ], "params": "{\"all_columns\":[\"Metric Name\",\"Value\"],\"adhoc_filters\":[{\"expressionType\":\"SIMPLE\",\"subject\":\"Table\",\"operator\":\"ILIKE\",\"comparator\":\"Process Data\",\"clause\":\"WHERE\",\"sqlExpression\":null}],\"dashboards\":[183],\"order_by_cols\":[\"[\\\"index\\\", true]\"]}", "query_context": "{\"datasource\":{\"id\":54,\"type\":\"table\"},\"queries\":[{\"filters\":[{\"col\":\"Table\",\"op\":\"ILIKE\",\"val\":\"Process Data\"}],\"columns\":[\"Metric Name\",\"Value\"],\"extras\":{\"having\":\"\",\"where\":\"\"},\"orderby\":[\"[\\\"index\\\", true]\"]}],\"row_limit\":1000}", "slice_name": "Test2", "viz_type": "table" }

This payload in request creates the chart, even form is properly filled

But there is an error in dashboard view

When I click the save button in chart menu then chart is viewed properly on dashboard. How to solve this issue?

[dosubot[bot]]

Hey @rsaleev, I'm here to help you with any bugs, questions, or contributions you have. Let's tackle this challenge together!

The issue you're encountering is related to the SQL error that arises because the column "index" is used in the ORDER BY clause but is not included in the GROUP BY clause or used in an aggregate function. This is a common SQL requirement when using ORDER BY with columns that are not aggregated.

To resolve this issue, you need to ensure that the "index" column is either included in the GROUP BY clause or used in an aggregate function. Since you're creating the chart via the API, you should adjust the query_context to include the "index" column in the GROUP BY clause if it is not being aggregated.

Here's what you can do:

1. Modify the query_context: Ensure that the "index" column is included in the GROUP BY clause within the query_context. This will prevent the SQL error by satisfying the SQL requirement.

2. Check the params field: Make sure that the order_by_cols parameter is correctly formatted and that the "index" column is handled appropriately in the SQL query.

If the "index" column is not meant to be aggregated, including it in the GROUP BY clause should resolve the error. If it should be aggregated, consider applying an appropriate aggregate function.

Unfortunately, the specific handling of columns in the ORDER BY clause that are not part of the SELECT statement is not detailed in the available information, so you may need to experiment with the API payload to achieve the desired result ^[1].

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Bug Report | Other}