foreignkey problem

Question

Hey,

Imagine you have this model:

class Category(models.Model):
      node_id = models.IntegerField(primary_key = True)
      type_id = models.IntegerField(max_length = 20)
      parent_id = models.IntegerField(max_length = 20)
      sort_order = models.IntegerField(max_length = 20)
      name = models.CharField(max_length = 45)
      lft = models.IntegerField(max_length = 20)
      rgt = models.IntegerField(max_length = 20)
      depth = models.IntegerField(max_length = 20)
      added_on = models.DateTimeField(auto_now = True)
      updated_on = models.DateTimeField(auto_now = True)
      status = models.IntegerField(max_length = 20)
      node = models.ForeignKey(Category_info, verbose_name = 'Category_info', to_field = 'node_id'

The important part is the foreignkey. When I try:

Category.objects.filter(type_id = 15, parent_id = offset, status = 1)

I get an error that get returned more than category, which is fine, because it is supposed to return more than one. But I want to filter the results trough another field, which would be type id (from the second Model)

Here it is:

class Category_info(models.Model):
      objtree_label_id = models.AutoField(primary_key = True)
      node_id = models.IntegerField(unique = True)
      language_id = models.IntegerField()
      label = models.CharField(max_length = 255)
      type_id = models.IntegerField()

The type_id can be any number from 1 - 5. I am desparately trying to get only one result where the type_id would be number 1.

Here is what I want in sql:

SELECT c.*, ci.*
FROM category c
JOIN category_info ci ON (c.node_id = ci.node_id)
WHERE c.type_id = 15 AND c.parent_id = 50 AND ci.type_id = 1

Any help is GREATLY appreciated.

Regards

Answer 1

To filter on fields in a related table, use the double-underscore notation. To get all Category objects where type_id of the related Category_info object is 15, use:

Category.objects.filter(node__type_id=15)

Django will then automagically understand that you're referring to the type_id field on whatever table node is related to.

Answer 2

So it still didnt solve my problem.. Let me try to explain in a bit more.

Lets say that the sql:

SELECT c.*, ci.*
FROM category c
JOIN category_info ci ON (c.node_id = ci.node_id)
WHERE c.type_id = 15 AND c.parent_id = 50 

Will return two rows. Both are identical, except type_id field from category_info table where there are two types - 1 and 2. If I will add to the sql - ci.type_id = 1, I will get the right result. But from what I have tried, even with the doubleunderscore notation, it still returns 2 rows in Django.

Now I have got:

Category.objects.filter(type_id = 15, parent_id = offset, status = 1, node__type_id = 1)

Where the node__type_id = 1 represents the "ci.type_id = 1". But it still does return two rows. When I will delete the "to_field" from my model definition it will pass, but return wrong data, because it binds it to the primary key by default. I tried to filter the data afterwards by chaining another filter, but still wont get past it.

Here is a bit form the debug, maybe it helps:

Caught an exception while rendering: get() returned more than one Category_info -- it returned 2! Lookup parameters were {'node_id__exact': 5379L}

It still looks like its trying to look only for the node_id and not for the type_id.

Sigh, I could cry...

Answer 3

Ok so...

select_related() together with the underscore thingy worked well...