Is there any way to use sql queries with permissions?

Frappe Framework
,
1

I have got a complex query that is not quite efficient if I use get a list so I’ve used SQL,
is there any way to use permission within SQL?

Case:
I want the top 10 vendors by purchase value (grand_total) of purchase orders for the last three months.
But data should be based on the logged-in user for example if the currently logged-in user is assigned to a specific region then top vendors should be based on that region, region is just one condition I’ve multiple conditions written using permission query.

If I use get_list:
In the case of using the get_list function, I’ve first to get all vendors then using a loop I’ve to get the last 3 months’ purchase order values then I’ve to club them after that I’ve to sort value and return the top 10

But using SQL it is pretty easy and straight-forward

2

You can try the frappe query builder and see if it respects the user permissions. I, however, doubt.

2 Likes
3

First Check Permission of User and then use and if/else statement to Use SQL, In this way you can use both SQL and Check Permissions simultaneously in Python File.

4

I also thought that query builder would respect permissions but unfortunately it doesn’t

5

Anyone who has access to the server or server-side code execution has full access to the database. There’s no simple way around that.

This seems like a great use case for a custom API method (either server script or a whitelisted method in an app). There, you can enforce any restrictions you want, including complex user details.

6

I think AI can handle SQL to QB conversion.

Other option might be creating a loop in the final result list. If user has read permission on that record, you are good to go. You can utilize the has_perm("read") method for that.

PS: Can you share the query? DM if possible.

7

Yes, you can enforce permissions within SQL by joining your query with a permissions table or filtering based on the logged-in user’s assigned region. Assuming you have a users table that stores region assignments, you can modify your SQL query like this:

"
SELECT v.vendor_id, v.vendor_name, SUM(po.grand_total) AS total_value
FROM purchase_orders po
JOIN vendors v ON po.vendor_id = v.vendor_id
JOIN users u ON u.user_id = CURRENT_USER_ID – Replace with the actual user ID retrieval method
WHERE po.order_date >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
AND po.region_id = u.region_id – Ensure region-based filtering
GROUP BY v.vendor_id, v.vendor_name
ORDER BY total_value DESC
LIMIT 10;
"
Regards : https://multilingua.ae/