Re: [Django] #29262: Custom Left Outer Join in Queries

[Django]

#29262: Custom Left Outer Join in Queries
-+-
 Reporter:  Sassan Haradji   |Owner:
 |  faizan2700
 Type:  New feature  |   Status:  assigned
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 Mariusz Felisiak):

 faizan2700, are you sure that you want to work on multiple tickets at
 once?

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/0107018a8da6d1ea-15f65e4c-dcde-4fc4-9ca2-8b6683bcf0ef-00%40eu-central-1.amazonses.com.

Re: [Django] #29262: Custom Left Outer Join in Queries

[Django]

#29262: Custom Left Outer Join in Queries
-+-
 Reporter:  Sassan Haradji   |Owner:
 |  faizan2700
 Type:  New feature  |   Status:  assigned
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
-+-
Changes (by faizan2700):

 * owner:  (none) => faizan2700
 * status:  new => assigned


-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/0107018a8d9e14e6-f2767125-8df3-4ece-9948-a14f899eb08e-00%40eu-central-1.amazonses.com.

Re: [Django] #29262: Custom Left Outer Join in Queries

[Django]

#29262: Custom Left Outer Join in Queries
-+-
 Reporter:  Sassan Haradji   |Owner:  (none)
 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 faizan2700):

 Is anyone working on it. Please assign it to me.

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/0107018a8d695767-88fd5b48-2c10-4eec-bde3-de689d60fdfc-00%40eu-central-1.amazonses.com.

Re: [Django] #29262: Custom Left Outer Join in Queries

[Django]

#29262: Custom Left Outer Join in Queries
-+-
 Reporter:  Sassan Haradji   |Owner:  (none)
 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
-+-
Changes (by Gaurav Joshi):

 * owner:  Gaurav Joshi => (none)
 * status:  assigned => new


-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/010701872c05db23-9503c422-99af-46e9-99cf-d9694a06c8c9-00%40eu-central-1.amazonses.com.

Re: [Django] #29262: Custom Left Outer Join in Queries

[Django]

#29262: Custom Left Outer Join in Queries
-+-
 Reporter:  Sassan Haradji   |Owner:  Gaurav
 |  Joshi
 Type:  New feature  |   Status:  assigned
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 Gaurav Joshi):

 Replying to [comment:22 Mariusz Felisiak]:
 > Replying to [comment:21 Gaurav Joshi]:
 > > Could you please assign the issue to me so that I can work on it?
 >
 > You've already assigned it to yourself.

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/0107018721b5e5de-37a75acf-0458-41cd-88e7-e8e42e52e17c-00%40eu-central-1.amazonses.com.

Re: [Django] #29262: Custom Left Outer Join in Queries

[Django]

#29262: Custom Left Outer Join in Queries
-+-
 Reporter:  Sassan Haradji   |Owner:  Gaurav
 |  Joshi
 Type:  New feature  |   Status:  assigned
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 Gaurav Joshi):

 Yes, I assigned that issue to myself . As a new member here, I'm still
 trying to navigate the process and it has caused some confusion for me

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/0107018721b56718-576bde9b-3077-40a5-b876-f6bf37ba2fd9-00%40eu-central-1.amazonses.com.

Re: [Django] #29262: Custom Left Outer Join in Queries

[Django]

#29262: Custom Left Outer Join in Queries
-+-
 Reporter:  Sassan Haradji   |Owner:  Gaurav
 |  Joshi
 Type:  New feature  |   Status:  assigned
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 Mariusz Felisiak):

 Replying to [comment:21 Gaurav Joshi]:
 > Could you please assign the issue to me so that I can work on it?

 You've already assigned it to yourself.

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/0107018721adc0d8-1b9e02b2-d4fe-4548-90ca-fb72e64dc839-00%40eu-central-1.amazonses.com.

Re: [Django] #29262: Custom Left Outer Join in Queries

[Django]

#29262: Custom Left Outer Join in Queries
-+-
 Reporter:  Sassan Haradji   |Owner:  Gaurav
 |  Joshi
 Type:  New feature  |   Status:  assigned
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
-+-
Changes (by Gaurav Joshi):

 * owner:  nobody => Gaurav Joshi
 * status:  new => assigned


