request new function to get the childtable

[guinanlin]

For example, Delivery Note and Delivery Note Item have a parent-child relationship.

I expect a function that can query the fields of the parent table Delivery Note and the child table Delivery Note Item at once.

I tried reading using something like the following, but I couldn't read the ChildDocType.

After carefully studying Frappe's logic for reading child table data, it should be similar to the following reading logic, note the parameter parent=Delivery%20Note

http://192.168.32.2:8001/api/resource/Delivery%20Item?&parent=Delivery%20Note&fields=[%20"name",%20"item_code",%20"qty"%20]&filters=[[%20"parent",%20"=",%20"MAT-DN-2024-00001%20"]]

*/
export const useFrappeCascadeGetDocList = <T=any, K=FrappeDoc<T>>(MainDocType: string, ChildDocType: string, mainArgs?: GetDocListArgs<K>, childArgs?: GetDocListArgs<K>, swrKey?: Key, options?: SWRConfiguration) => {

    const { url, db } = useContext(FrappeContext) as FrappeConfig;

   
    const swrResult = useSWR<T[], Error>(
        swrKey === undefined ? `${getRequestURL(MainDocType, url)}?${getDocListQueryString(mainArgs)}` : swrKey,
        () => db.getDocList<T, K>(MainDocType, mainArgs),
        options
    );

  
    const childSWRKey = swrResult.data ? `${getRequestURL(ChildDocType, url)}?${getDocListQueryString(childArgs)}` : null;

    
    const childSWRResult = useSWR(childSWRKey, () => db.getDocList(ChildDocType, childArgs), {
        ...options,
        revalidateOnFocus: false,
        shouldRetryOnError: false, 
    });

    return {
        mainDocs: swrResult,
        childDocs: childSWRResult,
    };
};

[nikkothari22]

Hey @guinanlin

You can use useFrappeGetDocList to filter on child tables. The array will have 4 elements instead of the usual 3.

I think you can also fetch child table fields by passing in the child table name and the field name in the fields array (separated by a . And the child table name would be enclosed in backticks).

The hooks use the standard Frappe REST APIs so they should work as usual.

[guinanlin]

Hey @guinanlin

You can use useFrappeGetDocList to filter on child tables. The array will have 4 elements instead of the usual 3.

I think you can also fetch child table fields by passing in the child table name and the field name in the fields array (separated by a . And the child table name would be enclosed in backticks).

The hooks use the standard Frappe REST APIs so they should work as usual.

use useFrappeGetDocList to filter on child tables just like as following:
http://192.168.32.2:8001/api/resource/Delivery%20Item?fields=[%20"name",%20"item_code",%20"qty"%20]&filters=[[%20"parent",%20"=",%20"MAT-DN-2024-00001%20"]]

