Sql query to display sales order of customers

i want to create sales order when i submit my doctype(order book). inorder to do that i’m using my child doctype(order book line). for each customer i want only one sales order, to do that i have to write the sql query. Help me out with this.
i have written the code for the creation of sales order , its just the query part which i have to write inorder to choose the customer and create only one sales order for each.

anyone there with a solution ???

You don’t have to write query for it. You can get (order book line) data in self.your_child_fieldname.
Just check what name you have given to that child table field.

You can have multiple customers in a child table and per customer one sales order.
For that try:

def on_submit(self):
for row in self.your_child_fieldname:
get_sales_order(row.item_code)

def get_sales_order(self,item_code):
your code

not working

How many sales order got created ? You have debugged your code what have you found?

error is coming while i submit the order book.
i’m attaching the error screenshot and fort he code as well.

You are using src.customer, customer field is not present in order book line.

customer field is there in the order book book line. as u can see in the previous reply"s 1st screenshot.

hello there is no customer on the screenshot just the user, date, shift, delivery warehouse and company

customer will come in place of child_fieldname ?
if customer will come then obviously it will give an error as customer field is not there in the doctype…it is inside child table.

do not use the customer in the loop instead use the table if you want to loop inside the table

it can be done this way : -

def get_sales_order(self):
customers = frappe.db.sql(""" select distinct customer from tabOrder Book Line where parent=’{0}’ “”".format(self.name))

	for cust in customers:
		order_lines = frappe.db.get_all("Order Book Line",{"parent": self.name,"customer":cust[0]})
		
		doc = frappe.new_doc('Sales Order')
		doc.customer = cust[0]
		doc.order_date = self.date
		# doc.delivery_date = src.delivery_date
		doc.company = self.company
		doc.order_book = self.name
		for ord in order_lines:
			src = frappe.get_doc('Order Book Line',ord['name'])
			
			item = frappe.get_doc("Item",src.product)
			doc.append('items', {
				'item_code':src.product,
				'item_name':item.item_name,
				'description':item.description,
				'delivery_date':datetime.now().date(),
				'qty':src.qty,
				'uom':src.uom,
				# 'stock_uom': item_code.stock_uom,
				'rate':src.rate,
				'warehouse':self.delivery_warehouse
			})
		doc.save()
		doc.submit()