Comment:

 Could you please assign the issue to me so that I can work on it?

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/01070187215bd2b8-6cc4e4f6-935a-4abd-af0d-a9cda984f0bd-00%40eu-central-1.amazonses.com.

Re: [Django] #29262: Custom Left Outer Join in Queries

[Django]

#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
-+-
Changes (by elonzh):

 * cc: elonzh (added)


-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/010701864a1187a3-3ffe1784-d131-46b6-9ba3-7808834c3257-00%40eu-central-1.amazonses.com.

Re: [Django] #29262: Custom Left Outer Join in Queries

[Django]

#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 Thorben Luepkes):

 I think I am running into a similar problem here, where I would like to
 append multiple `conditions` onto my join:
 I will try to be precise with this as much as possible.
 Imagine these two models. whose relation was set up years ago:


 {{{

 class Event(models.Model):
 instance_created_date = models.DateTimeField(auto_now_add=True)
 car = models.ForeignKey(Car, on_delete=models.CASCADE,
 related_name="car_events")
 ...
 a lot of normal text fields here, but they dont matter for this
 problem.
 }}}


 and


 {{{

 class Car(models.Model):
 a lot of text fields here, but they dont matter for this problem.
 hide_from_company_search = models.BooleanField(default=False)
 images = models.ManyToManyField(Image, through=CarImage)
 }}}



 Lets say I want to query the amount of events for a given car:
 {{{
 def get_car_events_qs() -> QuerySet:
 six_days_ago = (timezone.now().replace(hour=0, minute=0, second=0,
 microsecond=0) - timedelta(days=6))
 cars = Car.objects.prefetch_related(
 'car_events',
 ).filter(
 some_conditions_on_fields=False,
 ).annotate(
 num_car_events=Count(
 'car_events',
 filter=Q(car_events__instance_created_date__gt=six_days_ago),
 distinct=True)
 )

 return cars
 }}}


 The really tricky part for this is the performance of the query: `Cars`
 has `450.000` entries, and `Events` has `156.850.048`. All fields that I
 am using to query are indexed. The query takes around 4 minutes to
 complete, depending on the db load. It took 18 minutes before adding the
 indicies.

 This above ORM query will result in the following sql:


 {{{
 SELECT
   "core_car"."id",
 COUNT("analytics_carevent"."id") FILTER (WHERE
 ("analytics_carevent"."event" = 'view'
 AND "analytics_carevent"."instance_created_date"
 >= '2022-05-10T07:45:16.672279+00:00'::timestamptz
 AND "analytics_carevent"."instance_created_date" <
 '2022-05-11T07:45:16.672284+00:00'::timestamptz)) AS "num_cars_view",
 LEFT OUTER JOIN "analytics_carevent" ON ("core_car"."id" =
 "analytics_carevent"."car_id")
 WHERE
  ... some conditions that dont matter
 GROUP BY
 "core_car"."id"
 }}}



 I somehow suspect this `FILTER` to be a problem.
 I tried with

 {{{
 .annotate(num_car_events=Count('car_events'))
 }}}

 and moving the `car_events__instance_created_date__gt=six_days_ago` into
 the `filter`:

 {{{
 .filter(some_conditions_on_fields=False,
 car_events__instance_created_date__gt=six_days_ago)
 }}}


 But of course this would filter out Cars with no Events, which is not what
 we want - but it is super fast!
 I fiddled a bit with it in raw sql and came to his nice working example,
 that I now would like to write into ORM, since we dont really want to use
 rawsql. This query takes `2.2s`, which is in our acceptable boundary, but
 far less than the 18minutes.


 {{{
 SELECT
 "core_car"."id",
 COUNT(DISTINCT "analytics_carevent"."id") AS "num_cars_view",
 FROM
 "core_car"
 LEFT JOIN "analytics_carevent" ON ("core_car"."id" =
 "analytics_carevent"."car_id" AND "analytics_carevent"."event" = 'view'
 AND "analytics_carevent"."instance_created_date" >
 '2022-05-14T00:00:00+02:00'::timestamptz
 AND "analytics_carevent"."instance_created_date" <=
 '2022-05-15T00:00:00+02:00'::timestamptz)

 WHERE (some conditions that dont matter)
 GROUP BY "core_car"."id";
 }}}



 My question now is:
 How can I make the above query into the ORM?
 I need to put the "filter" or conditions onto the `left join`. If I just
 use `filter()` it will just put it into the `where` clause, which is
 wrong.
 I tried:


 {{{
  two_days_ago = (timezone.now().replace(hour=0, minute=0, second=0,
 microsecond=0) - timedelta(days=2))
 cars = Car.objects.prefetch_related(
 'car_events',
 ).filter(some_filters,)
 }}}

 and

 {{{
 cars =
 
cars.annotate(events=FilteredRelation('car_events')).filter(car_events__car_id__in=cars.values_list("id",
 flat=True), car_events__instance_created_date__gt=six_days_ago)
 }}}


 But I dont think this is 

