Hi all, I have few small chart on My Dashboard that used to work on V13, most of them look like this:
After updated to V15, it give me an error of (1054, “Unknown column ‘_unit’ in ‘order clause’”), detail as follow
{“name”:“be295656da”,“owner”:“Administrator”,“creation”:“2023-12-28 19:00:48.201719”,“modified”:“2023-12-28 19:00:48.201719”,“modified_by”:“Administrator”,“docstatus”:0,“idx”:0,“seen”:1,“method”:“(1054, "Unknown column ‘_unit’ in ‘order clause’")”,“error”:“Traceback with variables (most recent call last):\n File "apps/frappe/frappe/app.py", line 110, in application\n response = frappe.api.handle(request)\n request = <Request ‘https://victoria.frappe.cloud/api/method/frappe.desk.doctype.dashboard_chart.dashboard_chart.get’ [POST]>\n response = None\n rollback = True\n e = OperationalError(1054, "Unknown column ‘_unit’ in ‘order clause’")\n File "apps/frappe/frappe/api/init.py", line 49, in handle\n data = endpoint(arguments)\n request = <Request ‘https://victoria.frappe.cloud/api/method/frappe.desk.doctype.dashboard_chart.dashboard_chart.get’ [POST]>\n endpoint = <function handle_rpc_call at 0x7f8b62056520>\n arguments = {‘method’: ‘frappe.desk.doctype.dashboard_chart.dashboard_chart.get’}\n File "apps/frappe/frappe/api/v1.py", line 36, in handle_rpc_call\n return frappe.handler.handle()\n method = ‘frappe.desk.doctype.dashboard_chart.dashboard_chart.get’\n frappe = <module ‘frappe’ from ‘apps/frappe/frappe/init.py’>\n File "apps/frappe/frappe/handler.py", line 49, in handle\n data = execute_cmd(cmd)\n cmd = ‘frappe.desk.doctype.dashboard_chart.dashboard_chart.get’\n data = None\n File "apps/frappe/frappe/handler.py", line 85, in execute_cmd\n return frappe.call(method, frappe.form_dict)\n cmd = ‘frappe.desk.doctype.dashboard_chart.dashboard_chart.get’\n from_async = False\n server_script = None\n method = <function get at 0x7f8b5f17d440>\n File "apps/frappe/frappe/init.py", line 1715, in call\n return fn(args, **newargs)\n fn = <function get at 0x7f8b5f17d440>\n args = ()\n kwargs = {‘chart_name’: ‘Altar Sales’, ‘filters’: ‘[["Sales Order Item","item_group","=","Altar",false]]’, ‘refresh’: ‘1’, ‘time_interval’: ‘’, ‘timespan’: ‘’, ‘from_date’: ‘’, ‘to_date’: ‘’, ‘heatmap_year’: ‘’, ‘cmd’: ‘frappe.desk.doctype.dashboard_chart.dashboard_chart.get’}\n newargs = {‘chart_name’: ‘Altar Sales’, ‘filters’: ‘[["Sales Order Item","item_group","=","Altar",false]]’, ‘refresh’: ‘1’, ‘time_interval’: ‘’, ‘timespan’: ‘’, ‘from_date’: ‘’, ‘to_date’: ‘’, ‘heatmap_year’: ‘’}\n File "apps/frappe/frappe/utils/typing_validations.py", line 31, in wrapper\n return func(args, **kwargs)\n args = ()\n kwargs = {‘chart_name’: ‘Altar Sales’, ‘filters’: ‘[["Sales Order Item","item_group","=","Altar",false]]’, ‘refresh’: ‘1’, ‘time_interval’: ‘’, ‘timespan’: ‘’, ‘from_date’: ‘’, ‘to_date’: ‘’, ‘heatmap_year’: ‘’}\n apply_condition = <function whitelist..innerfn.. at 0x7f8b5f17d3a0>\n func = <function get at 0x7f8b5f17d300>\n File "apps/frappe/frappe/utils/dashboard.py", line 26, in wrapper\n results = generate_and_cache_results(kwargs, function, cache_key, chart)\n args = ()\n kwargs = {‘chart_name’: ‘Altar Sales’, ‘filters’: ‘[["Sales Order Item","item_group","=","Altar",false]]’, ‘refresh’: ‘1’, ‘time_interval’: ‘’, ‘timespan’: ‘’, ‘from_date’: ‘’, ‘to_date’: ‘’, ‘heatmap_year’: ‘’}\n chart = <DashboardChart: Altar Sales>\n no_cache = None\n chart_name = ‘Altar Sales’\n cache_key = ********\n function = <function get at 0x7f8b5f17d260>\n File "apps/frappe/frappe/utils/dashboard.py", line 41, in generate_and_cache_results\n results = function(\n args = {‘chart_name’: ‘Altar Sales’, ‘filters’: ‘[["Sales Order Item","item_group","=","Altar",false]]’, ‘refresh’: ‘1’, ‘time_interval’: ‘’, ‘timespan’: ‘’, ‘from_date’: ‘’, ‘to_date’: ‘’, ‘heatmap_year’: ‘’}\n function = <function get at 0x7f8b5f17d260>\n cache_key = ********\n chart = <DashboardChart: Altar Sales>\n File "apps/frappe/frappe/desk/doctype/dashboard_chart/dashboard_chart.py", line 144, in get\n chart_config = get_chart_config(chart, filters, timespan, timegrain, from_date, to_date)\n chart_name = ‘Altar Sales’\n chart = <DashboardChart: Altar Sales>\n no_cache = None\n filters = [[‘Sales Order Item’, ‘item_group’, ‘=’, ‘Altar’, False], [‘Sales Order Item’, ‘docstatus’, ‘<’, 2, False], [‘Sales Order Item’, ‘transaction_date’, ‘>=’, ‘2022-01-01’, False], [‘Sales Order Item’, ‘transaction_date’, ‘<=’, datetime.datetime(2024, 1, 31, 0, 0), False]]\n from_date = datetime.date(2022, 1, 1)\n to_date = datetime.datetime(2024, 1, 31, 0, 0)\n timespan = ‘Select Date Range’\n time_interval = None\n heatmap_year = None\n refresh = None\n timegrain = ‘Monthly’\n File "apps/frappe/frappe/desk/doctype/dashboard_chart/dashboard_chart.py", line 210, in get_chart_config\n data = frappe.db.get_list(\n chart = <DashboardChart: Altar Sales>\n filters = [[‘Sales Order Item’, ‘item_group’, ‘=’, ‘Altar’, False], [‘Sales Order Item’, ‘docstatus’, ‘<’, 2, False], [‘Sales Order Item’, ‘transaction_date’, ‘>=’, ‘2022-01-01’, False], [‘Sales Order Item’, ‘transaction_date’, ‘<=’, datetime.datetime(2024, 1, 31, 0, 0), False]]\n timespan = ‘Select Date Range’\n timegrain = ‘Monthly’\n from_date = ‘2022-01-01’\n to_date = datetime.datetime(2024, 1, 31, 0, 0)\n doctype = ‘Sales Order Item’\n datefield = ‘transaction_date’\n value_field = ‘net_amount’\n File "apps/frappe/frappe/database/database.py", line 698, in get_list\n return frappe.get_list(args, **kwargs)\n args = (‘Sales Order Item’,)\n kwargs = {‘fields’: [‘transaction_date as _unit’, ‘SUM(net_amount)’, 'COUNT()‘], ‘filters’: [[‘Sales Order Item’, ‘item_group’, ‘=’, ‘Altar’, False], [‘Sales Order Item’, ‘docstatus’, ‘<’, 2, False], [‘Sales Order Item’, ‘transaction_date’, ‘>=’, ‘2022-01-01’, False], [‘Sales Order Item’, ‘transaction_date’, ‘<=’, datetime.datetime(2024, 1, 31, 0, 0), False]], ‘group_by’: ‘_unit’, ‘order_by’: ‘_unit asc’, ‘as_list’: True}\n File "apps/frappe/frappe/init.py", line 1981, in get_list\n return frappe.model.db_query.DatabaseQuery(doctype).execute(args, **kwargs)\n doctype = ‘Sales Order Item’\n args = ()\n kwargs = {‘fields’: [‘transaction_date as _unit’, ‘SUM(net_amount)’, 'COUNT()’], ‘filters’: [[‘Sales Order Item’, ‘item_group’, ‘=’, ‘Altar’, False], [‘Sales Order Item’, ‘docstatus’, ‘<’, 2, False], [‘Sales Order Item’, ‘transaction_date’, ‘>=’, ‘2022-01-01’, False], [‘Sales Order Item’, ‘transaction_date’, ‘<=’, datetime.datetime(2024, 1, 31, 0, 0), False]], ‘group_by’: ‘_unit’, ‘order_by’: ‘_unit asc’, ‘as_list’: True}\n frappe = <module ‘frappe’ from ‘apps/frappe/frappe/init.py’>\n File "apps/frappe/frappe/model/db_query.py", line 203, in execute\n result = self.build_and_run()\n self = <frappe.model.db_query.DatabaseQuery object at 0x7f8b5f3abf90>\n fields = [‘transaction_date as _unit’, ‘SUM(net_amount)’, 'COUNT()']\n filters = [[‘Sales Order Item’, ‘item_group’, ‘=’, ‘Altar’, False], [‘Sales Order Item’, ‘docstatus’, ‘<’, 2, False], [‘Sales Order Item’, ‘transaction_date’, ‘>=’, ‘2022-01-01’, False], [‘Sales Order Item’, ‘transaction_date’, ‘<=’, datetime.datetime(2024, 1, 31, 0, 0), False]]\n or_filters = None\n docstatus = None\n group_by = ‘_unit’\n order_by = ‘_unit asc’\n limit_start = False\n limit_page_length = None\n as_list = True\n with_childnames = False\n debug = False\n ignore_permissions = False\n user = None\n with_comment_count = False\n join = ‘left join’\n distinct = False\n start = None\n page_length = None\n limit = None\n ignore_ifnull = False\n save_user_settings = False\n save_user_settings_fields = False\n update = None\n add_total_row = None\n user_settings = None\n reference_doctype = None\n run = True\n strict = True\n pluck = None\n ignore_ddl = False\n parent_doctype = None\n File "apps/frappe/frappe/model/db_query.py", line 243, in build_and_run\n return frappe.db.sql(\n self = <frappe.model.db_query.DatabaseQuery object at 0x7f8b5f3abf90>\n args = {‘tables’: ‘
tabSales Order Item’, ‘conditions’: "wheretabSales Order Item.item_group= ‘Altar’ and ifnull(tabSales Order Item.docstatus, 0) < 2.0 and ifnull(tabSales Order Item.transaction_date, ‘0001-01-01’) >= ‘2022-01-01’ and ifnull(tabSales Order Item.transaction_date, ‘0001-01-01’) <= ‘2024-01-31’", ‘fields’: 'NULL, NULL, COUNT()‘, ‘order_by’: ’ order by _unit asc’, ‘group_by’: ’ group by _unit’, ‘limit’: ‘’}\n query = "select NULL, NULL, COUNT()\n\t\t\tfromtabSales Order Item\n\t\t\twheretabSales Order Item.item_group= ‘Altar’ and ifnull(tabSales Order Item.docstatus, 0) < 2.0 and ifnull(tabSales Order Item.transaction_date, ‘0001-01-01’) >= ‘2022-01-01’ and ifnull(tabSales Order Item.transaction_date, ‘0001-01-01’) <= ‘2024-01-31’\n\t\t\t group by _unit\n\t\t\t order by _unit asc\n\t\t\t"\n File "apps/frappe/frappe/database/database.py", line 217, in sql\n self._cursor.execute(query, values)\n self = <frappe.database.mariadb.database.MariaDBDatabase object at 0x7f8b5f3afb90>\n query = "select NULL, NULL, COUNT()\n\t\t\tfromtabSales Order Item\n\t\t\twheretabSales Order Item.item_group= ‘Altar’ and coalesce(tabSales Order Item.docstatus, 0) < 2.0 and coalesce(tabSales Order Item.transaction_date, ‘0001-01-01’) >= ‘2022-01-01’ and coalesce(tabSales Order Item.transaction_date, ‘0001-01-01’) <= ‘2024-01-31’\n\t\t\t group by _unit\n\t\t\t order by _unit asc / FRAPPE_TRACE_ID: cc9682fd-e5bc-4a92-94d0-3722b3393509 /"\n values = None\n as_dict = False\n as_list = 0\n debug = False\n ignore_ddl = False\n auto_commit = 0\n update = None\n explain = False\n run = True\n pluck = False\n trace_id = ‘cc9682fd-e5bc-4a92-94d0-3722b3393509’\n File "env/lib/python3.11/site-packages/pymysql/cursors.py", line 153, in execute\n result = self._query(query)\n self = <pymysql.cursors.Cursor object at 0x7f8b5e1b6890>\n query = "select NULL, NULL, COUNT()\n\t\t\tfromtabSales Order Item\n\t\t\twheretabSales Order Item.item_group= ‘Altar’ and coalesce(tabSales Order Item.docstatus, 0) < 2.0 and coalesce(tabSales Order Item.transaction_date, ‘0001-01-01’) >= ‘2022-01-01’ and coalesce(tabSales Order Item.transaction_date, ‘0001-01-01’) <= ‘2024-01-31’\n\t\t\t group by _unit\n\t\t\t order by _unit asc / FRAPPE_TRACE_ID: cc9682fd-e5bc-4a92-94d0-3722b3393509 /"\n args = None\n File "env/lib/python3.11/site-packages/pymysql/cursors.py", line 322, in _query\n conn.query(q)\n self = <pymysql.cursors.Cursor object at 0x7f8b5e1b6890>\n q = "select NULL, NULL, COUNT()\n\t\t\tfromtabSales Order Item\n\t\t\twheretabSales Order Item.item_group= ‘Altar’ and coalesce(tabSales Order Item.docstatus, 0) < 2.0 and coalesce(tabSales Order Item.transaction_date, ‘0001-01-01’) >= ‘2022-01-01’ and coalesce(tabSales Order Item.transaction_date, ‘0001-01-01’) <= ‘2024-01-31’\n\t\t\t group by _unit\n\t\t\t order by _unit asc / FRAPPE_TRACE_ID: cc9682fd-e5bc-4a92-94d0-3722b3393509 /"\n conn = <pymysql.connections.Connection object at 0x7f8b5e1b62d0>\n File "env/lib/python3.11/site-packages/pymysql/connections.py", line 558, in query\n self._affected_rows = self._read_query_result(unbuffered=unbuffered)\n self = <pymysql.connections.Connection object at 0x7f8b5e1b62d0>\n sql = b"select NULL, NULL, COUNT()\n\t\t\tfromtabSales Order Item\n\t\t\twheretabSales Order Item.item_group= ‘Altar’ and coalesce(tabSales Order Item.docstatus, 0) < 2.0 and coalesce(tabSales Order Item.transaction_date, ‘0001-01-01’) >= ‘2022-01-01’ and coalesce(tabSales Order Item.transaction_date, ‘0001-01-01’) <= ‘2024-01-31’\n\t\t\t group by _unit\n\t\t\t order by _unit asc / FRAPPE_TRACE_ID: cc9682fd-e5bc-4a92-94d0-3722b3393509 */"\n unbuffered = False\n File "env/lib/python3.11/site-packages/pymysql/connections.py", line 822, in _read_query_result\n result.read()\n self = <pymysql.connections.Connection object at 0x7f8b5e1b62d0>\n unbuffered = False\n result = <pymysql.connections.MySQLResult object at 0x7f8b5fd9e4d0>\n File "env/lib/python3.11/site-packages/pymysql/connections.py", line 1200, in read\n first_packet = self.connection._read_packet()\n self = <pymysql.connections.MySQLResult object at 0x7f8b5fd9e4d0>\n File "env/lib/python3.11/site-packages/pymysql/connections.py", line 772, in _read_packet\n packet.raise_for_error()\n self = <pymysql.connections.Connection object at 0x7f8b5e1b62d0>\n packet_type = <class ‘pymysql.protocol.MysqlPacket’>\n buff = bytearray(b"\xff\x1e\x04#42S22Unknown column \‘_unit\’ in \‘order clause\’")\n packet_header = b’1\x00\x00\x01’\n btrl = 49\n btrh = 0\n packet_number = 1\n bytes_to_read = 49\n recv_data = b"\xff\x1e\x04#42S22Unknown column ‘_unit’ in ‘order clause’"\n packet = <pymysql.protocol.MysqlPacket object at 0x7f8b5e20a350>\n File "env/lib/python3.11/site-packages/pymysql/protocol.py", line 221, in raise_for_error\n err.raise_mysql_exception(self._data)\n self = <pymysql.protocol.MysqlPacket object at 0x7f8b5e20a350>\n errno = 1054\n File "env/lib/python3.11/site-packages/pymysql/err.py", line 143, in raise_mysql_exception\n raise errorclass(errno, errval)\n data = b"\xff\x1e\x04#42S22Unknown column ‘_unit’ in ‘order clause’"\n errno = 1054\n errval = "Unknown column ‘_unit’ in ‘order clause’"\n errorclass = <class ‘pymysql.err.OperationalError’>\npymysql.err.OperationalError: (1054, "Unknown column ‘_unit’ in ‘order clause’")”,“trace_id”:“cc9682fd-e5bc-4a92-94d0-3722b3393509”,“doctype”:“Error Log”,“__last_sync_on”:“2023-12-28T11:38:46.632Z”}
Please help. Thanks.