django model foreign key queryset selecting related fields

Question

I am trying to select from the join of the two related tables in DJango as shown below. But I am not able get the field name of the other table.

In SQL we can write:

select person.phonenumber,membership.* from membership where person=name ;

This displays all the membership fields along with phonenumber, but in Django it throws error:

Cannot resolve keyword 'phonenumber' into field

I have even tried with select_related but no use. Am I doing anything wrong?

model.py:

from django.db import models

# Create your models here.
class Person(models.Model):
    name = models.CharField(primary_key=True,max_length=128)
    phonenumber = models.CharField(max_length=128)
    city = models.CharField(max_length=128)


    def __unicode__(self):
        return self.name

class Membership(models.Model):
    person = models.ForeignKey(Person)
    date_joined = models.DateField()
    invite_reason = models.CharField(max_length=64)


>>> print m.query
SELECT `myapp_membership`.`id`, `myapp_membership`.`person_id`, `myapp_membership`.`date_joined`, `myapp_membership`.`invite_reason` FROM `myapp_membership` WHERE `myapp_membership`.`person_id` = x 
>>> m=Membership.objects.filter(person__name='x').select_related()
>>> print m.query
SELECT `myapp_membership`.`id`, `myapp_membership`.`person_id`, `myapp_membership`.`date_joined`, `myapp_membership`.`invite_reason`, `myapp_person`.`name`, `myapp_person`.`phonenumber`, `myapp_person`.`city` FROM `myapp_membership` INNER JOIN `myapp_person` ON (`myapp_membership`.`person_id` = `myapp_person`.`name`) WHERE `myapp_membership`.`person_id` = x 
>>> m=Membership.objects.filter(person__name='x').values('person','phonenumber').select_related()
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/usr/local/lib/python2.6/dist-packages/Django-1.2.4-py2.6.egg/django/db/models/query.py", line 510, in values
    return self._clone(klass=ValuesQuerySet, setup=True, _fields=fields)
  File "/usr/local/lib/python2.6/dist-packages/Django-1.2.4-py2.6.egg/django/db/models/query.py", line 766, in _clone
    c._setup_query()
  File "/usr/local/lib/python2.6/dist-packages/Django-1.2.4-py2.6.egg/django/db/models/query.py", line 889, in _setup_query
    self.query.add_fields(self.field_names, False)
  File "/usr/local/lib/python2.6/dist-packages/Django-1.2.4-py2.6.egg/django/db/models/sql/query.py", line 1537, in add_fields
    "Choices are: %s" % (name, ", ".join(names)))
FieldError: Cannot resolve keyword 'phonenumber' into field. Choices are: date_joined, id, invite_reason, person

score 23 · Accepted Answer · answered Mar 21 '11 at 16:33

23

phonenumber is a field on person, so you need to pass in person__phonenumber

m=Membership.objects.filter(person__name='x').values('person','person__phonenumber').

answered Mar 21 '11 at 16:33

Yuji 'Tomita' Tomita

115,817
29
282
245

@Yuji 'Tomita' Tomita What if I need to include one more join in the values? For example if the Person table contains foreign key to the table Company which address I want to find. I assumed that I would need to add in the values : 'person__company__address'. but this doesn't work for me.. Do you know what should I do? – GileBrt Jan 22 '18 at 12:28
@GileBrt that would work if you had a `Membership` model with FK to `Person` with FK to `Company` with a field called `address`. You likely have a slightly different model, so I recommend you post a new question. – Yuji 'Tomita' Tomita Jan 24 '18 at 07:03
@Yuji 'Tomita' Tomita There is a foreign key in my case, just as you described. And when doing just this it works, you are right. But, what I really need is to sum a certain value over these attributes mentioned in .values (like group by in sql). I was thinking that I would be able to do this if I just add ".annotate(total_value=Sum('a_value'))" to what you already wrote, but then this kind of value 'person__company__address' doesn't work. – GileBrt Jan 26 '18 at 16:09
@GileBrt you really need to post a new question - that idea is not for the comments. I'd be happy to help if you write a proper question with models, what you're attempting, etc. – Yuji 'Tomita' Tomita Jan 26 '18 at 22:17

score 3 · Answer 2 · answered Oct 27 '11 at 15:02

3

Also if you want a queryset (so not to use values) you can do this:

m=Membership.objects.filter(person__name="x").select_related('person', depth=1)

Sadly I do not know how to only get one field from that table and still get a queryset.

answered Oct 27 '11 at 15:02

Virgil Balibanu

1,037
4
11
16

5

you can't have both a field and depth param – aehlke Aug 23 '13 at 19:57
2

by doing this you obtain "select_related() got an unexpected keyword argument 'depth'". It's either depth param or field, but not two at once. – 1ronmat May 13 '17 at 10:43

django model foreign key queryset selecting related fields

2 Answers2

Linked