Re: [Django] #29262: Custom Left Outer Join in Queries

[Django]

#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 Hugo Maingonnat):

 I also need a way to generate a query with custom a join:

 {{{
 SELECT "main_table"."id" FROM "main_table" LEFT JOIN (SELECT
 distinct(main_id) FROM through_table WHERE other_id = 14) as foo ON
 foo.main_id=main_table.id ORDER BY "main_table"."id" DESC LIMIT 1;
 }}}

 The main queryset is generated by the view, and the left join subquery is
 a custom django-filter. Using a `id__in` would work but I have to repeat
 the ORDER BY DESC and LIMIT 1 in the subquery (otherwise it gets really
 ineficient) and ideally the filter should be independant from the initial
 queryset (so it does not know the ordering and limit). Also django-filter
 needs to keep a queryset so I cannot generate a RawQueryset (I am not even
 sure this is doable).

 Having a `join` keyword in the extra function would have unlocked me.

 The FilteredRelation object is only limited to a condition (q object) and
 not a full SELECT query. If it could take a RawSQL or a queryset it would
 be perfect.

 So it looks like I cannot generate a quite simple query using any of the
 django function, a bit frustrating :(

 Like @Alexandr Artemyev I found out the CustomJoin trick but it looks very
 brittle.

 Hope my feedback will help understanding the issue :)

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.3ae24a83b63ae19ecac4b3b3f1f50438%40djangoproject.com.

Re: [Django] #29262: Custom Left Outer Join in Queries

[Django]

#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
-+-
Changes (by Alex Scott):

 * cc: Alex Scott (added)


Comment:

 @simon, `FilteredRelation` is great but only allows custom outer joins one
 level deep at the moment so isn't a full solution for many use cases.
 Added a comment here as well: https://code.djangoproject.com/ticket/29789

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.e60db70384678b1f2e41d927e6c11d7c%40djangoproject.com.

Re: [Django] #29262: Custom Left Outer Join in Queries

[Django]

#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
-+-
Changes (by frnhr):

 * cc: frnhr (added)


-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.e233d1dc3b99a41fb4f3abbed27d2faf%40djangoproject.com.

Re: [Django] #29262: Custom Left Outer Join in Queries

[Django]

#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
-+-
Changes (by Ivan Zaikin):

 * cc: Ivan Zaikin (added)


-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.28d0de7f720cf170c3f9c11c2116ca89%40djangoproject.com.

Re: [Django] #29262: Custom Left Outer Join in Queries

[Django]

#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: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.8df639bc920e71c8e134507f1c929e83%40djangoproject.com.

Re: [Django] #29262: Custom Left Outer Join in Queries

[Django]

#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
-+-
Changes (by Alexandr Artemyev):

 * cc: Alexandr Artemyev (added)


-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.c21e7a283cb57156fe84cf28f4d21cbd%40djangoproject.com.

Re: [Django] #29262: Custom Left Outer Join in Queries

[Django]

#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 Simon Charette):

 I think this ticket should either be repurposed or closed as
 `FilteredRelation` does allow custom left outer joins.

 What comment:11 is missing at this point is a way to perform subquery
 pushdown and a way to JOIN subqueries.

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.53f3712afb1ebb37ece0b47698b0c40c%40djangoproject.com.

Re: [Django] #29262: Custom Left Outer Join in Queries

[Django]

