How are db-tables linked SQLwise?

ERPNext User Forum
1

I am trying to cast a pricing rule for an item group into permanent prices like so:

UPDATE `tabItem Price` SET price_list_rate = price_list_rate * 1.1 WHERE...

And here’s my question: What’s after the “WHERE”?

How is "tabItem Price" linked to "tabItem Group" ?

In my limited knowledge I would guess there to be some n:1 or n:n relation between the tables via some sort of id. Only I can’t seem to find such an identifier…

Please enlighten,
cheers,
Kombi

2

First, take a look at the Pricing Rule docs to see if that would satisfy, but it sounds really close to what you’re trying to achieve.

To answer your specific question: please don’t take this approach. If you are going to make programmatic changes to data in ERPNext you should always use the ORM and obey the document model workflows. If you don’t it can lead to (very) broken data:

#bash
clear && bench console

[1]: items = frappe.get_all('Item', {'item_group': ['in', ['My First Item Group', 'My Second Item Group']]}, 'name', pluck="name')

[2]: for item in frappe.get_all('Item Price', {'item_code': ['in', items]}):
           item_price = frappe.get_doc('Item Price', item)
           item_price.price_list_rate = item_price.price_list_rate * 1.1
           item_price.save()

[3]: frappe.db.commit() # if you don't do this, no data is written. If you make a mistake and not doing it allows you to exit without making changes
1 Like
3

Hi tmatteson,
fair point + wicked! (works like a charm)

Where might I find some documentation concerning the ORM and possible commands in bench console?

1 Like
4

Hi there,

The console is just a python shell, so any python expression is a valid command.

Frappe’s ORM is all documented under their database API:
https://frappeframework.com/docs/v14/user/en/api/database

As far as general relationality, every document has a name field that acts as a unique identifier. Child documents (n:1, defined in the doctype as part of a child table), always refer back to this name in a parent field. For 1:1 links, typically you’ll just use a link field, and then the value of that link field will again be the name of the document being linked.

5

Thanks very much Peter!

Clearly, I must do more python in my life…:slight_smile:

cheers,
kombi

1 Like