I guess that Cristiano may be right.
Making the query only on the Intermediate table would be enough for this
case, because the two columns that we need for the query are on this table,
and looks like Django is using a JOIN only to get the "id" of Especialidad.
*Here's an example:*
*GIven the Users...*
In [2]: Usuario.objects.all().values_list('pk', flat=True)
Out[2]: [1, 2]
*...and the Especialidads...*
In [3]: Especialidad.objects.all().values_list('pk', flat=True)
Out[3]: [1, 2]
*...and the M2M relations.*
In [5]: Usuario.objects.get(pk=1).permisosEspecialidad.all().values_list('pk',
flat=True)
Out[5]: [1, 2]
In [6]: Usuario.objects.get(pk=2).permisosEspecialidad.all().values_list('pk',
flat=True)
Out[6]: [2]
----
*The current Django query:*
SELECT "samiBackend_especialidad"."id" FROM "samiBackend_especialidade"
INNER JOIN "samiBackend_usuario_permisosEspecialidad" ON (
"samiBackend_especialidad"."id"
= "samiBackend_usuario_permisosEspecialidad"."especialidade_id" ) WHERE "
samiBackend_usuario_permisosEspecialidad"."usuario_id" = 1;
Results: 1, 2
SELECT "samiBackend_especialidade"."id" FROM "samiBackend_especialidad"
INNER JOIN "samiBackend_usuario_permisosEspecialidad" ON (
"samiBackend_especialidad"."id"
= "samiBackend_usuario_permisosEspecialidad"."especialidade_id" ) WHERE "
samiBackend_usuario_permisosEspecialidad"."usuario_id" = 2;
Results: 2
*...and the simpler query (just on the Intermediate table)*
SELECT "samiBackend_usuario_permisosEspecialidad"."especialidad_id" FROM "
samiBackend_usuario_permisosEspecialidad" WHERE "samiBackend_usuario_
permisosEspecialidad"."usuario_id" = 1;
Results: 1, 2
SELECT "samiBackend_usuario_permisosEspecialidad"."especialidad_id" FROM "
samiBackend_usuario_permisosEspecialidad" WHERE "samiBackend_usuario_
permisosEspecialidad"."usuario_id" = 2;
Results: 2
I may be missing something as well, but I think that this optimization may
be apropriated.
2015-11-19 1:04 GMT-02:00 Josh Smeaton <josh.smea...@gmail.com>:
> It might be a bit early in the day for me, but isn't that query already
> optimised? That is, it's already eliminated a join. It hasn't joined to the
> "Especialidad" table, it's only joined to the intermediate table. I *think*
> the join to the intermediate table is necessary because there could be
> duplicates.
>
> Given the tables:
>
> Usuario(pk):
> 1
> 2
>
> Intermediate(usurario_id, especialidad_id):
> 1, 1
> 1, 2
>
> Especialidad(pk)
> 1
> 2
>
> Joining Usuario to Intermediate will return 4 results in SQL (2 for each
> pk on Usuario) unless there was a distinct in there somewhere. I haven't
> tested, so I'm not sure if django does duplicate elimination, but I'm
> pretty sure it doesn't.
>
> Does this look right to you, or am I missing something?
>
> Cheers
>
>
> On Thursday, 19 November 2015 11:41:22 UTC+11, Cristiano Coelho wrote:
>>
>> Hello there,
>>
>> Lets say I have these two models (sorry about the spanish names!) (
>> Django 1.8.6 and MySQL backend )
>>
>> class Especialidad(models.Model):
>> nombre = models.CharField(max_length=250, blank=False, unique=True)
>>
>>
>>
>> class Usuario(AbstractBaseUser):
>> permisosEspecialidad = models.ManyToManyField("Especialidad", blank=True)
>>
>> Let u be some Usuario instance, and the following query:
>>
>> u.permisosEspecialidad.all().values_list('pk',flat=True)
>>
>> The actual printed query is:
>>
>> SELECT `samiBackend_especialidad`.`id`
>> FROM `samiBackend_especialidad`
>> INNER JOIN `samiBackend_usuario_permisosEspecialidad` ON (
>> `samiBackend_especialidad`.`id` =
>> `samiBackend_usuario_permisosEspecialidad`.`especialidad_id` )
>> WHERE `samiBackend_usuario_permisosEspecialidad`.`usuario_id` = 8
>>
>> As my understanding, since I'm only selecting the id field which is already
>> present in the intermediary table (and is also a FK), the actual join is
>> redundant, as I have all the info I need in this case.
>>
>> So the query could work like this
>>
>> SELECT `samiBackend_usuario_permisosEspecialidad`.`especialidad_id`
>> FROM `samiBackend_usuario_permisosEspecialidad`
>> WHERE `samiBackend_usuario_permisosEspecialidad`.`usuario_id` = 8
>>
>>
>> I guess this works this way because this particular case might be hard to
>> detect or won't be compatible with any additional query building, however,
>> for ForeignKey relations, this optimization is already done (If you select
>> the primary key from the selected model only, it wont add a join)
>>
>> What would be the complications to implement this? Would it worth the effort?
>>
>>
>> --
> 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+unsubscr...@googlegroups.com.
> To post to this group, send email to django-developers@googlegroups.com.
> Visit this group at http://groups.google.com/group/django-developers.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-developers/9eddc20d-8740-48d1-a38d-fd7a00a56f2a%40googlegroups.com
> <https://groups.google.com/d/msgid/django-developers/9eddc20d-8740-48d1-a38d-fd7a00a56f2a%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
>
> For more options, visit https://groups.google.com/d/optout.
>
--
Eduardo Erlo
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at http://groups.google.com/group/django-developers.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-developers/CAL_-ofaXXhafoy4u3EAqZdvAW%2B%3D0rGD2umDrG%2B1m%2B6y0aNcrYg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
