I want to get data from multiple interrelated DocTypes. These DocTypes are Work Order
and Work Order Items
Can I get the data using frappe.db.get_list
?
What I want to do is get following fields:
Work Order → Status
Work Order → Item to Manufacture
Work Order → Qty to Manufacture
Work Order → Manufactured Qty
Work Order → Work-in-Progress Warehouse
Work Order → Target Warehouse
Work Order → Items (All the Required Items)
Work Order → Items → Item Code
Work Order → Items → Required Qty
Work Order → Items → Transferred Qty
Work Order → Items → Consumed Qty
Work Order → Items → Available Qty at Source Warehouse
How to get this data without using frappe.db.sql
because using frappe.db.sql
will bypass validations and integrity checks as per my understanding?
Regards,
Is there some way to get data from multiple tables or we have to use frappe.db.sql
?
Currently I have started using frappe.db.sql
but it would be great if we can use frappe.db.get_list
or something similar to it.
Regards,
gsarunk
September 23, 2022, 2:12pm
#3
As far as i know frappe.db.sql is the way to join Doctypes.
All other fappe api framework require Doctype as a key.
Also frappe.db.sql is not the recommended way.
Alternatively You can iterate over the parent table and retrieve the other related entities.
FHenry
September 23, 2022, 5:24pm
#4
Hi,
There is various way to do it.
the new db style (sample from production_plan.py)
bom = frappe.qb.DocType("BOM")
mr = frappe.qb.DocType("Material Request")
mr_item = frappe.qb.DocType("Material Request Item")
pending_mr_query = (
frappe.qb.from_(mr)
.from_(mr_item)
.select(mr.name, mr.transaction_date)
.distinct()
.where(
(mr_item.parent == mr.name)
& (mr.material_request_type == "Manufacture")
& (mr.docstatus == 1)
& (mr.status != "Stopped")
& (mr.company == self.company)
& (mr_item.qty > IfNull(mr_item.ordered_qty, 0))
& (
ExistsCriterion(
frappe.qb.from_(bom)
.select(bom.name)
.where((bom.item == mr_item.item_code) & (bom.is_active == 1))
)
)
)
)
pending_mr = pending_mr_query.run(as_dict=True)
After that If you need to be sure records is ok for current user : get_doc on name
Another sample
# Find all material request lines that same price list product with a default supplier
# that match price list buying supplier
MaterialRequest = frappe.qb.DocType("Material Request")
MaterialRequestItem = frappe.qb.DocType("Material Request Item")
ItemPrice = frappe.qb.DocType("Item Price")
ItemDefault = frappe.qb.DocType("Item Default")
material_price_query_base = (
frappe.qb.from_(ItemPrice)
.inner_join(MaterialRequestItem)
.on((ItemPrice.item_code == MaterialRequestItem.item_code)
& (MaterialRequestItem.parentfield == 'items')
& (MaterialRequestItem.parenttype == 'Material Request'))
.inner_join(MaterialRequest)
.on(MaterialRequest.name == MaterialRequestItem.parent)
.inner_join(ItemDefault)
.on((ItemDefault.parenttype == 'Item')
& (ItemDefault.parentfield == 'item_defaults')
& (ItemDefault.parent == ItemPrice.item_code))
.where((ItemPrice.buying == 1)
& (MaterialRequest.material_request_type == 'Purchase')
& (ItemPrice.price_list_rate != MaterialRequestItem.rate))
.orderby(MaterialRequest.name, order=frappe.qb.desc)
.select(
MaterialRequest.name.as_('ma_name'),
MaterialRequestItem.name.as_('ma_item_name'),
MaterialRequestItem.rate,
ItemPrice.supplier,
ItemDefault.default_supplier,
ItemPrice.price_list_rate,
MaterialRequestItem.item_code,
)
)
2 Likes
I see its a child table that you want to join.
Frappe API handles this out of the Box.
You may like a cleaner implementation like one here
2 Likes
@Smit_Vora ,
Thanks you for the link but I am not able to understand as to what is happening in the code.
I checked the documentation but there is no mention that one can get all the child table records also.
Can you please explain in greater details.
Regards,