Frappe.get_all() with join to child table

Frappe Framework Customization
, ,
1

Hello

I have a “Parcel” Doctype
Wich have a “Parcel Content” Doctype as a child table

If i do frappe.get_doc(‘Parcel’, ‘NAME’)
I get the object with the child table

But i need to print out all the content of multiple “Parcels”
For the moment i have find three ways:

  1. For each Object, make a query to get “content”:

frappe.get_doc(‘Parcel’, ‘NAME’)

but i need to query this inside a loop, i can do some like 50 queries at a row.

  1. Query the related content using the parent name:

frappe.get_all(‘Parcel Content’, fields=[‘unit_price’, ‘description’, ‘qty’], filters=[[‘parent’, ‘in’, parcel_names]])

This works quite well, because i have the data

But what about this

  1. Advanced Join:
    Advanced Database queries in Frappe

frappe.get_all(‘Parcel’, fields=“name, carrier, tabParcel Content.unit_price”, filters=[[‘name’, ‘in’, parcel_names]])

So i’m able to filter all Objects i Need and join the table to fetch the extra details

My question here:
Is there a best practice or a built-in frappe way to do it?

PS: I’m using this query on a jinja template in a custom print format to render all extra details

2

as you said, the issue with get_all is that is doesn’t return all possible value.
In Jinja you can do something like this:

{% set doc = frappe.get_doc('Parcel', 'NAME') }
{% for details in doc.parcel_content %}
    //do something
{% endfor %}

You can also filter the doc.parcel_content before looping on it.
Hope it helps :slight_smile:

3

Adding another option(The most viable)

In v13 frappe team has added Frappe Query Builder
Docs here:
https://frappeframework.com/docs/v13/user/en/api/query-builder

https://pypika.readthedocs.io/en/latest/2_tutorial.html#selecting-data

To get multiple Docs with their Child Docs we need to execute a SQL or alternative using frappe Query Builder

parcel = frappe.qb.DocType('Parcel')
parcel_content = frappe.qb.DocType('Parcel Content')

query = frappe.qb
    .select(parcel.name, parcel_content.description, parcel_content.qty, parcel_content.amount)
    .from_(parcel_content)
    .join(parcel).on(parcel.name == parcel_content.parent)
    .where(parcel.name.isin(['ids']))
1 Like