基于Django 2.0编写复杂查询的技术咨询（附Recommender模型）

Hey there! Let's dive into some practical complex query implementations for your Recommender model using Django's ORM. I'll cover common real-world scenarios you might need, with clear, reusable code snippets:

1. Fetch Approved Recommendations for a Brand (With Related Data) #

If you need to pull all approved recommendations for a specific brand, and want to avoid performance issues from repeated database hits (N+1 problem), use select_related to prefetch related user and state data:

from django.contrib.auth import get_user_model
from your_app.models import Recommender

# Get the target brand user (replace with your lookup logic)
BrandUser = get_user_model()
target_brand = BrandUser.objects.get(username="acme_brand")

# Query with prefetching related objects
approved_recommendations = Recommender.objects.filter(
    brand=target_brand,
    authorized__id=6  # Match your "approved" State ID
).select_related("customer", "recommender", "authorized")

# Iterate through results without extra DB calls
for reco in approved_recommendations:
    print(f"Customer: {reco.customer.email}, Recommender: {reco.recommender.username}, Status: {reco.authorized.name}")

2. Recent Pending Recommendations (Sorted by Date) #

To get all pending recommendations created in the last 7 days, sorted from newest to oldest:

from datetime import datetime, timedelta

# Calculate the cutoff date
seven_days_ago = datetime.now() - timedelta(days=7)

recent_pending_reco = Recommender.objects.filter(
    authorized__id=5,  # Match your "pending" State ID
    dateTime__gte=seven_days_ago
).order_by("-dateTime")  # Descending order (newest first)

3. Aggregate Recommendation Counts by Status & Recommender #

If you need to generate stats—like how many recommendations each user has made, broken down by status—use annotate and values for aggregation:

from django.db.models import Count

recommender_status_stats = Recommender.objects.values(
    "recommender__username", "authorized__name"
).annotate(
    total_recommendations=Count("id")
).order_by("recommender__username", "authorized__name")

# Print aggregated results
for stat in recommender_status_stats:
    print(f"Recommender: {stat['recommender__username']} | Status: {stat['authorized__name']} | Count: {stat['total_recommendations']}")

4. Find Customers Recommended Multiple Times by the Same User #

To identify customers who've been recommended more than once by the same recommender:

repeat_recommendations = Recommender.objects.values(
    "customer__username", "recommender__username"
).annotate(
    recommendation_count=Count("id")
).filter(recommendation_count__gt=1)

for entry in repeat_recommendations:
    print(f"Customer {entry['customer__username']} was recommended {entry['recommendation_count']} times by {entry['recommender__username']}")

If you have a specific complex query in mind—like cross-model filtering, subqueries, or advanced aggregations—just share the exact requirement and I can refine the solution further!

内容的提问来源于stack exchange，提问作者Carmoreno