Frappe.db.sql() can only do SELECT but not update! What now?

ERPNext Customize ERPNext
1

How do you UPDATE the db in a server script when several JOIN statements are necessary?

2

In a server script, you can use JOIN statements in an UPDATE query to update records based on conditions in multiple tables. However, the syntax can vary depending on the SQL dialect youā€™re using. Hereā€™s a general example:

UPDATE table1 
SET table1.target_column=table2.source_column 
FROM table1 
JOIN table2 
ON table1.join_column=table2.join_column 
WHERE condition;

In your Python code, you can use this SQL query with frappe.db.sql:

def update_function():
    sql_query = """
    UPDATE table1 
    SET table1.target_column=table2.source_column 
    FROM table1 
    JOIN table2 
    ON table1.join_column=table2.join_column 
    WHERE condition
    """
    frappe.db.sql(sql_query)

Please replace table1, table2, target_column, source_column, join_column, and condition with your actual table names, column names, and condition.

1 Like
3

nope

image
image792Ɨ188 14.8 KB

4

Please Make It Custom App, And Then Install it on your site.

5

Thatā€™s a very fair point. So far I got how server scripts work. How do I make the code in a custom app fire when an invoice is created from a subscription?

6

Hi @kombi:

Due security reasons, only SELECT or EXPLAIN (read) statements are allowed from server scripts. safe_exec avoid any other raw SQL query, even using query builder, except if query is built from whitelisted methods (insert, set_value, bulk_insert, bulk_update ā€¦).

Hope this helps.

2 Likes
7

for starters, canā€™t find ā€œon saveā€ in hooks listā€¦ write_file maybe?

https://frappeframework.com/docs/user/en/python-api/hooks#list-of-available-hooks

8

Hi @kombi:

Maybe some logic changes to invoicing would be doable with server script.

Anyway, if finally choose custom app approach, you will need to override/extend the doctype class controller ā€¦ write_file hook refers to files uploaded to site

Check this:
https://frappeframework.com/docs/user/en/python-api/hooks#override-doctype-class

This video could be helpful too ā€¦

Hope this helps.

1 Like
9

There are different kinds of hooks: general app hooks (the ones you found) and controller hooks. Youā€™ll need a combination of both.

10

I will ā€˜ormā€™ myself through this one, wonā€™t look pretty but will work.

I shall look at hooks and Karani next though, thanks very much m24som, avc and Raffael!

1 Like
11

so hereā€™s what I did:

I know thereā€™s a typo in the name but Iā€™m too lazy to correct thatā€¦