Re: [Django] #21604: Embed raw queries as subqueries when used with an __in filter

[Django]

#21604: Embed raw queries as subqueries when used with an __in filter
-+-
 Reporter:  alex@…   |Owner:  William
 Type:  New feature  |   Status:  assigned
Component:  Database layer   |  Version:  dev
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  1
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Aivars Kalvāns):

 * cc: Aivars Kalvāns (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/0107018c4504e82e-79f7a350-be76-426a-9ac0-b36612a4ee75-00%40eu-central-1.amazonses.com.

Re: [Django] #21604: Embed raw queries as subqueries when used with an __in filter

[Django]

#21604: Embed raw queries as subqueries when used with an __in filter
-+-
 Reporter:  alex@…   |Owner:  William
 Type:  New feature  |   Status:  assigned
Component:  Database layer   |  Version:  dev
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  1
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Simon Charette):

 The submitted report was about making `RawQuerySet` work but if `RawSQL`
 works, and didn't exist at the time when this report was created 10 years
 ago, I'd be inclined to close this one as ''wontfix'' at this point.

-- 
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/0107018c4487dc0e-9521ade2-5152-4cee-9dc4-3f245265af53-00%40eu-central-1.amazonses.com.

Re: [Django] #21604: Embed raw queries as subqueries when used with an __in filter

[Django]

#21604: Embed raw queries as subqueries when used with an __in filter
-+-
 Reporter:  alex@…   |Owner:  William
 Type:  New feature  |   Status:  assigned
Component:  Database layer   |  Version:  dev
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  1
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Aivars Kalvāns):

 https://docs.djangoproject.com/en/4.2/ref/models/expressions/#raw-sql-
 expressions

-- 
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/0107018c43871fc6-0c425970-0e19-40a6-9989-98c3f3b97bd6-00%40eu-central-1.amazonses.com.

Re: [Django] #21604: Embed raw queries as subqueries when used with an __in filter

[Django]

#21604: Embed raw queries as subqueries when used with an __in filter
-+-
 Reporter:  alex@…   |Owner:  William
 Type:  New feature  |   Status:  assigned
Component:  Database layer   |  Version:  dev
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  1
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Aivars Kalvāns):

 Hi!

 Is this still a thing? I had a similar need recently and solved it by
 using RawSQL:

 {{{
 from django.db.models.expressions import RawSQL

 NamedCategory.objects.create(id=1, name="first")
 NamedCategory.objects.create(id=2, name="second")
 NamedCategory.objects.create(id=3, name="third")
 NamedCategory.objects.create(id=4, name="fourth")

 query = DumbCategory.objects.filter(
 id__in=RawSQL("SELECT id FROM queries_dumbcategory WHERE id >= %s",
 params=[3])
 )

 self.assertEqual(set(query.values_list("id", flat=True)), {3, 4})
 print(query.query)
 }}}

 this works as expected and prints out

 {{{
 SELECT "queries_dumbcategory"."id" FROM "queries_dumbcategory" WHERE
 "queries_dumbcategory"."id" IN (SELECT id FROM queries_dumbcategory WHERE
 id >= 3)
 }}}

 which confirms that the raw subquery was embedded.
 Is there something that would make it better or more useful by using
 RawQuerySet?

-- 
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/0107018c4382c62c-f9980168-3393-4e57-af19-4c64026bd756-00%40eu-central-1.amazonses.com.

Re: [Django] #21604: Embed raw queries as subqueries when used with an __in filter

[Django]

#21604: Embed raw queries as subqueries when used with an __in filter
-+-
 Reporter:  alex@…   |Owner:  William
 Type:  New feature  |   Status:  assigned
Component:  Database layer   |  Version:  dev
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  1
Easy pickings:  0|UI/UX:  0
-+-
Changes (by William):

 * owner:  nobody => William
 * 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/01070184aef86750-3f96654c-8190-4702-aac2-63be7abd7a8e-00%40eu-central-1.amazonses.com.

Re: [Django] #21604: Embed raw queries as subqueries when used with an __in filter

[Django]

#21604: Embed raw queries as subqueries when used with an __in filter
-+-
 Reporter:  alex@…   |Owner:  nobody
 Type:  New feature  |   Status:  new
