#29262: Custom Left Outer Join in Queries
-------------------------------------+-------------------------------------
Reporter: Sassan Haradji | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution:
Keywords: ORM Join | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Alexandr Artemyev):
I believe my case relates to this issue.
I have a user model and a second model like this
{{{
class SecondModel(models.Model):
date = models.DateField()
from_member = models.ForeignKey(User, models.CASCADE, '+',)
to_member = models.ForeignKey(User, models.CASCADE, '+')
first_value = models.IntegerField(default=0)
second_value = models.IntegerField(default=0)
}}}
I want to left join this subquery with the User model on `id=target`:
{{{
subquery = (
SecondModel.objects.filter(
Q(from_member=self.user) | Q(to_member=self.user),
date__gte=start_date, date__lte=end_date,
).annotate(
target=Case(When(Q(from_member=self.user),
then=F('to_member_id')), default=F('from_member_id')),
).values('target').annotate(
sum_first_value=Sum('first_value'),
sum_second_value=Sum('second_value'),
total=F('sum_first_value') + F('sum_second_value'),
)
)
}}}
I imagine it somehow like this:
{{{
qs = User.objects.annotate(subquery=subquery)
}}}
After that filter/exclude/order_by features should work.
Also I should have access to fields like
qs[0].subquery_first_value/qs[0].subquery_second_value
Here is an example SQL query that I want to be generated:
{{{
SELECT "users".*, "subquery".*
FROM "users"
LEFT OUTER JOIN
(SELECT CASE
WHEN "second_model"."from_member_id" = 7 THEN
"second_model"."to_member_id"
ELSE "second_model"."from_member_id"
END AS "target",
SUM("second_model"."first_value") AS "sum_first_value",
SUM("second_model"."second_value") AS "sum_second_value",
FROM "second_model"
WHERE ("second_model"."from_member_id" = 7
OR "second_model"."to_member_id" = 7) -- also the condition for
date on start and end
GROUP BY CASE
WHEN "second_model"."from_member_id" = 7 THEN
"second_model"."to_member_id"
ELSE "second_model"."from_member_id"
END) subquery ON ("users"."id" = subquery."target")
WHERE "users"."id" = 502
}}}
I found a solution that resolves my problem:
https://stackoverflow.com/a/42816689/5628843
using CustomJoin helps me, but to access values i need to use extra, and
this code doesn’t look very nice
{{{
join_to(Users, subquery, 'id', 'target', queryset, 'subquery').extra(
select={
field_name: f'COALESCE({field_name}, 0)'
for field_name in (
'sum_first_value',
'sum_second_value',
)
}
)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/29262#comment:14>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/065.8df639bc920e71c8e134507f1c929e83%40djangoproject.com.