#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 Sassan Haradji):

 Unfortunately I don't think so. What I needed was exactly left join.
 The query I needed was a rather complicated query, I try to abstract it
 here so that we can investigate it and find out what's needed in Django
 ORM to achieve it.
 Suppose that I have this table:



 {{{
 CREATE TABLE foo(id,related_id,value,type)
 AS VALUES
 ( 1 , 1,  'A1' , 1 ),
 ( 2 , 1,  'A2' , 2 ),
 ( 3 , 1,  'A3' , 3 ),
 ( 4 , 1,  'A4' , 4 ),
 ( 5 , 1,  'A5' , 5 ),
 ( 6 , 2,  'B1' , 1 ),
 ( 7 , 2,  'B2' , 2 ),
 ( 8 , 2,  'B3' , 3 ),
 ( 9 , 2,  'B4' , 4 ),
 ( 10, 2,  'B5' , 5 )
 ;
 }}}

 I want to aggregate these values and make this intermediate table:
 {{{
 -
 |  id   |   related_id   |  values
 |
 -
 |  1|   1|  (('A1',1),('A2',2),('A3',3),('A4',4),('A5',5))
 |
 -
 |  6|   2|  (('B1',1),('B2',2),('B3',3),('B4',4),('B5',5))
 |
 -
 }}}
 To do so I need to do a simple aggregation:
 {{{
 foo.objects.values('related_id').annotate(
 id=Min('id'),
 values=ArrayAgg(
 Func(
 'value',
 'type',
 function='ARRAY',
 template='%(function)s[%(expressions)s]',
 arg_joiner=',',
 ), output_field=ArrayField(ArrayField(models.FloatField())),
 ),
 )
 }}}
 This will generate this sql query (or something equivalent):
 {{{
 SELECT t.*
 FROM (
   SELECT
 min(id),
 related_id,
 array_agg(ARRAY(value, type)) AS values,
   FROM foo
   GROUP BY id
 ) AS t
 }}}
 So far so good. Then I want to order this query based on value column but
 this order should order values of this column that are in a row that has
 type=X. I can do so by this sql:
 {{{
 SELECT t1.*
 FROM (
   SELECT
 min(id),
 related_id,
 array_agg(ARRAY(value, type)) AS values,
   FROM foo
   GROUP BY id
 ) AS t1
 LEFT OUTER JOIN (SELECT value FROM foo WHERE type=X) AS t2 USING (id)
 ORDER BY t2.value
 }}}
 This is where I need this left join. It should be left join cause I don't
 wanna miss rows that don't have X type value.

 Now do you think it's possible to do the above with current Django ORM
 API?
 Consider that this table is really big, and the above sql query is the
 only one I found that executes in rational time and doesn't miss anything.
 Also the real problem is much more complected, the number of columns that
 are involved are much more so if you think there's room for simplifying
 the above sql solution consider that in my real usecase it may not be
 applicable so I please lets concentrate on interpreting the exact above
 query into Django ORM API and not change the sql query so that it fits the
 API.

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.e88defa24661d0066f9179754a6e7aea%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #29262: Custom Left Outer Join in Queries

[Django]

#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 Simon Charette):

 I'd be curious to know whether or not `FilteredRelation` would solve your
 use case like it did in #29555.

 This expression allows your to specify extra `JOIN` conditions so I'd
 assume annotating the subquery you want to `JOIN` against and then
 referencing it in the `FilteredRelation(condition)` should work. It'd help
 if you could provide your models definition and the exact query you're
 trying to generate through the ORM.

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.5b86cc7c3a28f49cf7eb7ea75d2b6d5a%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #29262: Custom Left Outer Join in Queries

[Django]

#29262: Custom Left Outer Join in Queries
-+-
 Reporter:  Sassan Haradji   |Owner:  nobody
 Type:  New feature  |   Status:  new
Component:  Database layer   |  Version:  2.0
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  ORM Join | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Josh Smeaton):

 I've begun a discussion on the mailing list:
 https://groups.google.com/forum/#!topic/django-developers/2ITfPZlbsao

 Please add your example to that thread if it's different to any already
 listed there.

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.3225b0d83c8ab62fde27894f311994ef%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #29262: Custom Left Outer Join in Queries

[Django]

#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
-+-
Changes (by Josh Smeaton):

 * version:  2.0 =>
 * stage:  Unreviewed => Accepted


-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.84c63196de7fcb25f1563e4abd45bdb0%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #29262: Custom Left Outer Join in Queries

[Django]

#29262: Custom Left Outer Join in Queries
-+-
 Reporter:  Sassan Haradji   |Owner:  nobody
 Type:  New feature  |   Status:  new