it will happen to ‘resulted in a `403 FORBIDDEN’, you can refer to
https://discuss.frappe.io/t/rest-api-how-to-access-data-in-item-child-table/54291/3

or just paste following url on your browser
http://192.168.32.2:8001/api/resource/Delivery%20Item?fields=[%20"name",%20"item_code",%20"qty"%20]&filters=[[%20"parent",%20"=",%20"MAT-DN-2024-00001%20"]]

because request the child tables should add parent=Delivery%20Note

http://192.168.32.2:8001/api/resource/Delivery%20Item?&parent=Delivery%20Note&fields=[%20"name",%20"item_code",%20"qty"%20]&filters=[[%20"parent",%20"=",%20"MAT-DN-2024-00001%20"]]

[nikkothari22]

@guinanlin In your examples, I believe you are referring to "Delivery Note Item" instead of "Delivery Item"? Your requests have "Delivery Item" though.

In your particular case though, why not just use useFrappeGetDoc and get the whole document? You would get the child table with it.

[guinanlin]

@guinanlin In your examples, I believe you are referring to "Delivery Note Item" instead of "Delivery Item"? Your requests have "Delivery Item" though.

In your particular case though, why not just use useFrappeGetDoc and get the whole document? You would get the child table with it.

yeah, it's "Delivery Note Item"
http://192.168.32.2:8001/api/resource/Delivery%20Note%20Item?&parent=Delivery%20Note&fields=[%20%22name%22,%20%22item_code%22,%20%22qty%22%20]&filters=[[%20%22parent%22,%20%22=%22,%20%22MAT-DN-2024-00001%20%22]]

why not just use useFrappeGetDoc and get the whole document? You would get the child table with it.
because I want to the get the DocList but not Doc , example as following.
I want to retrieve data from the child table, but I also need to add some fields from the parent table.

Delivery Note

name	customer
A	google
B	facebook
C	github

Delivery Note Item

name	parent	parentfield	parenttype
A1	A	items	Delivery Note
A2	A	items	Delivery Note
B1	B	items	Delivery Note
C1	C	items	Delivery Note

what I want to get as following:

parent_name	customer	name	parent	parentfield	parenttype
A	google	A1	A	items	Delivery Note
A	google	A2	A	items	Delivery Note
B	facebook	B1	B	items	Delivery Note
C	github	C1	C	items	Delivery Note

[nikkothari22]

Oh okay. Got it. You can also do something like this using the useFrappeGetDocList hook:

const { data } = useFrappeGetDocList("Delivery Note", {
   fields: ["name", "`tabDelivery Note Item`.name", ...]
)

This will perform a join between the child table and the parent table - and it's inbuilt in Frappe.

We usually use this, or write a custom function and call that for our use cases.

[guinanlin]

Oh okay. Got it. You can also do something like this using the useFrappeGetDocList hook:

const { data } = useFrappeGetDocList("Delivery Note", {
   fields: ["name", "`tabDelivery Note Item`.name", ...]
)

This will perform a join between the child table and the parent table - and it's inbuilt in Frappe.

We usually use this, or write a custom function and call that for our use cases.

Thanks a lot for your answer. It really works! I have another question. Can I use fields from a child table as a condition for my query? I tried but got an error.

eg: 'tabDelivery Note Item.name': 'd4d5ddc880'

console.log('columnsFields', columnsFields)
    const updatedColumnsFields = [...columnsFields, '`tabDelivery Note Item`.name']; 
    const { data, mutate, error } = useFrappeGetDocList<PurchaseOrder>('Delivery Note', {
        fields: updatedColumnsFields, 
        filters: {
            docstatus: 1,
            'tabDelivery Note Item.name': 'd4d5ddc880',
        },
        limit: contextValue.Pagination.pageSize,
        limit_start: contextValue.Pagination.pageIndex * contextValue.Pagination.pageSize,
    });

[sumitjain236]

Child Table Support in Filters not in Fields.
filter will be:

filters:[["Delivery Note Item","name","=","d4d5ddc880"]]

[hffmnn]

Sorry for hijacking this issue, but I am trying to do exactly the same with the core user type:

const { data } = useFrappeGetDocList<User>(doctype, {
    fields: [
		"name",
		"`tabHas Role`.name",
		],
  });

because I want to show all the roles of a user on a list, but somehow this doesn't work: it returns something like this:

{
  "data":[{
    "email":"user@examplecom",
    "role":"Purchase User"
  },
  {
    "email":"example@example.com",
    "role":"Stock User"
  }...

one item per role of the same user.

Any ideas?

[nikkothari22]

@hffmnn Yes, because Frappe performs a join, and gets multiple rows for the child table. So it's just a SELECT query with a join internally in Frappe.

[nikkothari22]

You could build a parser (in a useMemo probably) that automatically dedupes the entries though.

[hffmnn]

Thanks a lot for the ideas!

So what would you do? Do everything client side with e.g. the parser or expose an api method?

[nikkothari22]

I usually create a custom API for scenarios like this.

[hffmnn]

Thanks a lot for your feedback!