Showing title in link fields of a virtual doctype produces a query error.

arsanysamuel commented 6 months ago

Description of the issue

Showing title in link fields for virtual doctypes produces a SQL query error:

Error in query:
('SELECT `email` FROM `tabVirtual Doctype` WHERE `name`=%(param1)s LIMIT 1', {'param1': 'doc_name'})

This error is produced since there's no table for the virtual doctype in the database.

Context information (for bug reports)

Output of bench version

erpnext 14.x.x-develop
frappe 15.x.x-develop
custom_app 0.0.1

Steps to reproduce the issue

I've created a virtual doctype
Assigned a field as a "Title Field"
Checked "Show Title in Link Fields"
Created a link field for this virtual doctype in another doctype
Try to open a document from the other doctype

Observed result

Query error as shown below

Expected result

Linked fields for virtual doctypes showing title using the virtual doctype implementation instead of SQL query

Stacktrace / full error message

  File "/usr/lib/python3.10/threading.py", line 973, in _bootstrap
    self._bootstrap_inner()
  File "/usr/lib/python3.10/threading.py", line 1016, in _bootstrap_inner
    self.run()
  File "/usr/lib/python3.10/threading.py", line 953, in run
    self._target(*self._args, **self._kwargs)
  File "/usr/lib/python3.10/socketserver.py", line 683, in process_request_thread
    self.finish_request(request, client_address)
  File "/usr/lib/python3.10/socketserver.py", line 360, in finish_request
    self.RequestHandlerClass(request, client_address, self)
  File "/usr/lib/python3.10/socketserver.py", line 747, in __init__
    self.handle()
  File "/home/user/frappe-bench/env/lib/python3.10/site-packages/werkzeug/serving.py", line 390, in handle
    super().handle()
  File "/usr/lib/python3.10/http/server.py", line 433, in handle
    self.handle_one_request()
  File "/usr/lib/python3.10/http/server.py", line 421, in handle_one_request
    method()
  File "/home/user/frappe-bench/env/lib/python3.10/site-packages/werkzeug/serving.py", line 362, in run_wsgi
    execute(self.server.app)
  File "/home/user/frappe-bench/env/lib/python3.10/site-packages/werkzeug/serving.py", line 325, in execute
    for data in application_iter:
  File "/home/user/frappe-bench/env/lib/python3.10/site-packages/werkzeug/debug/__init__.py", line 330, in debug_application
    app_iter = self.app(environ, start_response)
  File "/home/user/frappe-bench/apps/frappe/frappe/middlewares.py", line 16, in __call__
    return super().__call__(environ, start_response)
  File "/home/user/frappe-bench/env/lib/python3.10/site-packages/werkzeug/middleware/shared_data.py", line 249, in __call__
    return self.app(environ, start_response)
  File "/home/user/frappe-bench/env/lib/python3.10/site-packages/werkzeug/middleware/shared_data.py", line 249, in __call__
    return self.app(environ, start_response)
  File "/home/user/frappe-bench/apps/frappe/frappe/app.py", line 74, in application
    app(environ, start_response),
  File "/home/user/frappe-bench/env/lib/python3.10/site-packages/werkzeug/wrappers/request.py", line 189, in application
    resp = f(*args[:-2] + (request,))
  File "/home/user/frappe-bench/apps/frappe/frappe/app.py", line 110, in application
    response = frappe.api.handle(request)
  File "/home/user/frappe-bench/apps/frappe/frappe/api/__init__.py", line 49, in handle
    data = endpoint(**arguments)
  File "/home/user/frappe-bench/apps/frappe/frappe/api/v1.py", line 36, in handle_rpc_call
    return frappe.handler.handle()
  File "/home/user/frappe-bench/apps/frappe/frappe/handler.py", line 49, in handle
    data = execute_cmd(cmd)
  File "/home/user/frappe-bench/apps/frappe/frappe/handler.py", line 85, in execute_cmd
    return frappe.call(method, **frappe.form_dict)
  File "/home/user/frappe-bench/apps/frappe/frappe/__init__.py", line 1754, in call
    return fn(*args, **newargs)
  File "/home/user/frappe-bench/apps/frappe/frappe/utils/typing_validations.py", line 31, in wrapper
    return func(*args, **kwargs)
  File "/home/user/frappe-bench/apps/frappe/frappe/desk/form/load.py", line 53, in getdoc
    set_link_titles(doc)
  File "/home/user/frappe-bench/apps/frappe/frappe/desk/form/load.py", line 420, in set_link_titles
    link_titles.update(get_title_values_for_link_and_dynamic_link_fields(doc))
  File "/home/user/frappe-bench/apps/frappe/frappe/desk/form/load.py", line 443, in get_title_values_for_link_and_dynamic_link_fields
    link_title = frappe.db.get_value(
  File "/home/user/frappe-bench/apps/frappe/frappe/database/database.py", line 506, in get_value
    result = self.get_values(
  File "/home/user/frappe-bench/apps/frappe/frappe/database/database.py", line 607, in get_values
    out = self._get_values_from_table(
  File "/home/user/frappe-bench/apps/frappe/frappe/database/database.py", line 874, in _get_values_from_table
    return query.run(as_dict=as_dict, debug=debug, update=update, run=run, pluck=pluck)
  File "/home/user/frappe-bench/apps/frappe/frappe/query_builder/utils.py", line 84, in execute_query
    result = frappe.db.sql(query, params, *args, **kwargs)  # nosemgrep
  File "/home/user/frappe-bench/apps/frappe/frappe/database/database.py", line 253, in sql
    traceback.print_stack()
Error in query:
('SELECT `email` FROM `tabVirtual Doctype` WHERE `name`=%(param1)s LIMIT 1', {'param1': 'doc_name'})

Additional information

Ubuntu 22.04.

ankush commented 6 months ago

This feature can't work with virtual doctypes. It would essentially require fetching entire doc just to fetch title.

arsanysamuel commented 6 months ago

I understand that it has to fetch the whole document, is there any method or a workaround even if it requires fetching the whole document?

ankush commented 6 months ago

You can write formatters and format them on the fly.

https://github.com/frappe/erpnext/blob/f8a1a7f51543c4782fc5a8faa3e74ef1c8606cbd/erpnext/public/js/utils.js#L983

frappe / frappe