@Tim, As I understood #27332 and #26426 both are only about specifying extra conditions in a join, which is a part of this ticket but isn't the main feature. The main feature here is to be able to join to sub-queries. The example query (using join_to_queryset() function) stated in the ticket will look something like following query in SQL:
SELECT name, age, length, sex, SnakeKillAggr.egg_count AS egg_count, SnakeKillAggr.kill_count AS kill_count
FROM snakes_snake
LEFT OUTER JOIN (
SELECT snake_id, COUNT(id) AS egg_count
FROM snakes_egg
GROUP BY snake_id
) AS SnakeEggAggr ON (snakes_snake.id = SnakeEggAggr.snake_id)
LEFT OUTER JOIN (
SELECT snake_id, COUNT(id) AS kill_count
FROM snakes_kill
GROUP BY snake_id
) AS SnakeKillAggr ON (snakes_snake.id = SnakeKillAggr.snake_id)
And if we want to add an extra condition in the second join such that a snake's kill count is only included if it's greater than it's age, we can do it as follows:
queryset = join_to_queryset(
table=Snake,
subquery=Kill.objects.values('snake').annotate(kill_count=Count('pk')),
table_field='id',
subquery_field='snake_id',
queryset=queryset,
alias='SnakeKillAggr',
extra_restriction_func=lambda where_class, alias, related_alias: (
where_class([ExtraWhere([f'{alias}.kill_count > {related_alias}.age'], ())])
)
).extra(select={'kill_count': 'SnakeKillAggr.kill_count'})
About example code's API is not very clean as it was just a hack to make things work at the time, but it can be cleaned up and made a proper API. Following can be the API:
join_to_queryset() method for queryset with following arguments:
base_queryset_field: Field of base queryset which will be used in primary join condition.
subqueryset: Sub-queryset to join to.
subqueryset_field: Field of base sub-queryset which will be used in primary join condition.
extra_restrictions: List of extra restriction for the join (format of an object in this list needs to be decided).
And join_to_model() method for model with following argumets:
base_queryset_field: Field of base queryset which will be used in primary join condition.
model: Model to join to.
model_field: Field of base model which will be used in primary join condition.
extra_restrictions: List of extra restriction for the join (format of an object in this list needs to be decided).
So the example in the ticket will look something like (format of extra_restrictions needs to be decided):
queryset = queryset.join_to_queryset(
base_queryset_field='id', subqueryset=Kill.objects.values('snake').annotate(kill_count=Count('pk')),
subqueryset_field='snake_id',
extra_restrictions=['{subqueryset}.kill_count > {base_queryset}.age']
)