如何在Django中无需新建表扩展模型，实现两模型数据联合查询？

Absolutely, you don't need to create a new model or database table to achieve this! The OneToOneField approach you tried generates extra tables and requires joins, but we can directly fetch and combine data from your existing models using their shared ticker + date unique constraint. Here are two clean approaches that keep your original database structure intact:

Approach 1: Bulk-Friendly Query with Subqueries (Best for Lists/Batches) #

This method uses Django's Subquery and OuterRef to pull fields from NELiReport directly into your SSALiReport queryset, avoiding a separate table entirely. It’s efficient for bulk data retrieval.

Step 1: Your Existing Model Structure (Unchanged) #

First, confirm your models stay exactly as they are:

from django.db import models

class SSALiReport(models.Model):
    ticker = models.CharField(max_length=20)
    date = models.DateField()
    sentiment = models.FloatField()
    # Add any other existing fields here

    class Meta:
        unique_together = ('ticker', 'date')

class NELiReport(models.Model):
    ticker = models.CharField(max_length=20)
    date = models.DateField()
    prediction = models.FloatField()
    # Add any other existing fields here

    class Meta:
        unique_together = ('ticker', 'date')

Step 2: Build a Utility Function or Custom Manager #

Create a function (or add a custom manager method) to return the combined data:

from django.db.models import OuterRef, Subquery

def get_nn_feed(ticker=None, date=None):
    # Subquery to fetch prediction from NELiReport matching ticker+date
    nel_prediction_subquery = NELiReport.objects.filter(
        ticker=OuterRef('ticker'),
        date=OuterRef('date')
    ).values('prediction')[:1]

    # Annotate SSALiReport queryset with the prediction field
    combined_queryset = SSALiReport.objects.annotate(
        prediction=Subquery(nel_prediction_subquery)
    )

    # Apply optional filters if provided
    if ticker:
        combined_queryset = combined_queryset.filter(ticker=ticker)
    if date:
        combined_queryset = combined_queryset.filter(date=date)

    return combined_queryset

Equivalent SQL Query #

This Django code translates to a SQL query that uses a correlated subquery (no extra tables):

SELECT 
    ssa.id, ssa.ticker, ssa.date, ssa.sentiment,
    (SELECT ne.prediction 
     FROM nelireport ne 
     WHERE ne.ticker = ssa.ticker AND ne.date = ssa.date 
     LIMIT 1) AS prediction
FROM ssalireport ssa
-- Optional WHERE clause if ticker/date filters are applied
WHERE ssa.ticker = 'AAPL' AND ssa.date = '2024-05-20';

How to Use It #

# Get combined data for a specific ticker and date
combined_data = get_nn_feed(ticker='AAPL', date='2024-05-20').first()
print(combined_data.sentiment)  # From SSALiReport
print(combined_data.prediction) # From NELiReport

# Get all combined data for a ticker
all_aapl_data = get_nn_feed(ticker='AAPL')
for item in all_aapl_data:
    print(f"{item.date}: Sentiment={item.sentiment}, Prediction={item.prediction}")

Approach 2: Proxy Model with Property (Best for Single Objects) #

If you prefer a model-like interface for individual records, use a proxy model (which doesn’t create a new table) with a property to fetch the related NELiReport field.

Create the Proxy Model #

class NNFeed(SSALiReport):
    class Meta:
        proxy = True  # Tells Django this is a proxy, no new table

    @property
    def prediction(self):
        # Fetch the matching NELiReport record
        try:
            return NELiReport.objects.get(ticker=self.ticker, date=self.date).prediction
        except NELiReport.DoesNotExist:
            return None  # Or return a default value like 0.0

How to Use It #

# Get a single combined record
nn_feed_item = NNFeed.objects.get(ticker='MSFT', date='2024-05-20')
print(nn_feed_item.sentiment)   # From SSALiReport
print(nn_feed_item.prediction)  # From NELiReport

⚠️ Note: This approach can cause N+1 query issues if you use it on a queryset of multiple objects (each record triggers a separate query for NELiReport). For bulk operations, stick with Approach 1.

Key Takeaways #

• Both methods keep your original SSALiReport and NELiReport database structures completely unchanged.
• No new tables or rows are created—we’re just combining existing data at query time.
• Choose Approach 1 for bulk data retrieval, Approach 2 for single-record access with a model-like interface.

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