I want to get data from multiple DocType using frappe.db.get_list

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?


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.


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.


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 = (
			.select(mr.name, mr.transaction_date)
				(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))
				& (
						.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 = (
        .on((ItemPrice.item_code == MaterialRequestItem.item_code)
            & (MaterialRequestItem.parentfield == 'items')
            & (MaterialRequestItem.parenttype == 'Material Request'))
        .on(MaterialRequest.name == MaterialRequestItem.parent)
        .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)

Useful tip

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



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.