[Django] #29527: Multi-column comparisons

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

[Django] #29527: Multi-column comparisons

Django
#29527: Multi-column comparisons
-----------------------------------------+--------------------------------
               Reporter:  Ryan Hiebert   |          Owner:  nobody
                   Type:  Uncategorized  |         Status:  new
              Component:  Uncategorized  |        Version:  2.0
               Severity:  Normal         |       Keywords:  QuerySet.extra
           Triage Stage:  Unreviewed     |      Has patch:  0
    Needs documentation:  0              |    Needs tests:  0
Patch needs improvement:  0              |  Easy pickings:  0
                  UI/UX:  0              |
-----------------------------------------+--------------------------------
 Multi-column comparisons

 In order to implement keyset pagination, AKA the seek method, across
 multiple fields including ordering on non-unique fields, I'm wanting to
 write a PostgreSQL that compares multiple fields in an ordered fashion. To
 my knowledge this isn't possible with the current ORM syntax. My
 particular use-case further complicates this by also wanting a subquery in
 order to avoid serializing more than the unique key in the response.
 Here's a generic example where clause that mirrors my use-case:

 {{{#!sql
 (a,b,c,id) > (SELECT a,b,c,id FROM mytable WHERE id = ?)
 }}}

 What this does special is account for all of the columns when making the
 comparison. So it will only compare the id column if all of the a, b, and
 c columns are the same, to allow for exact indentification in an arbitrary
 ordering.

 In the PostgreSQL docs this feature is called Row Constructor Comparison,
 and the documentation is here: https://www.postgresql.org/docs/9.6/static
 /functions-comparisons.html#ROW-WISE-COMPARISON.

--
Ticket URL: <https://code.djangoproject.com/ticket/29527>
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/054.43ad730db1c7294ad2dd7a97fd423678%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #29527: Multi-column comparisons

Django
#29527: Multi-column comparisons
--------------------------------+--------------------------------------
     Reporter:  Ryan Hiebert    |                    Owner:  nobody
         Type:  Uncategorized   |                   Status:  new
    Component:  Uncategorized   |                  Version:  2.0
     Severity:  Normal          |               Resolution:
     Keywords:  QuerySet.extra  |             Triage Stage:  Unreviewed
    Has patch:  0               |      Needs documentation:  0
  Needs tests:  0               |  Patch needs improvement:  0
Easy pickings:  0               |                    UI/UX:  0
--------------------------------+--------------------------------------

Comment (by Ryan Hiebert):

 With the help of a patient colleague (thanks Tim!) I've been able to
 accomplish what I needed to natively, without using {{{.extra()}}}. With
 one caveat, I needed to add the LHS as a calculated column using annotate,
 which is a bit of a kludge, which ends up returning annotated data that I
 didn't really need. For my case, that's not too big of a deal, but perhaps
 less than ideal.

 It might be deserving of a way to remove annotations that don't need to be
 selected, or to allow filter expressions that allow a dynamic expression
 on both sides. I'm not sure if that deserves its own ticket, if it should
 be on this one, or if perhaps there's already a ticket open for that.

 Here's what we came up with:

 {{{#!python
 from django.db.models import Func, F, Subquery

 def after(queryset, value):
     fields = ['a', 'b', 'c', 'id']
     return (
         queryset
         .annotate(rank=Func(*(F(field) for field in fields),
 function='ROW'))
         .filter(rank=Subquery(queryset.filter(id=value).values(*fields)))
         .order_by(*fields)
     )
 }}}

 One other thing to note is that this opts to use the explicit {{{ROW}}}
 function, rather than relying on that being the result of just using
 parenthesis, because parenthesis alone with a single value doesn't
 necessarily make a {{{ROW}}}.

--
Ticket URL: <https://code.djangoproject.com/ticket/29527#comment:1>
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.832fa3e6c31326b13e3390afd7d02584%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #29527: Multi-column comparisons

Django
In reply to this post by Django
#29527: Multi-column comparisons
-------------------------------------+-------------------------------------
     Reporter:  Ryan Hiebert         |                    Owner:  nobody
         Type:  Uncategorized        |                   Status:  new
    Component:  Database layer       |                  Version:  2.0
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:  QuerySet.extra       |             Triage Stage:
                                     |  Unreviewed
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by Tim Graham):

 * component:  Uncategorized => Database layer (models, ORM)


Comment:

 I'm not sure what's to be done to close this ticket. Can you offer a
 patch?

--
Ticket URL: <https://code.djangoproject.com/ticket/29527#comment:2>
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.03679d6364460d8de71201947d331b27%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #29527: Multi-column comparisons

Django
In reply to this post by Django
#29527: Multi-column comparisons
-------------------------------------+-------------------------------------
     Reporter:  Ryan Hiebert         |                    Owner:  nobody
         Type:  Uncategorized        |                   Status:  closed
    Component:  Database layer       |                  Version:  2.0
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:  needsinfo
     Keywords:  QuerySet.extra       |             Triage Stage:
                                     |  Unreviewed
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by Tim Graham):

 * status:  new => closed
 * resolution:   => needsinfo


--
Ticket URL: <https://code.djangoproject.com/ticket/29527#comment:3>
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.4953146f3ca04a537219ffdd4f611b0a%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.