Component:  Database layer   |  Version:  2.0
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  ORM Join | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Josh Smeaton):

 * cc: josh.smeaton@… (added)


-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.eae9cfd9ee2e565a4b1fa4183ab984c1%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #29262: Custom Left Outer Join in Queries

[Django]

#29262: Custom Left Outer Join in Queries
-+-
 Reporter:  Sassan Haradji   |Owner:  nobody
 Type:  New feature  |   Status:  new
Component:  Database layer   |  Version:  2.0
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  ORM Join | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Josh Smeaton):

 It's more than likely that I have misinterpreted your request to address
 this issue as a demand for core to fix the issue due to language
 differences. I apologise.

 https://code.djangoproject.com/ticket/26426 (customise joins) and
 https://code.djangoproject.com/ticket/25590 (custom join classes) are
 almost duplicates, but are much more general than the specific feature of
 supporting user-defined left joins. I'd probably argue that
 https://code.djangoproject.com/ticket/26426 should be closed, as we have
 EXISTS expressions now that can solve that specific problem. Would you
 agree Tim?

 I think there are really two features that we should try to support.

 1. Allow users to join data onto a query with a LEFT JOIN
 2. Allow users to add additional conditions onto a JOIN condition

 With those two features we'd get pretty far down the line for supporting
 most common custom join requirements.

 What we'd need to do is come to a consensus on what the right syntax would
 look like to make user defined LEFT JOINs possible. I'm not interested in
 providing hooks into customising already generated SQL. That would just be
 a hack to work around our lack of actual support. That's mostly why the
 linked ticket from 10 years ago languished - it was hacked into .extra()
 which we're no longer committed to updating.

 So what would a decent syntax look like? Should we consider a new queryset
 method? Should we use .annotate()?

 I'll throw out some ideas:

 {{{

 
MyModel.objects.annotate(joined=Outer(OtherModel.objects.filter(mymodel_id=OuterRef('pk')))

 MyModel.objects.outer('othermodel')

 MyModel.objects.outer(othermodel=OtherModel.objects.filter(user=request.user))

 
MyModel.objects.partial_related(othermodel=OtherModel.objects.filter(user=request.user))
 }}}

 Django has tended to avoid using language that maps too closely to SQL in
 the past, though with the addition of more complex expression types that
 hasn't been so much of a blocker. I'd be hesitant to add a new queryset
 method called outer for that reason though. New classes can map to SQL
 because they're not as discoverable and really are for advanced usage.
 Increasing the scope of the queryset api with regard to SQL terminology
 seems off to me.

 This is the kind of question that could probably be asked of the django-
 developers mailing list. There are lots of people with opinions there
 that'd be relevant to this discussion. In principle though, Django should
 definitely support adding left joins to a queryset.

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.1498aa57e6e6437b86fbba07f3794932%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #29262: Custom Left Outer Join in Queries

[Django]

#29262: Custom Left Outer Join in Queries
-+-
 Reporter:  Sassan Haradji   |Owner:  nobody
 Type:  New feature  |   Status:  new
Component:  Database layer   |  Version:  2.0
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  ORM Join | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Tim Graham):

 Duplicate of #26426, "Add a way to customize a QuerySet's joins"?

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.edff69a192f60179786bcac5b2d3f078%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #29262: Custom Left Outer Join in Queries

[Django]

#29262: Custom Left Outer Join in Queries
-+-
 Reporter:  Sassan Haradji   |Owner:  nobody
 Type:  New feature  |   Status:  new
Component:  Database layer   |  Version:  2.0
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  ORM Join | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Sassan Haradji):

 SpamBayes doesn't let me to send this part because it's referring some
 google searches, so I post it in a gist:
 https://gist.github.com/sassanh/43ef664872c322a5f88434f10c5ce4ea


 Btw, there's an implicit message in every issue reported in open source
 community, the reporter doesn't ask any specific person to do it, as he
 has no right to do so as he's not paying them. What he wants by reporting
 an issue is trying to make consensus that the issue exists, get
 acknowledgement from the community that issue exists and someone (maybe
 himself, maybe a current contributor or maybe someone he's 2 years old now
 and is going to become a software developer in future) should solve it. An
 open issue/ticket means it is a step toward progression of the project, so
 while I do not ask current contributors to solve this issue, I do ask them
 to not abuse their privileges on this ticketing system and do not close it
 until it's either implemented in Django or someone provides good logic
 "why a developer using django framework can do whatever he wants without
 using custom outer joins."

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.73eccc6d771aab83cf3b0d1e64ee8868%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #29262: Custom Left Outer Join in Queries

