GenericRelation field lookups

[litnimax]

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...

[litnimax]

New issue

2 lines of code will tell better:

mysql> select provider_numberpool.*  from provider_numberpool left join  billing_subscription on provider_numberpool.id = billing_subscription.id where billing_subscription.id IS NULL;
+----+-----------------+--------------+------+
| id | name            | is_published | note |
+----+-----------------+--------------+------+
|  1 | range 100 - 101 |            1 |      | 
+----+-----------------+--------------+------+

1 row in set (0.00 sec)
mysql> select provider_numberpool.*  from provider_numberpool left join  billing_subscription on provider_numberpool.id = billing_subscription.object_id where billing_subscription.id IS NULL;
Empty set (0.00 sec)

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

provider_numberpool.id = billing_subscription.id

works (but has no sense), but

provider_numberpool.id = billing_subscription.object_id

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:

mysql> explain extended select provider_numberpool.*  from provider_numberpool left join  billing_subscription on provider_numberpool.id = billing_subscription.id where billing_subscription.id IS NULL;
+----+-------------+----------------------+--------+---------------+---------+---------+-------+------+----------------------+
| id | select_type | table                | type   | possible_keys | key     | key_len | ref   | rows | Extra                |
+----+-------------+----------------------+--------+---------------+---------+---------+-------+------+----------------------+
|  1 | SIMPLE      | provider_numberpool  | system | NULL          | NULL    | NULL    | NULL  |    1 |                      | 
|  1 | SIMPLE      | billing_subscription | const  | PRIMARY       | PRIMARY | 4       | const |    0 | unique row not found | 
+----+-------------+----------------------+--------+---------------+---------+---------+-------+------+----------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                     |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select '1' AS `id`,'range 100 - 101' AS `name`,'1' AS `is_published`,'' AS `note` from `billing`.`provider_numberpool` left join `billing`.`billing_subscription` on(multiple equal('1')) where isnull('0') | 
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Not working case:

mysql> explain extended select provider_numberpool.*  from provider_numberpool left join  billing_subscription on provider_numberpool.id = billing_subscription.object_id where billing_subscription.id IS NULL;
+----+-------------+----------------------+--------+--------------------------------+--------------------------------+---------+-------+------+-------------------------+
| id | select_type | table                | type   | possible_keys                  | key                            | key_len | ref   | rows | Extra                   |
+----+-------------+----------------------+--------+--------------------------------+--------------------------------+---------+-------+------+-------------------------+
|  1 | SIMPLE      | provider_numberpool  | system | NULL                           | NULL                           | NULL    | NULL  |    1 |                         | 
|  1 | SIMPLE      | billing_subscription | ref    | billing_subscription_object_id | billing_subscription_object_id | 4       | const |   15 | Using where; Not exists | 
+----+-------------+----------------------+--------+--------------------------------+--------------------------------+---------+-------+------+-------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                        |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select '1' AS `id`,'range 100 - 101' AS `name`,'1' AS `is_published`,'' AS `note` from `billing`.`provider_numberpool` left join `billing`.`billing_subscription` on((`billing`.`billing_subscription`.`object_id` = '1')) where isnull(`billing`.`billing_subscription`.`id`) | 
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

And finally table definition:

CREATE TABLE `billing_subscription` (
  `id` int(11) NOT NULL auto_increment,
  `content_type_id` int(11) NOT NULL,
  `object_id` int(10) NOT NULL,
  `account_id` int(11) NOT NULL,
  `currency_id` int(11) NOT NULL,
  `period` int(11) NOT NULL,
  `setup` decimal(10,2) NOT NULL,
  `rate` decimal(10,2) NOT NULL,
  `create_date` datetime NOT NULL,
  `paidtill_date` date NOT NULL,
  `is_enabled` tinyint(1) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `billing_subscription_content_type_id` (`content_type_id`),
  KEY `billing_subscription_account_id` (`account_id`),
  KEY `billing_subscription_currency_id` (`currency_id`),
  KEY `billing_subscription_object_id` (`object_id`)
) ENGINE=MyISAM AUTO_INCREMENT=56 DEFAULT CHARSET=utf8 

Conclusion

MySQL bug? What about Postgres? How to solve it!?

[Malcolm Tredinnick]

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.