Opened 17 years ago
Closed 16 years ago
#6051 closed (fixed)
GenericRelation field lookups
Reported by: | litnimax | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | dev |
Severity: | Keywords: | generic | |
Cc: | drackett@… | Triage Stage: | Accepted |
Has patch: | yes | Needs documentation: | no |
Needs tests: | yes | Patch needs improvement: | yes |
Easy pickings: | no | UI/UX: | no |
Description
Problem descriptions
Currently GenericRelations documentation does not say anything that it's possible.
I mean expression like
Animal.objects.filter(tags__tag__exact='heavy')
is not described there. But if we look in generic.py we can find there pretty much code that makes it possible. But apparently it's not ready yet. So I am creating here this ticket for discussion as I was told django developers list if not the right place to do it (my post is here).
So, at the moment I found the following issues.
- Wrong field name when using intermediate tables,
for example, name__exact='bla-bla' worked, but user__account__is_enabled=True did not work.
Fixed in patch attached - file:wrong_field_name.patch - Not using content types table that makes possible for wrong results. Live example is below.
class Subscription(models.Model): """ Universal model for all subscriptions. """ content_type = models.ForeignKey(ContentType) object_id = models.PositiveIntegerField(db_index=True) account = models.ForeignKey(VoipAccount, verbose_name=_("account")) currency = models.ForeignKey(Currency, verbose_name=_("currency")) period = models.IntegerField(_("period"), choices=PERIOD_CHOICES) setup = models.DecimalField( _("setup fee"), max_digits=10, decimal_places=2) rate = models.DecimalField( _("period rate"), max_digits=10, decimal_places=2) create_date = models.DateTimeField(_("created on"), db_index=True) paidtill_date = models.DateField(_("paid till"), db_index=True) is_enabled = models.BooleanField(_("is enabled"), db_index=True) content_object = generic.GenericForeignKey()
Now model that uses Subscription:
class LocalNumber(models.Model): number = models.CharField(_('number'), max_length=10, unique=True) subscription = generic.GenericRelation(Subscription)
These models make it possible for users to "subscribe" to numbers. It generates the following code:
In [48]: LocalNumber.objects.filter(subscription__is_enabled=True) Out[48]: [<LocalNumber: 1000>, <LocalNumber: 1020>] Out[49]: (['`provider_localnumber`.`id`', '`provider_localnumber`.`pool_id`', '`provider_localnumber`.`sub_pool_id`', '`provider_localnumber`.`number`'], ' FROM `provider_localnumber` LEFT OUTER JOIN `billing_subscription` AS `m2m_provider_localnumber__subscription` ON `provider_localnumber`.`id` = `m2m_provider_localnumber__subscription`.`object_id` INNER JOIN `billing_subscription` AS `provider_localnumber__subscription` ON `m2m_provider_localnumber__subscription`.`id` = `provider_localnumber__subscription`.`id` WHERE (`provider_localnumber__subscription`.`is_enabled` = %s)', [True])
I do not see any relations with content types. Imagine that some other model has same object_id? Our initial situation is correct:
mysql> select * from billing_subscription; +----+-----------------+-----------+------------+-------------+--------+-------+------+---------------------+---------------+------------+ | id | content_type_id | object_id | account_id | currency_id | period | setup | rate | create_date | paidtill_date | is_enabled | +----+-----------------+-----------+------------+-------------+--------+-------+------+---------------------+---------------+------------+ | 54 | 174 | 46317 | 20865 | 1 | 1 | 0.00 | 0.00 | 2007-11-28 23:52:53 | 2008-11-27 | 1 | | 53 | 174 | 46337 | 20865 | 1 | 1 | 0.00 | 0.00 | 2007-11-28 21:09:39 | 2008-11-27 | 1 | +----+-----------------+-----------+------------+-------------+--------+-------+------+---------------------+---------------+------------+
Now let "subscribe" some other model:
mysql> insert into billing_subscription (content_type_id, object_id, account_id, is_enabled) values (12, 46337, 20865, 1); mysql> select * from billing_subscription; +----+-----------------+-----------+------------+-------------+--------+-------+------+---------------------+---------------+------------+ | id | content_type_id | object_id | account_id | currency_id | period | setup | rate | create_date | paidtill_date | is_enabled | +----+-----------------+-----------+------------+-------------+--------+-------+------+---------------------+---------------+------------+ | 54 | 174 | 46317 | 20865 | 1 | 1 | 0.00 | 0.00 | 2007-11-28 23:52:53 | 2008-11-27 | 1 | | 53 | 174 | 46337 | 20865 | 1 | 1 | 0.00 | 0.00 | 2007-11-28 21:09:39 | 2008-11-27 | 1 | | 55 | 12 | 46337 | 20865 | 0 | 0 | 0.00 | 0.00 | 0000-00-00 00:00:00 | 0000-00-00 | 1 | +----+-----------------+-----------+------------+-------------+--------+-------+------+---------------------+---------------+------------+
As you can see subscription.id=55 has content_type_id=12 and content_object is not LocalNumber. Let proove it:
In [61]: print Subscription.objects.get(pk=53).content_object 1020 In [62]: print Subscription.objects.get(pk=54).content_object 1000 In [63]: print Subscription.objects.get(pk=55).content_object None
As you can see there are only 2 subscriptions to numbers (We got None for 3-rd line because there is no such object_id for model id 12). But let see again:
In [64]: LocalNumber.objects.filter(subscription__is_enabled=True) Out[64]: [<LocalNumber: 1000>, <LocalNumber: 1020>, <LocalNumber: 1020>]
What happened!? Why do we have 3 numbers instead of 2? This is because current implementation does not use content types table.
To be continued...
Attachments (1)
Change History (6)
by , 17 years ago
Attachment: | wrong_field_name.patch added |
---|
comment:1 by , 17 years ago
comment:2 by , 17 years ago
Cc: | added |
---|
comment:3 by , 17 years ago
Patch needs improvement: | set |
---|---|
Triage Stage: | Unreviewed → Accepted |
comment:4 by , 16 years ago
Component: | Uncategorized → Database layer (models, ORM) |
---|---|
Needs tests: | set |
comment:5 by , 16 years ago
Resolution: | → fixed |
---|---|
Status: | new → closed |
I'm having a bit of trouble understanding all this and a lot of it refers to pre-queryset-refactor code. I'm fairly sure the main issue was fixed in r8608. If not and somebody feels it should be reopened, we'll need a patch against the Django tests directory showing something that fails that should work. That will make debugging whatever the remaining problem is easier. But, again, it's been fixed in the normal course of business.
New issue
2 lines of code will tell better:
What is the difference? Only ON clause of JOIN. In GenericObject we have object_id equal to some other model's primary key. So object_id is not a strict foreign key
Why
works (but has no sense), but
does not!? Both fields are INT, both have index. The only differency that id is primary key, and object_id is simple index.
Let see what EXPLAIN will explain.
Working case:
Not working case:
And finally table definition:
Conclusion
MySQL bug? What about Postgres? How to solve it!?