I am currently using the following SQL query in my Frappe Insight dashboard to display data based on the logged-in user:
SELECT
ed.name AS enquiry_id,
ed.team_lead,
COUNT(DISTINCT tl.name) AS total_enquiry,
SUM(CASE WHEN DATE(ed.next_follow_up_date) = CURDATE() THEN 1 ELSE 0 END) AS today_followup,
SUM(CASE WHEN tl.status = 'Hot' THEN 1 ELSE 0 END) AS hot_leads,
SUM(CASE WHEN tl.status = 'Warm' THEN 1 ELSE 0 END) AS warm_leads,
SUM(CASE WHEN tl.status = 'Cold' THEN 1 ELSE 0 END) AS cold_leads,
tl.user_id AS team_lead_user,
tl.designation AS team_lead_designation,
ed.employee_name AS kec_name,
kec_emp.first_name AS kec_user,
kec_emp.designation AS kec_designation
FROM `tabEnquiry Detail` ed
JOIN `tabEmployee` tl ON tl.name = ed.team_lead
LEFT JOIN `tabEmployee` kec_emp ON kec_emp.name = ed.employee_name
WHERE tl.user_id = '{{ frappe.session.user }}'
ORDER BY ed.name;
However, this query is not fetching data for the logged-in user. The reason is that in Frappe Insights v3+, the variable {{ frappe.session.user }} is no longer supported in SQL queries. As a result, the filter condition does not correctly resolve to the current user, and the query returns no results.
how can i fix that ?