select_related() for fetching individual fields
1,037 views
Skip to first unread message
Дилян Палаузов
Jan 26, 2018, 12:24:20 PM1/26/18
to django-developers
Hello,
Provided:
class M(models.Model):
n = models.IntegerField()
r = models.IntegerField()
t = models.IntegerField()
s = models.IntegerField()
u = models.IntegerField()
class F(models.Model):
g = models.ForeignKey(M, on_delete=models.CASCADE)
a = models.IntegerField()
b = models.IntegerField()
c = models.IntegerField()
d = models.IntegerField()
e = models.IntegerField()
I want to obtain with a queryset all fields from F and M.n, but not the other fields of M like M.r, so that a single SELECT is generated, that doesn't pick unnecessary columns.
F.objects.select_related('g').defer('g__r', 'g__t', 'g__s', 'g__u') does work, but I don't like it, as I want to say only M.n has to be obtained, and not the others.
prefetch_related() is also suboptimal, as it creates two SQL queries instead of one. only() does not let me specify 'All fields of F'.
So here neither only() nor defer() nor prefetch_related() are good.
F.objects.select_related('g__n') would fit from logic, but does not work (django.core.exceptions.FieldError: Non-relational field given in select_related: 'n'. Choices are: (none))
I propose teaching select_related() to accept the above expression.
Confer https://code.djangoproject.com/ticket/29072 .
Regards
Дилян
Provided:
class M(models.Model):
n = models.IntegerField()
r = models.IntegerField()
t = models.IntegerField()
s = models.IntegerField()
u = models.IntegerField()
class F(models.Model):
g = models.ForeignKey(M, on_delete=models.CASCADE)
a = models.IntegerField()
b = models.IntegerField()
c = models.IntegerField()
d = models.IntegerField()
e = models.IntegerField()
I want to obtain with a queryset all fields from F and M.n, but not the other fields of M like M.r, so that a single SELECT is generated, that doesn't pick unnecessary columns.
F.objects.select_related('g').defer('g__r', 'g__t', 'g__s', 'g__u') does work, but I don't like it, as I want to say only M.n has to be obtained, and not the others.
prefetch_related() is also suboptimal, as it creates two SQL queries instead of one. only() does not let me specify 'All fields of F'.
So here neither only() nor defer() nor prefetch_related() are good.
F.objects.select_related('g__n') would fit from logic, but does not work (django.core.exceptions.FieldError: Non-relational field given in select_related: 'n'. Choices are: (none))
I propose teaching select_related() to accept the above expression.
Confer https://code.djangoproject.com/ticket/29072 .
Regards
Дилян
Adam Johnson
Jan 26, 2018, 1:05:43 PM1/26/18
to django-d...@googlegroups.com
F.objects.select_related('g__n') looks a bit convoluted to me.
Also I'd say it's generally a rare case that such an optimization is worth it, so it's probably not useful to add ORM shortcut syntax for it.
You can already do something like F.objects.select_related('g').only('g__n', *[f.name for f in F._meta.get_fields()]) and write a small helper function for the list comprehension to avoid typing.
Also I'd say it's generally a rare case that such an optimization is worth it, so it's probably not useful to add ORM shortcut syntax for it.
Дилян
--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscribe@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/d9a58b37-d084-2efe-33b0-4cd9b8289796%40aegee.org.
For more options, visit https://groups.google.com/d/optout.
Adam
Дилян Палаузов
Jan 29, 2018, 7:44:41 AM1/29/18
to django-d...@googlegroups.com, Adam Johnson
Hello Adam,
my use case is:
-- because on post_save signal the F object is serialized, all fields have to be fetched in advance, otherwise the serializer does a separate SQL for each uncached field
-- listing explicitly the fields in defer/only clauses is not smart, as the queries have to be updated every time the current or related model change
-- I want to have efficient SQL
Users expect from F.object.select_related('g__n') to fetch the n-field, it does not matter if there is a number or JOIN-object behind 'n'.
https://docs.djangoproject.com/en/2.0/ref/models/querysets/ contains this example:
Book.objects.select_related('author__hometown')
so if "hometown" is a separate model, the Queryset works as expected, but not if it is a primitive field,
Besides, the syntax proposed by me has no other meaning currently and follows the overall logic. (In fact, I was expecting that Django does it the way I describe, because of the aforementioned example).
Regards
Дилян
On 01/26/18 19:05, Adam Johnson wrote:
> F.objects.select_related('g__n') looks a bit convoluted to me.
>
> You can already do something like F.objects.select_related('g').only('g__n', *[f.name <http://f.name> for f in F._meta.get_fields()]) and write a small helper function for the list comprehension to avoid typing.
> To post to this group, send email to django-d...@googlegroups.com <mailto:django-d...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/django-developers <https://groups.google.com/group/django-developers>.
> To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/d9a58b37-d084-2efe-33b0-4cd9b8289796%40aegee.org <https://groups.google.com/d/msgid/django-developers/d9a58b37-d084-2efe-33b0-4cd9b8289796%40aegee.org>.
> For more options, visit https://groups.google.com/d/optout <https://groups.google.com/d/optout>.
>
>
>
>
> --
> Adam
> To post to this group, send email to django-d...@googlegroups.com <mailto:django-d...@googlegroups.com>.
my use case is:
-- because on post_save signal the F object is serialized, all fields have to be fetched in advance, otherwise the serializer does a separate SQL for each uncached field
-- listing explicitly the fields in defer/only clauses is not smart, as the queries have to be updated every time the current or related model change
-- I want to have efficient SQL
Users expect from F.object.select_related('g__n') to fetch the n-field, it does not matter if there is a number or JOIN-object behind 'n'.
https://docs.djangoproject.com/en/2.0/ref/models/querysets/ contains this example:
Book.objects.select_related('author__hometown')
so if "hometown" is a separate model, the Queryset works as expected, but not if it is a primitive field,
Besides, the syntax proposed by me has no other meaning currently and follows the overall logic. (In fact, I was expecting that Django does it the way I describe, because of the aforementioned example).
Regards
Дилян
On 01/26/18 19:05, Adam Johnson wrote:
> F.objects.select_related('g__n') looks a bit convoluted to me.
>
>
> Also I'd say it's generally a rare case that such an optimization is worth it, so it's probably not useful to add ORM shortcut syntax for it.
>
> Also I'd say it's generally a rare case that such an optimization is worth it, so it's probably not useful to add ORM shortcut syntax for it.
>
> On 26 January 2018 at 17:24, Дилян Палаузов <dpa-d...@aegee.org <mailto:dpa-d...@aegee.org>> wrote:
>
> Hello,
>
> Provided:
>
> class M(models.Model):
> n = models.IntegerField()
> r = models.IntegerField()
> t = models.IntegerField()
> s = models.IntegerField()
> u = models.IntegerField()
>
> class F(models.Model):
> g = models.ForeignKey(M, on_delete=models.CASCADE)
> a = models.IntegerField()
> b = models.IntegerField()
> c = models.IntegerField()
> d = models.IntegerField()
> e = models.IntegerField()
>
>
> I want to obtain with a queryset all fields from F and M.n, but not the other fields of M like M.r, so that a single SELECT is generated, that doesn't pick unnecessary columns.
>
> F.objects.select_related('g').defer('g__r', 'g__t', 'g__s', 'g__u') does work, but I don't like it, as I want to say only M.n has to be obtained, and not the others.
>
> prefetch_related() is also suboptimal, as it creates two SQL queries instead of one. only() does not let me specify 'All fields of F'.
>
> So here neither only() nor defer() nor prefetch_related() are good.
>
> F.objects.select_related('g__n') would fit from logic, but does not work (django.core.exceptions.FieldError: Non-relational field given in select_related: 'n'. Choices are: (none))
>
> I propose teaching select_related() to accept the above expression.
>
> Confer https://code.djangoproject.com/ticket/29072 <https://code.djangoproject.com/ticket/29072> .
>
> Hello,
>
> Provided:
>
> class M(models.Model):
> n = models.IntegerField()
> r = models.IntegerField()
> t = models.IntegerField()
> s = models.IntegerField()
> u = models.IntegerField()
>
> class F(models.Model):
> g = models.ForeignKey(M, on_delete=models.CASCADE)
> a = models.IntegerField()
> b = models.IntegerField()
> c = models.IntegerField()
> d = models.IntegerField()
> e = models.IntegerField()
>
>
> I want to obtain with a queryset all fields from F and M.n, but not the other fields of M like M.r, so that a single SELECT is generated, that doesn't pick unnecessary columns.
>
> F.objects.select_related('g').defer('g__r', 'g__t', 'g__s', 'g__u') does work, but I don't like it, as I want to say only M.n has to be obtained, and not the others.
>
> prefetch_related() is also suboptimal, as it creates two SQL queries instead of one. only() does not let me specify 'All fields of F'.
>
> So here neither only() nor defer() nor prefetch_related() are good.
>
> F.objects.select_related('g__n') would fit from logic, but does not work (django.core.exceptions.FieldError: Non-relational field given in select_related: 'n'. Choices are: (none))
>
> I propose teaching select_related() to accept the above expression.
>
>
> Regards
> Дилян
>
> --
> You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to django-develop...@googlegroups.com <mailto:django-developers%2Bunsu...@googlegroups.com>.
> Regards
> Дилян
>
> --
> You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
> To post to this group, send email to django-d...@googlegroups.com <mailto:django-d...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/django-developers <https://groups.google.com/group/django-developers>.
> To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/d9a58b37-d084-2efe-33b0-4cd9b8289796%40aegee.org <https://groups.google.com/d/msgid/django-developers/d9a58b37-d084-2efe-33b0-4cd9b8289796%40aegee.org>.
> For more options, visit https://groups.google.com/d/optout <https://groups.google.com/d/optout>.
>
>
>
>
> --
> Adam
>
> --
> You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to django-develop...@googlegroups.com <mailto:django-develop...@googlegroups.com>.
> --
> You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
> To post to this group, send email to django-d...@googlegroups.com <mailto:django-d...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/django-developers.
> To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/CAMyDDM3bpJnz%2BPNux3XG0VEWJV0vMjqJ2ex_uQyU5nLL3wjS1A%40mail.gmail.com <https://groups.google.com/d/msgid/django-developers/CAMyDDM3bpJnz%2BPNux3XG0VEWJV0vMjqJ2ex_uQyU5nLL3wjS1A%40mail.gmail.com?utm_medium=email&utm_source=footer>.
Adam Johnson
Jan 29, 2018, 3:49:30 PM1/29/18
to Дилян Палаузов, django-d...@googlegroups.com
-- listing explicitly the fields in defer/only clauses is not smart, as the queries have to be updated every time the current or related model change
Not with the snippet I shared
-- I want to have efficient SQL
In my experience, it's rarely the case that only()/defer() make a noticeable difference. Queries tend to be dominated by network time back/forth with the database and disk access, where most DB's are row-oriented so accessing one field loads the whole row. You have to have fairly large fields before you can notice the effect of deferring them.
In all I think this suggestion is just adding syntactical sugar to the ORM. Since your use case is already possible, just more explicitly, it doesn't seem worth the effort of adding and maintaining code for this.
I think it could be done in a third party package, so feel free to try that, and if gets support I'd be more interested.
To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscribe@googlegroups.com <mailto:django-developers%2Bunsubs...@googlegroups.com>.
To post to this group, send email to django-developers@googlegroups.com <mailto:django-developers@googlegroups.com>.
Visit this group at https://groups.google.com/group/django-developers <https://groups.google.com/group/django-developers>.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/d9a58b37-d084-2efe-33b0-4cd9b8289796%40aegee.org <https://groups.google.com/d/msgid/django-developers/d9a58b37-d084-2efe-33b0-4cd9b8289796%40aegee.org>.
For more options, visit https://groups.google.com/d/optout <https://groups.google.com/d/optout>.
--
Adam
--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscribe@googlegroups.com <mailto:django-developers+unsubsc...@googlegroups.com>.
To post to this group, send email to django-developers@googlegroups.com <mailto:django-developers@googlegroups.com>.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/CAMyDDM3bpJnz%2BPNux3XG0VEWJV0vMjqJ2ex_uQyU5nLL3wjS1A%40mail.gmail.com <https://groups.google.com/d/msgid/django-developers/CAMyDDM3bpJnz%2BPNux3XG0VEWJV0vMjqJ2ex_uQyU5nLL3wjS1A%40mail.gmail.com?utm_medium=email&utm_source=footer>.
For more options, visit https://groups.google.com/d/optout.
Adam
Reply all
Reply to author
Forward
0 new messages