Combine 2 django models based on multiple columns without using select

I have 2 models where both of them contain 2 columns which can be treated as keys and a third column which is the value

The goal is to inner join both the models but somehow I'm having trouble doing that

I tried following this link but I don't think in my case I would want the columns to foreign key to the other so I can't use "select_related"

My Models are as follows:

class AssetReturnTs(models.Model):
    data_date = models.DateTimeField()
    asset = models.ForeignKey(Asset, on_delete=CASCADE)
    return = models.DecimalField(max_digits=19, decimal_places=10, null=True)  

    class Meta:
        db_table = "return"



class AssetWeightTs(models.Model):
    data_date = models.DateTimeField()
    asset = models.ForeignKey(Asset, on_delete=CASCADE)
    weight = models.DecimalField(max_digits=19, decimal_places=10, null=True)  

    class Meta:
        db_table = "weight"

I want to do a query such that I join the AssetReturn and AssetWeight on data_date and asset_id The end goal is to then do a weighted sum of the return.

Query should look like this:

data_date | asset_id | weight | return
---=----- | -------- | ------ | -------

Currently I'm querying both separately and converting them to pandas and merging them. It looks like this:

asset_return_ts = AssetReturnTs.objects.get_returns(start_date, end_date, asset_list).values(*columns_required)
asset_weight_ts = AssetWeightTs.objects.get_weights(start_date, end_date, asset_list).values(*columns_required2)

# Convert to df

merged_df = pd.merge(left=asset_return_ts_df, right=asset_weight_ts_df, on=['data_date', 'asset_id'], how='inner')

Any solution which reduces 2 separate queries to one and helps compute the weighted sum would be greatly appreciated

CodePudding user response：

a Subquery with an OuterRef in an annotation could help you with that.

Something like:

weights_subquery = AssetWeightTs.objects.filter(
    data_date=OuterRef('data_date'),
    asset_id=OuterRef('asset_id')
).values("weight")[:1]

return_query = AssetReturnTs.objects.annotate(
    weight=Subquery(weights_subquery)
)

This would give you all 4 values you want in each object of return_query