#==================================== Double-underlined cross-table query ===============
# premises at this time related_name=booklist
Property Query:#Query the address of the publisher of the Linux book? #Linux_obj=models. Book.objects.filter (title= "Linux"). First () #print (linux_obj.publish.name) # ## Query the names and prices of all the books published by The Old Boys publishing house # #Pubobj=models. Publish.objects.filter (name= "Old boy Publishing House"). First () #print (PubObj.bookList.all (). VALUES ("title", "Price")) #此时 related_name=booklistDouble underline query: . Filter (filter condition). VALUES (the value to display)
Note: If you do not define related_name, the table name is used directly when you reverse query, do not need to add _set #Query the address of the publisher of the Linux book? --Forward Query #Ret=models. Book.objects.filter (title= "Linux"). VALUES ("Publish__addr") #print (ret) #or--Reverse query #Ret=models. Publish.objects.filter (booklist__title= "Linux"). VALUES ("addr") #print (ret) #Query the name and price of all books published by the Old Boys publishing house #Ret1=models. Publish.objects.filter (name= "Old boy Publishing House"). VALUES ("Booklist__title", "Booklist__price") #print (RET1) # #Ret2=models. Book.objects.filter (publish__name= "Old boy Publishing House"). VALUES ("title", "Price") #print (Ret2) #Query the names of all books that have been out of Egon (many-to-many)ret=models. Author.objects.filter (name="Egon"). VALUES ("Booklist__title") Print(ret) Ret2=models. Book.objects.filter (authorlist__name="Egon"). VALUES ("title") Print(Ret2)#the names of all books published by Egon and the names of the publishersret=models. Book.objects.filter (authorlist__name='Egon'). VALUES ('title','Publish__name') #The name of all books published by the author of the mobile phone number starting with 151 and the name of the publisher #Method 1: #Ad=models. AuthorDetail.objects.filter (tel__startswith= "151"). First () #print (Ad.author.bookList.all (). VALUES ("title", "Publish__name")) #Method 2Models. Book.objects.filter (authorlist__authordetail__tel__startswith="151"). VALUES ("title","Publish__name")
#================================================================ aggregation function fromDjango.db.modelsImportAvg,sum,count,max,min#aggregate function: aggregate
#Check the prices of all books andret=models. Book.objects.all (). Aggregate (Pricesum=sum (" Price")) Print(ret)#{' pricesum ': Decimal (' 166.00 ')}ret=models. Book.objects.all (). Aggregate (Priceavg=avg (" Price"), Pricesum=sum (" Price")) #grouping function annotate function #Query the number of authors for each bookbook_list=models. Book.objects.all (). Annotate (C=count ("Authorlist__name"))#at this time the book_list is a queryset forBook_objinchbook_list:Print(BOOK_OBJ.C)#默认分组成一组,adds a C field to the new table after grouping #find the cheapest books ever published by a publisherRet=models. Book.objects.all (). VALUES ('publish__id'). Annotate (Min (" Price"))#The fields within values are grouped fields Print(ret)
Python-day71--django Multi-table double underline query and group aggregation