[Django]

#29262: Custom Left Outer Join in Queries
-+-
 Reporter:  Sassan Haradji   |Owner:  nobody
 Type:  New feature  |   Status:  new
Component:  Database layer   |  Version:  2.0
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  ORM Join | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Sassan Haradji):

 4. I don't need union, I'm not trying to add rows to my main query, I'm
 trying to add a column to my query via outer join. So unfortunately union
 is not an option.

 5. Would be glad if you find it and share it here so that we can see if it
 solves the problem.

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.6708d5eb03de3629a2a69352112e1605%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #29262: Custom Left Outer Join in Queries

[Django]

#29262: Custom Left Outer Join in Queries
-+-
 Reporter:  Sassan Haradji   |Owner:  nobody
 Type:  New feature  |   Status:  new
Component:  Database layer   |  Version:  2.0
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  ORM Join | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Sassan Haradji):

 1. I like many need LEFT OUTER JOIN not INNER JOIN, if INNER JOIN was an
 efficient alternative to LEFT OUTER JOIN django itself would use it in its
 foreignkey joins, these joins don't do same thing and aren't an
 alternative for eachother.

 2. This is one from "10 years ago":
 https://code.djangoproject.com/ticket/7231

 3. `.raw()` may be good for some use cases, but I don't get it why should
 I lose all the features on a normal query (like count, filter, etc) just
 cause I wanna add a simple column by an outer join to that normal query.
 What I suggested as an "at least alternative" was an escape hatch that
 lets developer use normal queries (not raw queries) and patch them.
 A framework is just a tool in hands of the developer, I think everyone on
 earth trying to build a tool should consider that the tool should not
 limit its users, but it should give him new opportunities. Django provides
 lots of opportunities, but not providing an easy way to patch final sql it
 compiles is not a good thing. There are hard ways, I can subclass
 connection and query and change their behavior but there should be an easy
 way to do it after all (even if django supports left outer join). In
 documentation it should try to convince normal users to not use it and
 talk about security problems and instabilities it may introduce to code.
 But if someone needs it and knows what he's doing, then he should be able
 to do it.

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.a87de363ecc98c017fdacc3376caad49%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #29262: Custom Left Outer Join in Queries

[Django]

#29262: Custom Left Outer Join in Queries
-+-
 Reporter:  Sassan Haradji   |Owner:  nobody
 Type:  New feature  |   Status:  new
Component:  Database layer   |  Version:  2.0
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  ORM Join | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Josh Smeaton):

 A few things.

 1. I wasn't aware there were situations where a LEFT OUTER JOIN would have
 **better** performance than an INNER JOIN, since the LEFT JOIN looks at
 and includes more data. Are you able to provide the SQL for what is being
 generated and for what you want? The model definitions would also help.

 2. There have been lots of questions about customising joins. As far as I
 know, there have been no pull requests implementing such a thing in a
 reasonable manner. If you're aware of any pull requests it'd be good if
 you could share where those are so we can discuss the merits of each
 change.

 3. You're asking for an escape hatch that isn't the escape hatch that
 django is already providing. Django provides .raw() for exactly these
 purposes. If, for some technical reason, .raw() isn't appropriate, please
 discuss why so we can address those particular concerns.

 4. Is it possible to address your situation by using .union()? You can
 represent the FULL JOIN portion with 1 query, and the NULL join portion
 with a 2nd query, the .union() them together.

 5. There was some work done recently on annotating joins onto querysets,
 but I've been unable to find that ticket or patch.

 Now I am picking up from your ticket that you're frustrated. But no open
 source contributor anywhere has ever responded to the equivalent of "this
 situation is really bad for me so you must fix it for me" by jumping to do
 exactly that. If you want people to work, for free, on something you care
 about, then it's always better to approach the conversation in a more
 positive and friendly tone.

 We'd also be open to any contributions you or your company would be
 willing to make provided it made sense for the project.

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.85547f004ef97642ebd81a83b602bd7a%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.