On Mon, 2009-02-09 at 06:56 -0800, AlexMVdovin wrote:
> At first step I will describe my models:
>
> class Meter(models.Model):
> id = models.IntegerField(primary_key=True)
>
> ........some stuff here...
>
> collector = models.ManyToManyField("self",
> through='MeterCollector', symmetrical=False)
>
> class MeterCollector(models.Model):
> meter = models.ForeignKey(Meter, related_name='meter_id')
> collector_node = models.ForeignKey(Meter,
> related_name='collecotor_node_id')
>
> def __unicode__(self):
> return u'meter id: %s; collector node id: %s;' %
> ( self.meter_id, self.collector_node_id)
>
> class Meta:
> db_table = 'meter_to_collector'
>
> class MeterSspec(models.Model):
> id = models.IntegerField(primary_key=True)
> meter = models.ForeignKey(Meter)
> sspec = models.ForeignKey(Sspec)
> ......some stuff...
>
> Everything works fine until I use filter like this in view.py:
>
> meter_list = Meter.objects.all().filter(meter__metersspec__sspec =
> sspec_id)
>
> I expect something like "Give me all meters that have sspec_id = 1",
> so my custom query will be:
>
> SELECT `meter`.`id`, `meter`.`description` FROM `meter` LEFT JOIN
> `meter_to_collector` ON (`meter`.`id` =
> `meter_to_collector`.`collector_node_id`) LEFT JOIN `meter` T3 ON
> (`meter_to_collector`.`meter_id` = T3.`id`) INNER JOIN `meter_sspec`
> ON (`meter`.`id` = `meter_sspec`.`meter_id`) WHERE
> `meter_sspec`.`sspec_id` = '1'
>
> But django gives me something like this:
>
> SELECT `meter`.`id`, `meter`.`description` FROM `meter` INNER JOIN
> `meter_to_collector` ON (`meter`.`id` =
> `meter_to_collector`.`collector_node_id`) INNER JOIN
> `meter` T3 ON (`meter_to_collector`.`meter_id` = T3.`id`) INNER JOIN
> `meter_sspec` ON (T3.`id` = `meter_sspec`.`meter_id`) WHERE
> `meter_sspec`.`sspec_id` = '1'
>
> There are 2 common problems:
> 1) How can I force django to use LEFT JOIN ???
Wrong question. The real question is "why is Django using an inner join
there" and the reason is because you're filtering on a specific,
non-NULL, value on the last table. Thus, you've already ruled out the
possibility of the later tables having NULL rows in the final result.
Django is clever enough to know that this means it doesn't need to do
outer joins there.
Since inner joins are much faster than outer ones, we optimise the join
when there's a specific non-NULL value involved.
> 2) Genreal problem: Im wondering why django joins meter_sspec with
> meter_to_collector (T3) and not with meter???
Because it's the only way to get the right answer. :-)
MeterSpec -> Meter is a many-to-one relation. So there could be multiple
MeterSpec possibilities associated with a given Meter instance. You're
asking for all those MeterSpecs that are related to a Meter that have
*a* MeterSpec associated with them that has a particular Sspec value.
The second MeterSpec instance doesn't have to be the same as the first
one in that filtering (because of the many-to-one), so we must use a new
instance of the table.
> So, is there any way to do it without custom sql query ?
Before we can give an answer, we must first know the question. What are
you trying to do?
You've given some models and some code and Django appears to be
generating the correct SQL for what you've asked in the queryset.
Apparently this isn't giving you the results you are after, so what
results are you looking for here? There's a strong possibility that your
queryset filter isn't the one you're really intending. I can't see any
problems after that point from what you've posted.
Regards,
Malcolm
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