Component:  Database layer   |  Version:  dev
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  1
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Simon Charette):

 Andreas, if this is something you'd like to see in Django
 [https://docs.djangoproject.com/en/4.1/internals/contributing/writing-
 code/ you could give a shot submitting a PR] based on the approach
 described in comment:3

 Something along these lines, with regression tests, should get you almost
 all the way there

 {{{#!diff
 diff --git a/django/db/models/sql/query.py b/django/db/models/sql/query.py
 index c2a71ff589..4845ae9e90 100644
 --- a/django/db/models/sql/query.py
 +++ b/django/db/models/sql/query.py
 @@ -82,6 +82,8 @@ def get_children_from_q(q):
  class RawQuery:
  """A single raw SQL query."""

 +has_select_fields = True
 +
  def __init__(self, sql, using, params=()):
  self.params = params
  self.sql = sql
 @@ -151,6 +153,9 @@ def _execute_query(self):
  self.cursor = connection.cursor()
  self.cursor.execute(self.sql, params)

 +def as_sql(self, compiler, connection):
 +return self.sql, self.params
 +

  ExplainInfo = namedtuple("ExplainInfo", ("format", "options"))
 }}}

-- 
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/010701842e4e5608-b38b9dfd-2d97-4cbd-ba0f-76b98e2314ab-00%40eu-central-1.amazonses.com.

Re: [Django] #21604: Embed raw queries as subqueries when used with an __in filter

[Django]

#21604: Embed raw queries as subqueries when used with an __in filter
-+-
 Reporter:  alex@…   |Owner:  nobody
 Type:  New feature  |   Status:  new
Component:  Database layer   |  Version:  dev
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  1
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Andreas Galazis):

 Any news? This is a must if you want to optimize subqueries that cannot be
 generated by 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 view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/010701842ccb0fd5-dc3e60e2-0d44-4265-9ae9-eb8c1683dd49-00%40eu-central-1.amazonses.com.

Re: [Django] #21604: Embed raw queries as subqueries when used with an __in filter

[Django]

#21604: Embed raw queries as subqueries when used with an __in filter
-+-
 Reporter:  alex@…   |Owner:  nobody
 Type:  New feature  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  1
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Simon Charette):

 This should be as easy as implementing `RawQuery.as_sql` to return
 `(self.sql, self.params)` and setting `has_select_fields=True` as class
 attribute.

-- 
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/074.ecd49dcf3ca22bcf9244a248e7f89bb8%40djangoproject.com.

Re: [Django] #21604: Embed raw queries as subqueries when used with an __in filter

[Django]

#21604: Embed raw queries as subqueries when used with an __in filter
-+-
 Reporter:  alex@…   |Owner:  nobody
 Type:  New feature  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |   Resolution:
 Severity:  Normal   | Triage Stage:  Accepted
 Keywords:   |  Needs documentation:  0
Has patch:  1|  Patch needs improvement:  1
  Needs tests:  0|UI/UX:  0
Easy pickings:  0|
-+-
Changes (by akaariai):

 * needs_better_patch:  0 => 1


Comment:

 Sorry for not responding to this ticket sooner.

 I think the patch is a bit too invasive for this ticket. We only need
 support for `somefield__in=rawqs`. It would be hopefully possible to just
 alter the way how the SQL is generated when used in an `in` lookup. In
 particular there shouldn't be any need

 I wonder if it would be better to just add support for
 `qs.filter(somefield__in=RawQuery(sql_str, params))` (and also do the same
 for other lookup types if at all possible). This would be a lot easier to
 support (actually, this will be likely extremely easy to do after #14030).
 Of course, this doesn't allow one to use an existing raw query.

 If this could be implemented with just wrapping the query in subquery when
 used from `__in` lookup, then I think supporting `somefield__in=rawqs`
 directly is OK. If not, then lets focus on adding support for
 `somefield__in=RawQuery(sql_str, params)`. In other words, the current
 patch seems too complicated for the added feature.

-- 
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/074.eed172b419b38bddd9a9dfa80e91f538%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #21604: Embed raw queries as subqueries when used with an __in filter

[Django]

#21604: Embed raw queries as subqueries when used with an __in filter
-+-
 Reporter:  alex@…   |Owner:  nobody
 Type:  New feature  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |   Resolution:
 Severity:  Normal   | Triage Stage:  Accepted
 Keywords:   |  Needs documentation:  0
Has patch:  1|  Patch needs improvement:  0
  Needs tests:  0|UI/UX:  0
Easy pickings:  0|
-+-
Changes (by mjtamlyn):

 * needs_better_patch:   => 0
 * stage:  Unreviewed => Accepted
 * needs_tests:   => 0
 * needs_docs:   => 0


-- 
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/074.2a66d7c9dff568a03882e29645bec71c%40djangoproject.com.
For more options, visit https://groups.google.com/groups/opt_out.