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

ERPNext
, ,
#1

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,

#2

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,

#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.

#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
#5

Useful tip

#6

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
#7

@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,